Hey vendors, consultants, clients, etc. STOP USING SQL LOGINS. Now if the SQL Server you are using isn’t attached to a Windows domain then fine, odds are you’ll need a SQL Authentication login. However if the machine is a member of the Windows domain then login to SQL Server using Windows Authentication.
The other day I was connecting to a clients SQL Server. I had to log onto a different server to run SSMS, so far so good. But then they gave me the connection information for the SQL Server which had a SQL Auth username and password. I’ve already got a domain account, so for the love of god why did I have to have another stupid username and password instead of just connecting via the Windows Account I had JUST USED to log into Windows.
Vendor apps are just as bad. They’ll insist that the Windows services run under a domain account, usually so that they can access network shares or something, but then they require a SQL Auth account to be created to log into the SQL Server database. This means that someone needs to track another username and password, and given that SQL Auth accounts are easier to break into than Windows Auth accounts it’s less secure over all on top of that.
In 15+ years of managing SQL Servers for people I’ve found only a few software venders that were even willing to try running the software under a domain account so that we could use Windows auth to connect to the SQL Server. And one of those was the one that I worked for, where I forced the developers to make that an option so that the DBA & sysadmin would have the option to install the Software under domain accounts and use those domain accounts to connect to the SQL Server. The developer didn’t understand why I cared about this but eventually I got my way, mostly I think so that I’d shut up about it.
In my mind there are only a few times when it is truly acceptable to use a SQL Authentication Login at this point.
- The clients are not running Windows.
- The clients are not on the same domain, and the two domains aren’t trusted, and the user doesn’t have a Windows login in the same domain as the SQL Server.
- The SQL Server is running version 4.2 or earlier.
- The client application is a Windows service and it isn’t on the domain or the domains aren’t trusted.
Now you’ll notice for #2 I was pretty specific. That’s because if the client is running Windows and the user has a domain login in the same domain as the SQL Server then the client application can be run as the users account in the other domain (this includes SQL Server Management Studio).
In summary, in case you didn’t get my point yet, STOP USING SQL AUTHENTICATION LOGINS.