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

SQL SERVER - Why DBAs Need to Know about Kerberos - Notes from the Field #126 Kathi [Note from Pinal]: Not everyone knows about Kerberos. Many think it is a two headed dog from mythology, well that is true but when we talk about database, it is all about security. In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about Why DBAs Need to Know about Kerberos. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.


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.

SQL SERVER - Why DBAs Need to Know about Kerberos - Notes from the Field #126 3wolfs-800x792 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:

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

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.

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

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:

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

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.

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

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), Twitter

Notes from the Field, SQL Function, SQL Server Security
Previous Post
SQL SERVER – What is Trace Flag – An Introduction
Next Post
SQL SERVER – SSMS Tip: How to Get Larger Fonts for Results in Grid Output

Related Posts

5 Comments. Leave new

  • Very Good Article

    Reply
  • Stephen Mangiameli
    June 1, 2016 8:34 pm

    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.

    Reply
  • 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 :)

    Reply
  • Hello Sir,
    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.

    Reply

Leave a Reply