Register a SPN for SQL Server Authentication with Kerberos

Problem

After querying the SQL Server sys.dm_exec_connections DMV I noticed that all my currently connected sessions using Windows Authentication had used NTLM and not Kerberos. How do I get them to use Kerberos? Check out this tip to learn more.

Solution

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 
policies.

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

Next Steps

Read more on the pros and cons of using Kerberos or NTLM authentication
Understand different SQL Server network protocols
Understand different SQL Server authentication modes

About the author

Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Tuesday, January 8, 2019 - 4:04:22 AM - Shashi Kant	Back To Top (78659)
Please make sure there is no data and time difference from AD server. SQL server and AD server time should be in sync. If there is a difference in time more then 5 to 10 min then these error should come even your SPN registered. AD won't allow for Kerbros authentication.

Monday, September 16, 2013 - 9:59:16 AM - John Langston	Back To Top (26811)
I have run into a feature of NETBIOS relative to server names that impacts proper SPN registration for Kerberos. There is a fifteen (15) character name limitation. Anything beyond 15 characters is truncated, so manual registration will be required (and the truncated server name that is registered will need to be removed).

Thursday, June 27, 2013 - 9:47:23 AM - zzx375	Back To Top (25598)
Server names that exceed the NetBios name length of 16 characters will need to have their SPN explicitly created. We discovered this after the corner office wanted server names exceeding the NetBios limit. Microsoft has an SSPI executable (got my copy after a PSS call) that helps diagnose SPN issues.