Security Account Delegation

Security account delegation is the ability to connect to multiple servers, and with each server change, to retain the authentication credentials of the original client. For example, if a user (LONDON\joetuck) connects to ServerA, which then connects to ServerB, ServerB knows that the connection security identity is LONDON\joetuck.

To use delegation, all servers that you are connecting to must be running Microsoft® Windows® 2000, with Kerberos support enabled, and you must be using Microsoft Active Directory™, the directory service for Windows 2000. The following options in Active Directory must be specified as follows in order for delegation to work:

• The Account is sensitive and cannot be delegated check box must not be selected for the user requesting delegation.
  
  
• The Account is trusted for delegation check box must be selected for the service account of SQL Server.
  
  
• The Computer is trusted for delegation check box must be selected for the server running an instance of Microsoft SQL Server™.

To use security account delegation, SQL Server must have:

• A Service Principal Name (SPN) assigned by the Windows 2000 account domain administrator.

  The SPN must be assigned to the service account of the SQL Server service on that particular computer. Delegation enforces mutual authentication. The SPN proves that SQL Server is verified on the particular server, at the particular socket address, by the Windows 2000 account domain administrator. You can have your domain administrator establish an SPN for SQL Server with the setspn utility through the Windows 2000 Resource Kit.

  To create an SPN for SQL Server, enter the following code at a command prompt:

  setspn -A MSSQLSvc/Host:port serviceaccount
  

  For example:

  setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount
  

  For more information about the setspn utility, see the Windows 2000 documentation.

Before enabling delegation, consider the following:

• You must be using TCP/IP. You cannot use Named Pipes, because the SPN targets a particular TCP/IP socket. If you are using multiple ports, you must have a SPN for each port.
  
  
• You can also enable delegation by running under the LocalSystem account. SQL Server will self-register at service startup and automatically register the SPN. This option is easier than enabling delegation using a domain user account. However, when SQL Server shuts down, the SPNs will be unregistered for the LocalSystem account.

  Note  If you change service accounts in SQL Server, you need to delete any previous SPNs and create new ones.

Adding an SPN to SQL Server

To add an SPN on an instance of SQL Server named "myserver.microsoft.com", for an instance listening on port 1433, using service account MYDOMAIN\sqlsvc, run the following at a command prompt:

setspn -A MSSQLSvc/myserver.microsoft.com:1433 sqlsvc

You cannot use the Netbios name. You must use the fully qualified DNS name. You cannot specify the domain qualifier for the service account. You must use only the account name.

To change and use the LocalSystem account, enter the following code at a command prompt to delete the previously registered SPN :

setspn -D MSSQLSvc/myserver.microsoft.com:1433 sqlsvc

For more information about security account delegation, see the Windows 2000 documentation.