Most database professionals avoid Kerberos like, well, the three-headed guard dog, Cerberus, that the technology is named after. Most DBAs don’t have the permissions to change the settings in Active Directory (AD) that affect Kerberos, but I believe that DBAs should understand Kerberos authentication so that they can help troubleshoot issues that come up.
In order for Kerberos authentication to work, a Service Principal Name (SPN) must be registered for the SQL Server service. The SPN can be seen in AD as a property of the service account. If the service account for the SQL Server instance is local, such as Network Service, then the SPN is a property of the computer object.
In this example, I’ll demonstrate how to see the settings, and what happens when I change the service account.
My server is named SQL1 with two SQL Server instances. The Network Service account is being used for the service account. To see the SPNs registered to SQL1 I use the setspn utility with the L switch. You can use setspn with a DOS or PowerShell window.
Setspn –L SQL1
Here are the results:
An SPN is made up of the service, a slash, and the fully qualified server name. For SQL Server, the service is MSSQLSvc. Each instance requires two SPNs: one with the instance name and one with the port number. The SPNs for the default instance are
The SPNs for the named instance are
To demonstrate that Kerberos authentication is being used, I connect to the default instance of SQL1 from another server SSRS and run this query:
SELECT s.host_name, auth_scheme FROM sys.dm_exec_connections AS C JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id;
In the results, you can see that connections from the local machine use NTLM authentication, but from another server use Kerberos.
To see what can happen, I change the service account to MyDomain\SQLService and try again to connect from SSRS. If the attempt is made quickly, it may be successful because Kerberos tickets are cached. Caching is one of the benefits of Kerberos authentication: it’s more efficient because it cuts down on traffic to the domain controller. To view and remove the cached tickets run this in a command or PowerShell window.
Klist Klist purge
Now, when trying to connect to SQL1 from the SSRS server, this error is encountered:
This means that an SPN exists for the service, but it is attached to the wrong account. In this case, the SPN is a property of the SQL1 computer object, but it should be attached to the sqlservice account. This error is hard to figure out if you don’t understand Kerberos.
To clear up this problem, the incorrect SPNs must be removed (D switch) and then replaced with the correct SPNs (A or S switch). Here is the script:
setspn -D MSSQLSvc/SQL1.MyDomain.Local SQL1 setspn -D MSSQLSvc/SQL1.MyDomain.Local:1433 SQL1 setspn -S MSSQLSvc/SQL1.MyDomain.Local MyDomain\SQLService setspn -S MSSQLSvc/SQL1.MyDomain.Local:1433 MyDomain\SQLService
The result “Updated object” shows that the commands were successful.
Immediately after running the script, it’s possible to connect to the instance again.
If you would like to learn more about Kerberos authentication and delegation, especially as it relates to SSRS, be sure to check out my course on Pluralsight.
If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Very Good Article
DBAs will ultimately, eventually learn about Kerberos. Do yourself a favor and be proactive about gaining the knowledge; don’t be like me! Authentication, especially via Kerberos, can get tricky fast when you are using C names and aliases.
Nice article but I would have loved to see some more info on how SQL tries to register the SPN itself and what to do when it fails. Who/what needs which permissions to make the registration successful? Kerberos is indeed an obscure matter to me :)
This article was really helpful for me to understand how SPNs should be registered. Thanks for sharing this knowledge. I am dealing with kind of same situation but the only difference is our services are in cluster. So I believe any changes we apply, that should be done on both the nodes?
We are using a domain account for SQL service startup account.
SPN needed for SQL network name.