Using Windows Groups for SQL Server Logins as a Best Practice

ProblemWe're taking a break from the tips format this month to provide you with some Best Practice articles for the many aspects of Microsoft SQL Server development, administration, and support.  This article will focus on the use of Windows Groups for security in your SQL Server instances in lieu of relying on SQL Server logins for administering your security contexts.

SolutionFirst we should explain those terms I just tossed out to you without warning.  What is a Windows Group?  SQL Server Login?  I can see some of you throwing your hands up already.  It's really simple, trust me. 

Inside of SQL Server you have two options for allowing clients to access your databases:  Windows (also known as Trusted) Authentication and SQL Server Authentication:

The main difference between the two authentication schemes is that Windows authentication relies on your domain's security architecture while SQL Server authentication adds an additional process for authentication by requiring a hard-coded login and associated password for connectivity to any SQL Server instances or databases. 

This may make SQL authentication sound like a more-secure model for tightening access to your databases; this is not necessarily the case.  These logins and associated passwords must be saved somewhere in the client/application structure whether it is in an .ini file, a DSN (data source name) file, registry setting, or (forbid) hard-coded into the application code.  Very likely this information is exposed quite easily to anyone with access to the client computer or application server. 

In the case of hard-coded logins and passwords or .ini files this information would most-likely be common to all installations of the particular application.  This is quite troubling in the case of turnkey solutions purchased from third-party vendors outside your organization.  Each customer has the same security information to connect to your databases; they just are missing one link in order to do so: server connectivity.  A middling hacker could easily overcome this information with little effort.  This is really no different in my opinion than securing the System Administrator login (sa) that is native to each and every SQL Server installation with a blank password.  This is particularly the case when you have a single login associated with an application; not so much the case when each client or user has their own login id.  Though that still does still open yourself up for hacking and also causes additional overhead as I will discuss below.

By using Windows authentication you pass on the domain login information from any client attempting to access databases on a SQL Server instance.  If the user is authenticated to the domain correctly then SQL Server will "trust" that the Security Administrators in your domain are doing their job and that the user is who they say they are.  SQL "trusts" the login process in the domain and that is why you'll often see Windows authentication referred to as "Trusted Authentication". 

So what does this afford the SQL Server Database Administrator or Programmer/Developer?  For the Programmer/Developer it means that you don't need to code in the user login and password into the application construct.  In whatever connection scheme you use you simply need to state the application is going to utilized "trusted" security in order to connect to the database.  Examples of the connection strings for both SQL authentication and Windows authentication below highlight this point:

For this example I'll create two logins, the first will be using SQL Server authentication, the second will rely on Windows authentication.

USE [master]  GO CREATE LOGIN [Timothy]  WITH      PASSWORD=N'Timothy',      DEFAULT_DATABASE=[MSSQLTips],      CHECK_EXPIRATION=OFF,      CHECK_POLICY=OFF;      USE [master]  GO CREATE LOGIN [SAURON\Timothy] FROM WINDOWS  WITH      DEFAULT_DATABASE=[MSSQLTips];

You'll note that when creating a SQL-based login you must specify not only the login name, but an associated password.  The DEFAULT_DATABASE clause directs the connection to a specific database if no database is specified in the connection string within the application construct the DEFAULT_DATABASE statement is used for either SQL or Windows security constructs. 

Meanwhile, the CHECK_EXPIRATION and CHECK_POLICY clauses, associated only with SQL-based security, direct SQL to require the password strength and expiration periods from your domain security rules to either be ignored or enforced.  Meanwhile, a Windows-based login only requires that you declare the domain/login associated with the login you're creating.  The password is irrelevant, because the user will be logging into the domain first; authentication there must be successfully completed before connection to your SQL Server instance.

With these two logins in place on your instance you need to grant rights to the default database (at least) if you want to allow them to be useful.  The code for doing so is below, first for the SQL authenticated login followed by the login based upon Windows authentication:

