How to Choose a SQL Server Authentication Mode

Probably one of the most important decisions you’ll make during your install of SQL Server 2008 is which authentication mode to choose.  You’ve only got two choices:  Windows authentication, and Windows and SQL authentication.  They both have their place, and I know one of the choices seems redundant, but it’s really not.  Here I’m going to explain what each of these means and when you would choose each one.

Windows Authentication

This is the default option during setup because it’s the most secure.  Using this method allows you to control all of your database security through Windows and Active Directory.  This method allows you to place users into groups if you so desire (more on that later), and it allows you not have to manage passwords for your users.  It also keeps your environment secure by either eliminating generic accounts, or at least allowing you to keep tighter control over their passwords. 

This scenario is simple.  You’ve got an application that needs to connect to SQL Server 2008, and you don’t want the username and password sitting out there in a file somewhere.  So the easiest way to protect against this is to use a Windows account with a password you control and run the application under that user.  This way you can also rotate the password as you see fit and there’s nothing out on the server that reveals it.  Having passwords sitting out in ini files or in webcofig files, etc is one of the worst management nightmares in security because any of the developers or anyone else can just go out and get the password and connect under the generic account.  And this makes chasing down problems much harder.

So how do you know if Windows authentication is right for you?  Well, it’s going to be right for you most of the time.  If you’ve got a single domain and all of your database users will be in this domain (or a trusted domain), then this option is for you.  It’s the most secure and you can’t go wrong if you wanted to force everyone to connect using Windows authentication.

SQL and Windows Authentication

This one seems redundant, but it’s really not.  In the old days, you had 3 choices for security, you had Windows, SQL, and Both.  To a degree that made sense, but in retrospect it really doesn’t.  The logic here is simple.  Since Windows is more secure, there’s no reason to disallow it.  All you’re really doing here is allowing SQL authentication along with the most secure method.  So you’re always going to be able to connect with Windows authentication, the only thing we’re arguing about is whether or not you’re going to allow SQL as well.

So now the question is when will you want to allow SQL authentication?  Well, I know what I said above, but the answer here is “most of the time”.  I know, it sounds like I just contradicted myself, but there’s a theoretical answer and a practical answer, and this is the practical answer.  See, while Windows is more secure, there are plenty of reasons to use SQL authentication so it’s a good idea to have it turned on.  For example, it’s a good idea to leave yourself a backdoor for emergencies or just for troubleshooting.  A good example of this is with the common “cannot generate SSPI context” error.  This is an error that pops up in windows sometimes that results from Kerberos not working for any number of reasons.  So in this case SQL Server is going to try to default to Kerberos (because Windows controls the security method when you use Windows authentication) and if you’re having SSPI issues you won’t be able to connect to your database. 

If you’re using SQL authentication though, Windows has to use NTLM which bypasses Kerberos and you can now connect to the database to troubleshoot the server, or merely to continue working until you get the Kerberos problem worked out.  So having a SQL backdoor can be useful in a very practical sense.  And the really cool thing about using SQL authentication in SQL Server 2005 and above is that you can take advantage of your Windows security model and force your SQL passwords to adhere to your Windows password policies.  So you can force SQL passwords to expire, and to honor your Windows password complexity requirements.

You can also have external customers whom you don’t want to have Windows accounts.  In this case, SQL authentication is a good choice too.  And it doesn’t have to be external customers.  You could just have non-Windows domains and Windows authentication simply isn’t an option.

While Windows authentication is the most secure, it’s simply not practical as your sole authentication method.  Don’t get me wrong though.  You’ll still want to insist on using Windows every chance you get.  You should work really hard to insist on running applications and websites through specific Windows accounts.  Your developers will usually fight you on this because they have a generic SQL account and they want to use it.  They love being able to bypass their personal accounts and connect to the production database to do things.  So while enabling SQL authentication is always a good idea, it’s best to keep it restricted to admin usage and only hand out SQL accounts if there’s absolutely no other way around a connection problem.  It’s also important to note that your decision during install isn’t final.  You can easily change the authentication mode at any time.  You have to restart the SQL Server service once you do though.