Export (0) Print
Expand All
48 out of 52 rated this helpful - Rate this topic

Security Account Delegation

SQL Server 2000

  New Information - SQL Server 2000 SP3.

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 register the SPN. When SQL Server shuts down, the SPNs will be unregistered for the LocalSystem account.

    Security Note  The LocalSystem account has elevated local privileges. Enable delegation using a domain user account whenever possible.

    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. If the NetBIOS name of the SQL server computer is the same as the domain name, do not specify the domain qualifier for the service account. You must use only the account name.

If the NetBIOS name of the SQL Server computer is not the same as the domain name, you must specify the domain qualifier for the service account, as in the following example:

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

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

Security Note  The LocalSystem account has elevated local privileges. Enable delegation using a domain user account whenever possible.

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.