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.
Denny
2 Responses
Sorry to say, but I 100% disagree with your post. Using Windows Authentication is nice because you don’t need to worry about having to remember two passwords, sure, but it also means that you need to be infinitely more granular with your security settings.I much prefer having role-based SQL accounts for my users. For an application database, create a single login and grant it the relevant accesses. Does a new employee need database access? Okay, create them a SQL login, grant them limited access, and then manage them through SQL server. No need to worry about contacting the local windows admin to manage their account.
Kramaswamy,Why not just grant their Windows login rights into the database, and drop it into the database roles that are needed. Then when the employee leaves you aren’t responsible for finding out that the employee has left and turning off the account. Instead the account stops working automatically because the Windows login is disabled.Your idea of better security is good, however the implementation that you have described allows for logins which belong to former employees potentially be left enabled after the employee has left, which is a major problem. Also people are much more likely to share a SQL login and password with other employees than they are their Windows login and password.Denny