Authentication mode of SQL server

I am collecting some interesting facts related to Authentication mode of SQL server.

 

When we are going to install the SQL server, it's asked about the Authentication mode. We have to choose between 2 options.

 

1.    Windows Authentication mode.

2.    Windows and SQL Authentication mode.

 

In my article, I am going to provide some short description related to it.

 

Windows Authentication mode

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 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.

Windows and SQL Authentication mode

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.

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.

 

 

Posted by: MR. JOYDEEP DAS