Problem We'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.
Solution First 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:
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.
So let's recap.
SQL Server allows you to have two types of logins: those based upon Windows accounts and those that are based solely upon creation of a login and password within SQL Server.
Windows-based logins are preferred when possible because it removes a layer of authentication, it streamlines the login process, and prevents the DBA having to be the go-to source for remembering passwords.
Windows-based logins can be created from Windows Groups, which takes the process one step further by eliminating the need for the DBA to be called into action when users' database needs change. Windows Group-based logins are preferred, but with the caveat that fully-qualified naming is used when referencing database objects, or so long as database objects are discretely named across all schemas.
This concludes our first Best Practices Tip on SQL Server Logins. Please stay tuned to MSSQLTips.com for more Best Practices tips. I've also provided links below that may be appropriate to the discussion.
Thanks for the article on using Windows groups, Tim!
As a software developer (not a DBA expert) working on an applications that use SQL Server as the database server, one concept I've often heard told to the clients when setting up users for SQL Server is that they should set up a separate user for each end user, so that they will be able to tell who performed given actions when looking at any audit logs or anything. The idea was that if everyone logged in using a single shared user, there would be no way to determine which person actually performed a given action.
Is that not an issue when using the Windows groups solution, since SQL Server only sees the one login/user associated with the group? It certainly seems to lead to cleaner management of privilages and access. Maybe the need to determine which individual was performing a specific action was being overstated?
Thank you again for the great article.
Friday, September 04, 2009 - 5:13:09 AM - timmer26
OK I think I get what you are saying here. When you create a table you should specify the proper schema to use instead of letting it use the default user schema. Is this correct?
The problem is, and this is specific to create ESRI SDE feature class tables, is that it is done through the application. I do not think thier is a way to specify the schema, it just uses the default one associated with that particular logged in user.
So in this instance it is poor application control, correct?
Thursday, September 03, 2009 - 9:20:35 AM - timmer26
The issue with dbo schema being required in you application highlights the concern about best practices (ha! our root topic!) around developing with fully-qualified naming. In this case it sounds like your application is making calls to objects without a defined schema: SELECT * FROM tableA instead of SELECT * FROM schemaX.tableA for example. Therefore SQL Server has to make the assumption the the dbo schema is to be used if both dbo.tableA and schemaX.tableA both exist in the database.
If you're developing an application for use and separating objects out in schemas, there application's schemas should be organized by a role or business purpose, not by a developer name such as accounting.users or payroll.history. Not dave.users and paul.users or paul.history.
Peteo, that is why I mentioned that you really need to make sure that usage of fully-qualified naming is enforced when using groups. The users need to explicitly use the schema name as a prefix when querying or else unintended results will occur if, for instance you have dbname.schema1.tableX and dbname.schema2.tableX.
Ok here is the problem I have had with groups. You cannot assign a default schema to the group. Therefore if you have, say, an admin group that is creating tables in a particular database you will all of a sudden have the named schema from each user assigned to the tables they create. This looks ugly real quick.
It is possible that there is an easy solution to this but I cannot find one. If you have any further insight because we would LOVE to use this for management!!
Thursday, September 03, 2009 - 6:39:17 AM - LeeFAR
If you use Windows security to grant permissions to your databases, how do you then prevent users from accessing the database and its objects when the users use something other than the intended application? For example, let's say you have a custom application and that has a SQL backend. And you have an AD group that you put users into and the permission that AD group to the database and the objects (stored procedures). Now when the users run the custom application, the app connects using the Windows identity of the user. But now let's say that user goes into something like Excel and connects to your database. How do you prevent that person from executing the stored procedures? For more thoughts, look at this MSDN post.