Service Principal Name (SPN) Support in Client Connections

Beginning with SQL Server 2008, support for service principal names (SPNs) has been extended to enable mutual authentication across all protocols. In previous versions of SQL Server, SPNs were only supported for Kerberos over TCP when the default SPN for the SQL Server instance was registered with Active Directory.

SPNs are used by the authentication protocol to determine the account in which a SQL Server instance runs. If the instance account is known, Kerberos authentication can be used to provide mutual authentication by the client and server. If the instance account is not known, NTLM authentication, which only provides authentication of the client by the server, is used. Currently, SQL Server Native Client performs the authentication lookup, deriving the SPN from the instance name and network connection properties. SQL Server instances will attempt to register SPNs on startup, or they can be registered manually. However, registration will fail if there are insufficient access rights for the account that attempts to register the SPNs.

Domain and computer accounts are registered automatically in Active Directory. These can be used as SPNs, or administrators can define their own SPNs. SQL Server makes secure authentication more manageable and reliable by allowing clients to directly specify the SPN to use.


An SPN specified by a client application is only used when a connection is made with Windows integrated security.

To manually create a domain user Service Principle Name (SPN) for the SQL Server service account

  1. Click Start, click Run, and then enter cmd in the Run dialog box.
  2. From the command line, navigate to Windows Server support tools installation directory. By default, these tools are located in the C:\Program Files\Support Tools directory.
  3. Enter a valid command to create the SPN. To create the SPN, you can use the NetBIOS name or the Fully Qualified Domain Name (FQDN) of the SQL Server. However, you must create an SPN for both the NetBIOS name and the FQDN.
    When you create an SPN for a clustered SQL Server, you must specify the virtual name of the SQL Server Cluster as the SQL Server computer name.

    • To create an SPN for the NetBIOS name of the SQL Server use the following command: setspn –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>
    • To create an SPN for the FQDN of the SQL Server use the following command: setspn -A MSSQLSvc/<SQL Server FQDN>:1433 <Domain\Account>

  1.  Verify that the command completed successfully by reviewing the command’s output for the updated object line.

To verify the domain user SPN is registered correctly using the SetSPN command

1.      Click Start, click Run, and then enter cmd in the Run dialog box.

2.      From the command line enter the following command: setspn –L <domain\SQL Service Account>

3.      Review the registered ServicePrincipalName to ensure that a valid SPN has been created for the SQL Server.

To verify the domain user SPN is registered correctly using the ADSIEdit MMC console

1.      Click Start, click Run, and then enter adsiedit.msc to launch the ADSIEdit MMC console.

2.      If necessary, connect to the site server's domain.

3.      In the console pane, expand the site server's domain, expand DC=<server distinguished name>, expand CN=Users, and then right-click CN=<Service Account User>. On the context menu, click Properties.

4.      In the CN=<Service Account User> Properties dialog box, review the servicePrincipalName value to ensure that a valid SPN has been created and associated with the correct SQL Server.

To change the SQL Server service account from local system to a domain user account

1.      Create or select a domain or local system user account that will be used as the SQL Server service account.

2.      Open SQL Server Configuration Manager.

3.      Click SQL Server 2005 Services, and then double click SQL Server<INSTANCE NAME>.

4.      On the Log on tab, select This account, and then enter the user name and password for the domain user account created in step 1 or click Browse to find the user account in Active Directory and then click Apply.

5.      Click Yes on the Confirm Account Change dialog box to confirm the service account change and restart the SQL Server Service.

6.      Click OK after the service account has been successfully changed.




Register a SPN for SQL Server Authentication with Kerberos

When it comes to configuring your SQL Servers to use Kerberos authentication there are a couple of prerequisites that must be met. First, the clients and servers must be joined to a domain. If they are joined, but they are in different domains then a two-way trust must be setup between these domains. Secondly an SPN must be successfully registered for the SQL Server service so that it can be identified on the network. The first requirement is pretty easy to validate so let's concentrate on the second one.

There are a few ways that we can check if the SPN has been registered successfully. If your SQL Server instance is running under a domain account (which is recommended) you can run the following command to see the services that are registered. If there are no services registered for this account you will get the error message below the command.

C:\Users\test>setspn -l DOMAIN\SQLServiceAccount
FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x00000525
Could not find account SQLServiceAccount

Alternatively, you can also use the SQL Server error log to validate if the SPN has been registered successfully or not. By either filtering within the SSMS GUI or using xp_read_errorlog we can search for the string below. If found we know the SPN did not register successfully.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the 
SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated 
authentication to fall back to NTLM instead of Kerberos. This is an informational message. 
Further action is only required if Kerberos authentication is required by authentication 

Finally, you can contact your system administrator and have them use the ADSIEdit MMC console to manually check if the service is registered.

Now that we've identified the issue we can go through a couple of different options that will allow us to successfully register the SPN and use Kerberos authentication.

Option 1 - Register SPN automatically

To enable the SPN to be registered automatically on SQL Server startup the service must be running under the "Local System" or "Network Service" accounts (not recommended), under a domain administrator account, or under an account that has permissions to register an SPN. The permissions required for this are the "Read servicePrincipalName" and "Write servicePrincipalName" access control settings in the Active Directory service. One thing that should be noted is granting these rights is not recommended (see http://support.microsoft.com/kb/319723) if SQL Server is clustered or if you have multiple domain controllers as latency in Active Directory replication can cause connectivity issues with your SQL Server instance.

Option 2 - Register SPN manually

To register an SPN manually we can use the Microsoft provided Setspn.exe utility. To be able to run this tool and register an SPN you need to be a domain admin or have the appropriate privileges (defined above). One other thing to note is that the -s option ensures that the SPN you are trying to create is not already defined. Here are a couple examples. The first one is for a default instance and the second is for a named instance.

setspn -s MSSQLSvc/myhost.redmond.microsoft.com DOMAIN\SQLServiceAccount
setspn -s MSSQLSvc/myhost.redmond.microsoft.com:instancename DOMAIN\SQLServiceAccount

Once you've picked and implemented one of these options and if necessary restarted SQL Server you can establish a new connection and run the following TSQL to check that you are now using Kerberos authentication.

select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections


No comments:

Post a Comment

Popular Posts