Register a SPN for SQL Server Authentication with Kerberos

Problem

After querying SQL Server sys.dm_exec_connections DMV, I noticed all my connected sessions use Windows Authentication with NTLM and not Kerberos. How do I get them to use Kerberos?  Check out this tip to learn more about how to register SPN for SQL Server.

Solution

To configure 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 to be identified on the network. The first requirement is pretty easy to validate so let’s concentrate on the second one.

We can apply a few ways to 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 in a CMD window to see the services that are registered.

setspn -l DOMAIN\SQLServiceAccount

If there are no services registered for this account you will get the error message below the command.

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

Check SPN Status

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

Ben Snaidero

Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.

• MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017