SQL SERVER – Why DBAs Need to Know about Kerberos – Notes from the Field #126

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.

Most DBAs deal with Kerberos when they administer SSRS, but you can encounter problems related to Kerberos authentication even if you are not using SSRS. Have you ever changed the service account on one of your SQL Servers? Doing so could actually keep you from connecting to that SQL Server from another computer in your domain or joining it to an Availability Group.

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

MSSQLSvc/SQL1.MyDomain.Local:1433
MSSQLSvc/SQL1.MyDomain.Local

The SPNs for the named instance are

MSSQLSvc/SQL1.MyDomain.Local:49200
MSSQLSvc/SQL1.MyDomain.Local:INST2

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.