Registering Kerberos Service Principal Names by Using Http.sys
Native XML Web Services is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
When you create or modify HTTP endpoints by using CREATE ENDPOINT or ALTER ENDPOINT, you specify the authentication type that is used to authenticate users that are sending HTTP SOAP request to the endpoint. For more information, see CREATE ENDPOINT (Transact-SQL) and ALTER ENDPOINT (Transact-SQL).
By using CREATE ENDPOINT ALTER ENDPOINT, you can configure endpoints to support Kerberos authentication in the following ways:
By setting AUTHENTICATION=KERBEROS, Kerberos is used as the sole means of HTTP authentication
When AUTHENTICATION=INTEGRATED, HTTP authentication for the endpoint can offer the following options as part of the authentication challenge: NEGOTIATE, KERBEROS, and NTLM. For the NEGOTIATE option, client and server try to establish Kerberos-based authentication. If Kerberos is not supported by the client or negotiation is not possible, authentication will fall back to NTLM. To prevent having the client fall back to NTLM when you use NEGOTIATE, we recommend that the client set AUTHENTICATION=KERBEROS on the endpoint.
To support mutual authentication under Kerberos, an instance of SQL Server must associate a Service Principal Name (SPN) with the account it will be running on, such as a local system account or domain user account. The specific details for SPN registration by the specific instance of SQL Server are determined by the type of service account it has been configured under. If SQL Server is running under the local system account or network service account, SPNs must be registered under the computer name. If SQL Server is running under a domain user account, SPNs must be registered under the domain user name.
Using SetSPN.exe
To enable association of an SPN to the account that the instance of SQL Server is running on, use the Windows SetSPN.exe support tool. This tool adds the SPN for the computer name on which the SQL Server instance is running under the Windows domain service user account located in Active Directory. In this scenario, the SetSPN.exe tool can be used to add two SPNs: one for the NetBIOS name and another for the fully qualified DNS name.
For example, if the SetSPN.exe tool is executed from the SQL Server instance running on MyComputer, the following two SPNs are associated with the account the SQL Server instance is running under and must be added to directory:
HTTP/MyComputer;
HTTP/MyComputer.fully.qualified.domain.name.com
Note that a single account may have multiple SPNs, but an SPN can only be registered for one account.
To delete these same two SPN names, both NetBIOS and fully qualified DNS, also use the SetSPN.exe.
Considerations
Similar to Httpcfg.exe, SetSPN.exe is available with Windows Server 2003 and is installed when you use the same procedure to install Httpcfg.exe and other Windows Support Tools. For more information, see Configuring the HTTP Kernel-Mode Driver (Http.sys).
If a SQL Server instance is not running as the local system account, only integrated authentication users that have DOMAIN ADMIN privileges can change SPN registration by using the SetSPN.exe tool.
If a SQL Server instance is running as the local system account, only members of the SQL Server sysadmin fixed server role can change SPN registrations by using the SetSPN.exe tool.
If the service account is Local System, the SPN is added in the Active Directory account of the computer without having to use the SetSPN.exe tool.
Syntax for SetSPN.exe
The syntax for SetSPN.exe is:
setspn { -ASPN | -DSPN | -L } service_account
Arguments
-A
Adds the specified SPN to the account.-D
Deletes the specified SPN to the account.-L
Lists all SPNs registered to the account.
Examples
If an instance of SQL Server is running as a domain user (MyDomain\MySQLAccount) on a computer that is named MySQLHost, the following commands can be used to set the appropriate SPNs:
setspn –A http/MySQLHost MyDomain\MySQLAccount
setspn –A http/MySqlHost.Mydomain.Mycorp.com MyDomain\MySQLAccount
Note that one account can have multiple SPNs (one for each service or host name), but an SPN can be registered under only one account. Having the same SPN registered on multiple accounts causes Kerberos authentication to fail.
For example, the account MyDomain\MySQLAccount can have the following different SPNs registered on it. The first two commands are for two different services (http and rpc). The last one is for a different host name, assuming the computer has multiple host names.
setspn –A http/MySQLHost MyDomain\MySQLAccount
setspn –A rpc/MySQLHost MyDomain\MySQLAccount
setspn –A http/MySecondHost MyDomain\MySQLAccount
Conversely, the following scenario will cause a Kerberos failure:
setspn –A http/MySQLHost MyDomain\MySQLAccountOne
setspn –A http/MySQLHost MyDomain\MySQLAccountTwo
Failure occurs because there are two instances of SQL Server on a computer that is running under two different service accounts (MySQLAccountOne and MySQLAccountTwo). Registering both SPNs, one for each instance of SQL Server is not a supported scenario.
This has implications when multiple instances of SQL Server that are run on the same computer under different accounts. The SPN can only be registered for one account. If you require multiple instances of SQL Server (for example, Inst1 and Inst2) to coexist beside other applications (such as IIS) and you want to use HTTP Kerberos authentication for all services, use one of the following options to resolve SPN registration conflicts:
Have all instances and applications run as the same account.
For example, have Inst1, Inst2 and IIS all run as LocalSystem or Mydomain\MyServiceAccount.
Register multiple host names for the same computer and have each instance and application listen on a different host. Therefore, in this case, you would have to do the following:
Create three different host names for the computer.
Assign each host to a different application.
Register three sets of SPNs, one for each host name/application combination.
Troubleshooting Kerberos SPN Registration Issues
The most common troubleshooting issues for Kerberos SPN registration are the following:
An SPN is not registered.
When an SPN is not registered, Kerberos authentication will work from the local computer on which the instance of SQL Server is running, but will fail at remote client computers.
An SPN is registered more than one time.
There are several scenarios where an administrator can duplicate the service-principal-names (SPN) in the domain directory that will cause Kerberos authentication to fail. These include the following:
Making changes to the domain account under which the instance of SQL Server runs
If SetSpn.exe is run while running an instance of SQL Server as one domain account, such as DOMAIN\User1, and then the domain account that is used to run SQL Server is changed, such as DOMAIN\User2, when SetSPN.exe is run again, it will cause the same SPN to be inserted in the directory under both accounts.
Installing multiple instances of SQL Server that run under different accounts
If you install multiple instances of SQL Server and then run each instance under a different account, if SetSpn.exe is run on each instance, there will be duplicate accounts in the directory under each SQL Server service account. This applies for both instances that are running under a domain user and also the local System account.
Removing and reinstalling an instance of SQL Server under a different account
If you install SQL Server under one account, register the SPNs, remove and reinstall SQL Server under a different account, and then reregister the SPNs, each domain account will have the same SPNs. This means the SPNs will be duplicated.
In each of these scenarios, the issue is that the HTTP endpoint will fall back to NTLM authentication until the issue is resolved. This typically involves searching the directory for the duplicate or stale SPNs and manually removing them.