If you are like most, you probably have looked into using Windows Authentication as a method to authenticate users to SQL Server 2005. Windows Authentication is the preferred and more secure method of connecting to SQL Server. If your goal is to use Windows authentication for everything then under some situations it does present some challenges. One of those challenges is setting up linked servers to impersonate the local login when connecting to a linked server. This article will discuss how to set up delegation on your SQL Server instances so you can use the impersonate options when setting up the security properties of linked server definitions.
What is Delegation?
Delegation is when a middle tier server, impersonates the client login when connecting to a backend server. When users connect to a backend server through a middle server this is commonly called a double hop. In order to make Windows Authentication work in this situation the middle tier server need to impersonate the user when connecting to the backend machine. This impersonation allows the backend machine to know the login of the original user so queries can be run in the security context of the original user. For the purposes of this article, this means that when a client connects to SQL Server, the client login will be impersonated when using a linked server to connect to a backend SQL Server machine.
For the purpose of this article, I also need to provide you with a layout of the computer topology for this article. To help convey the hardware architecture for my delegation examples below please review the following diagram.
Here there are three machine involved, one user machine and two servers. The user machine is referred to as client and requires that the user needs to logon to the network with a domain account. For the purpose of this article, assume that user is SDS\GREG and the user connects to SERVER1 using windows authentication. The application on the client machine needs to be able to run a number of queries that retrieves data from both SERVER1 and SERVER2. Any time the application retrieves data from SERVER2 it will be done using a linked server from SERVER1. Each server is running a single default instance of SQL Server. All of the SQL Server services for SERVER1 instance are running under the domain account named SERVER1_DF, and all services on the SERVER2 instance are running under the SERVER2_DF domain account.
It is worth mentioning here that one problem I encountered while setting up delegation was associated with the account I used to run my SQL Server services. When I ran my SQL Server Services under the computer account, delegation wouldnt work. You need to make sure your services are running under an account that is different from the machine name, hence the reason my service account names are <machine name>_DF. The DF stands for default instance.
How to Setup Delegation
The setup of delegation is not complicated, but does require a number of steps. Keep in mind the method I will show you is specific to my SQL Server setup. If your setup is different, additional and/or different steps might be needed.
One of the first things you need to do is make sure all service and user accounts involved in your delegation situation are allowed to be delegated. Active directory definitions for accounts identifies whether or not an account can be delegated. Delegation is controlled via a check box within the Account Options section of the Account tab on the domain account properties window. The check box Account is sensitive and cannot be delegated needs to be unchecked. The following screen shot shows this for user SDS\GREG:
You need to make sure all accounts that will be using linked servers have the Account options set appropriately to allow their account to be delegated. In my example, you need to make sure both SERVER1_DF and SERVER2_DF have this check box, unchecked.
The next step to setting up delegation is to establish a Server Principle Name (SPN) entry for each SQL Server instance. To do this you use the SETSPN tool. This tool is part of the windows support tools, which can be downloaded from Microsoft for your version of Windows. For my two servers in the above diagram I ran the following set of SETSPN commands from the command prompt. Keep in mind these commands need to be executed under an account that has domain administration permissions.
SETSPN -A MSSQLSvc/SERVER1:1433 SDS\SERVER1_DF SETSPN -A MSSQLSvc/SERVER1.SDS.COM:1433 SDS\SERVER1_DF SETSPN -A MSSQLSvc/SERVER2:1433 SDS\SERVER2_DF SETSPN -A MSSQLSvc/SERVER2.SDS.COM:1433 SDS\SERVER2_DF
Here the first two commands define SPNs for SERVER1 and the second two commands for SERVER2. Note the first SPN command in each set registers the SPN by referencing just the machine name, and the second one identifies the fully qualified domain name of the server. To verify the SPNs are registered correctly for a service account you can run the following command:
SETSPN L SDS\SERVER1_DF
This command will list all the SPNs associated with domain account SDS\SERVER1_DF.
The next step in setting up delegation is to make sure the SQL Server service accounts are set up so they can perform delegation. To do this you set the appropriate delegation options for the SQL Server accounts under the Delegation tab when reviewing the domain account properties. Note the delegation tab will not be displayed for an account until the SETSPN command for that account has been established. So, in my example I need to set the delegation options for SERVER1_DF and SERVER2_DF accounts. There are two different options you can pick when setting the delegation options for an account, constrained and un-constrained. I decided to use constrained delegation for my set up, since that minimizes the number of services that can perform delegation. Below is a screen shot of the options I used to for setting up my SERVER1_DF services account:
Here you can see that I select Use Kerberos only radio button and then specified the specific service type that would be doing the delegation. For SQL Server the service type is MSSQLSvc. I also specified the computer name SERVER1 and the port that SQL Server is listening on.
You also need to verify that the computer account within Active directory is also set up to support delegation. To do this edit the computer properties in Active directory to look like this:
Here I have set up my SERVER1 machine to delegate using Kerberos, just as I did with the service account above.
Lastly, you need to verify that the local security policies on the middle tier server are set up to allow delegation. This is done by using the Local Security Policy tool under Administrative tools. Expand the Local Policy item under the Security section, and then expand the User rights assignment. Then double click on the Impersonate a client after authentication item to modify the properties. Use the Add Users of Groups button to add the account that the SQL Server services are running under. In my case that would be SERVER1_DF. After I added my account the Impersonate a client after authentication properties looks like this:
Here, I have added account SDS\SERVER1_DF to the local security policies on SERVER1, my middle tier server.
Once you have set up your accounts and machines you need to verify that delegation works using linked servers. To do this, log on to a client machine using a windows account. Make sure the account you use has a login established on your middle tier and backend SQL Server machines. Once logged on to your client machine, connect to your middle tier SQL Server machine. I normally do this using a client machine that has SQL Server Management studio installed. When I am connected, I then open a new query window and verify that I have connected to the middle tier server via Kerberos. To do this I issue the following command:
select auth_scheme from sys.dm_exec_connections where session_id = @@SPID
If the displayed auth_scheme is KERBEROS then I know I have successfully connected to the middle tier server using Kerberos authentication method. If NTLM is displayed for the auth_scheme then I know I did not successfully set up my middle tier server for delegation, and I go back to make sure I didnt miss a step.
Once I have successfully verified that I am connected to the middle tier server using Kerberos, then the final test I do is to submit a linked server request to my backend server. In my case, I would be submitting a linked server request to SERVER2. So for me to verify my delegation is set up I would issue the following command:
select name from SERVER2.master.sys.servers where server_id = 0
If delegation is set up correctly this command should return the name SERVER2. If delegation is not set up, an authentication error will be displayed.
Troubleshooting Delegation Setup
It isnt extremely straightforward to set up delegation. In fact, I had a number of failed attempts before I successfully set up my first set of SQL Server machines for delegation. To help me troubleshoot my delegation setup, I used the following document:
This document walks through a number of different situations and provides steps for verifying that your delegation setup is correct.
Being able to use windows authentication for linked servers provides a more secure architecture then defining login mappings. It also minimizes the work needed to set up and maintain linked server definitions. Setting up delegation does require a number of steps to successfully set up your servers, and possibly some troubleshooting but it is worth the effort. This article provides you with the steps and tools necessary to set up and troubleshoot setting up your SQL Server environment to use delegation.