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\SQLServiceAccountIf 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 SQLServiceAccountAlternatively, 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\SQLServiceAccountCheck 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_connectionsNext 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
