SQL Server and Kerberos

One side effect of following the best practices and running SQL Server under a Domain User account is that “Kerberos” authentication may not work due to the fact that the SPN cannot be registered.

To step back a bit – what’s an SPN? This is a mapping maintained in Active Directory between a service account and a service on a specific port number. This allows the client to be assured that the service it is connected to has not been spoofed and that the account that is listening on the service is the correct one.

Why do I need Kerberos ? well there are some very minor performance advantages as it is not as chatty to the Domain Controllers, but the main advantage is that it allows for reporting services to pass identity down to a data source without the “double hop” issue (see https://weblogs.asp.net/owscott/archive/2008/08/22/iis-windows-authentication-and-the-double-hop-issue.aspx)

The SPN can ONLY be registered in Active Directory by an account with “Validated write to service principle name” permission. Usually this is a domain Administrator.

How do I know if Kerberos is working properly ?

you can query the current connection or all connection and check if NTLM or KERBEROS have been used for authentication

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

you may also see an error message in the SQL Error log
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. 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

How do I fix this

There are three methods:

a) quickly add the service account to the Domain Admin’s and restart SQL Server then remove from group – might raise a few eye brows but hey it works

b) manually register the SPN using an appropriate account like Domain Admin – maybe in a larger firm someone might need to do this for you

setspn -A MSSQLSvc/mysqlservername:1433 DOMAIN\SERVICEACCOUNT

c) Grant the computer account the rights to set its own SPN. This is done via the “Validated write to service princ” property in Active Directory users and Computers Tool.

What about Analysis Services?

One key difference between the DBEngine and Analysis Services seems to be that Analysis Services will not automatically try and add its own SPN if it is missing, so you will need to manually configure the SPN as per KB article below
https://support.microsoft.com/kb/917409