USE [MSSQLTips]  GO CREATE USER [Timothy] FOR LOGIN [Timothy] WITH DEFAULT_SCHEMA=[dbo]; EXEC sp_addrolemember N'db_datareader', N'Timothy';  USE [MSSQLTips]  GO CREATE USER [SAURON\Timothy] FOR LOGIN [SAURON\Timothy]  WITH DEFAULT_SCHEMA=[dbo]; EXEC sp_addrolemember N'db_datareader', N'SAURON\Timothy';

You'll see that I'm creating a user in a very similar fashion between both security schemes.  Short of the login names being different there is no differences in how users are created from one security scheme to another.  The differences lie at the login level of SQL Server's security model.  In both cases I created a user with the same name as the login.  This is not a requirement.  I could have just as easily created a login named Timothy_Windows from the Windows-based login.  The code would look like the following if that was the case:

USE [MSSQLTips]  GO CREATE USER [Timothy_Windows] FOR LOGIN [SAURON\Timothy]  WITH DEFAULT_SCHEMA=[dbo]; EXEC sp_addrolemember N'db_datareader', N'Timothy_Windows';;

So now that we've laid the foundations in my test instance for the Timothy login (SQL-based) and SAURON\Timothy login (Windows-based) how does this bode for the Programmer/Developer?  I alluded to simplicity in the connection string for Windows v. SQL logins. This is evident in the code from two sample connection strings:

SQL-Based Security: "Driver={SQLServer};Server=Sauron;Database=MSSQL Tips;Uid=Timothy;Pwd=Timothy;"  Windows-Based Security: "Driver={SQLServer};Server=Sauron;Database=MSSQL Tips;Trusted_Connection=yes;"

We don't have to expose the login or the password by using Windows-based security.  Furthermore you don't need to customize a connection string per distinct user.

For the DBA, using Windows-based security means less overhead.  Password management can be a nightmare for a DBA.  Constantly fielding questions from users asking for a password reminders when they lose them require you to interrupt your other work to answer their immediate needs.  It also requires you to keep a secondary password management system current since the password is not visible for a easy review by even the DBA.  With Windows security that is all handled by the Domain Security Administrators - right where that overhead belongs.

Windows Groups make life even better for the DBA.  I was about to say my love for Windows Groups rivals that of my love for bacon or chocolate ice cream, but that would be blasphemy.  Why do I have such deep affection for Windows Groups?  What are Windows Groups?  Let's look at that...

Windows Groups are logical constructs at the domain level that organize individual domain logins into distinct entities.  Individual logins can then be transitioned in and out of the group by the Domain Security Admin.  As a DBA you can create a login based upon a Windows Group, rather than a Windows login.  The code is the same, with the exception of one concept that may cause concern: the DEFAULT_SCHEMA clause. 

You can not set a default schema for a SQL database user based from a Windows Group.  This means that you must rely on solid application coding for fully-qualified naming conventions for all objects or having discrete object names across all schemas.  Now for the bonus of using Windows Groups: it allows the DBA to be fully-hands-off when it comes to dealing with the comings and goings of individual users in your organization!  It pushes that responsibility back onto the Domain Security Admins. 

Think about this for a second:  Bob is hired into the Accounting Department in your company and needs to have access to five different databases that are used in the department.  Even with Windows logins you would need to create a login and then create a user for that login in each database.  Compound that if the databases reside on different SQL Servers.  With a login based off of a Windows Group, you create a single login for the Group. 

Assign the rights accordingly, and then as Bob or other users transition in and out of departments or employment within the organization, the Domain Security Administrators add and remove them from the Windows Group that feeds the SQL login and database user in each database.  You as the DBA do not need to be notified when Bob leaves the company (in this example).  Furthermore, it reduces the use of logins by replacing tens, hundreds, or thousands of individual Windows-based logins in your SQL environment with a single login based off of the Windows Group.

The basic structure is outlined below:

SQL Server knows only of the Windows Group underlying the login.  It cares not for whom is a member of that group.  It assumes that it is all handled by the domain authentication process (remember the concept of trust?)  As individuals transition in and out of the group, you as the DBA don't know and don't care.  Even better, you don't need to do a darn thing