SQL Server and Kerberos: Please do not forget the Service Principal Names

When using Windows authentication for a connection to a Microsoft SQL Server, Kerberos is the first choice. Only if the use of this authentication scheme is not possible, NTLM (NT LAN Manager) is used. This is transparent for the user, and NTLM does not impose any restrictions in most cases. Therefore, in many environments it is not even noticed that Kerberos is not configured correctly and therefore cannot be used.

Is Kerberos used?

Kerberos is used only for TCP/IP connections and only when authenticating with a domain account. In a connection established in this way, you can determine the authentication scheme with the following command:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID 

If “KERBEROS” is returned here, everything is fine. In case of “NTLM” please read on, because then Kerberos could not be used.

As a user of the PowerShell module dbatools, you can also use the command Test-DbaConnection to determine.

Is there a matching service principal name?

If Kerberos is not used, the required Service Principal Names (SPN) are usually missing in the Active Directory. This is because the SQL Server attempts to create the SPNs itself at every startup. In many cases, however, the necessary rights are missing.

When using a local service account for the SQL Server, the creation of the SPNs is done via the computer account, and this account typically has the required rights in Active Directory. However, when using a domain user as a service account, in many cases it is forgotten to give it the required rights. Which SPNs have been registered for the domain user used can be determined using the “setspn.exe” command with the “-L” parameter. Alternatively, the Test-DbaSpn command from the PowerShell module dbatools can be used.

In addition to the automatic creation of the SPNs, there is also the option to have an administrator create the SPNs manually in Active Directory. The command “setspn.exe” or the PowerShell module dbatools with the command Set-DbaSpn helps here as well. However, especially when using a domain user that is used as a service account for multiple SQL Server instances, it is then necessary to remember to create the SPNs again for each new installation. Therefore, our recommendation in this case is to provide the account with the necessary rights and let the SQL Server create the necessary SPNs itself.

Best Practice Configuration

To give the domain user used as a service account the required permissions, simply run this PowerShell command as Active Directory Administrator:

dsacls.exe (Get-ADUser -Identity <Insert service account name here>).DistinguishedName /G “SELF:RPWP;servicePrincipalName”

The documentation of the dsacls program can be found here. The parameter “/G” stands for granting rights, “SELF” refers to the specified account itself, “RP” and “WP” stand for reading as well as writing a property, “servicePrincipalName” names the corresponding property.

Test the success

After restarting the service, SQL Server should now be able to register the required SPNs itself at every startup. You can check this in the Windows Event Viewer or in the ERRORLOG file. I like to use PowerShell to access the event viewer. So here, as an example, the command to get from the default instance of computer SQL01 all messages where the term “SPN” appears:

Get-EventLog -ComputerName SQL01 -LogName Application -Source MSSQLSERVER -Message *SPN*

Create an overview

To get an overview of all SPNs related to SQL Server across the entire Active Directory, this PowerShell code can be used:

$adObjectWithSQLServerSPN = 
    (Get-ADComputer -Filter '*' -Properties servicePrincipalNames) + 
    (Get-ADUser -Filter '*' -Properties servicePrincipalNames) | 
    Where-Object servicePrincipalNames -match ^MSSQLSvc |
    ForEach-Object {
        [PSCustomObject]@{
            ObjectClass       = $_.ObjectClass
            Name              = $_.Name
            DistinguishedName = $_.DistinguishedName
            SQLServerSPNs     = $_.ServicePrincipalNames | Where-Object { $_ -match '^MSSQLSvc' }
        }
    }
$adObjectWithSQLServerSPN | Out-GridView
 

Summary

With proper configuration of the service account, the automatic creation of SPNs by SQL Server can be used to automatically use Kerberos for all connections.