Kerberos for SQL Server: SPNs, a static port and a DNS name

A database server with several named instances has been running for months, applications connect with domain accounts and nobody reports problems. While configuring a linked server that should pass the user’s identity on to a second instance, the query fails with Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. A look at the connections reveals the cause: every session uses NTLM, and NTLM cannot pass an identity any further.

Windows authentication in SQL Server does not stop working when Kerberos fails. The client silently falls back to NTLM, and the problem only surfaces with delegation, an audit or NTLM restrictions in the domain. Kerberos works when three elements match: the name the client connects to, the instance port, and the SPN registered on the service account. This post shows how to line them up.

How the client chooses between Kerberos and NTLM

With Windows authentication, the client driver asks the domain controller for a ticket for a service with a specific name, the SPN (Service Principal Name). The domain controller looks up the account that has this SPN in its servicePrincipalName attribute and encrypts the ticket with that account’s key. SQL Server decrypts the ticket with the key of the account the service runs as. For SQL Server, the SPN takes one of two forms:

MSSQLSvc/sql-crm.corp.example.com:14331     TCP connections: name and port
MSSQLSvc/srv-sql-01.corp.example.com:CRM    named instance over named pipes

The driver does not take the name directly from the connection string. It first resolves it in DNS and uses the canonical name, and it takes the port from the connection string or from the SQL Browser service when the client connects with server\INSTANCE. If the domain controller cannot find an SPN with the name built this way, the Negotiate mechanism picks NTLM without any message. The connection works, but without mutual authentication of the server and without delegation, so linked servers and multi-tier applications pass on an anonymous logon.

Symptom	Most common cause
auth_scheme equal to NTLM on a connection from another machine	no SPN for the name and port the client uses
Cannot generate SSPI context	SPN registered on an account other than the service account, or the same SPN on two accounts
NTLM only when connecting through an alias	the alias is a CNAME record, or there is no SPN for the alias name
NTLM when connecting by IP address	default behaviour: Windows does not use Kerberos for an IP address
NTLM after an instance restart	dynamic port different from the port in the registered SPN
ANONYMOUS LOGON on a linked server	first connection authenticated with NTLM, or delegation not configured

The service account

The key needed to decrypt the ticket belongs to the account the SQL Server service runs as, so the SPN must be registered on that account. A group managed service account (gMSA) works best: the domain controller changes the password, nobody knows it, and one account can serve instances on several servers. In the examples in this post, all instances run as CORP\svc-sql$. Creating and deploying the account is covered in the fifth part of this guide, A gMSA as the SQL Server service account.

Why SQL Server does not register the SPN itself

At startup, the SQL Server service tries to register its SPNs on the service account and to remove them when it stops. This only succeeds if the account has the right to read and write the servicePrincipalName attribute on itself. Neither a gMSA nor a regular domain account has it by default, so the SQL Server error log contains an entry like this:

The SQL Server Network Interface library could not register the Service Principal Name (SPN)
[ MSSQLSvc/srv-sql-01.corp.example.com:CRM ] for the SQL Server service. Windows return code: 0x2098,
state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.

Code 0x2098 means insufficient access rights in the directory. The right needed for automatic registration is granted with:

dsacls 'CN=svc-sql,CN=Managed Service Accounts,DC=corp,DC=example,DC=com' /G 'SELF:RPWP;servicePrincipalName'

Automatic registration has limitations, however, which make manual registration the better choice for named instances:

Aspect	Automatic registration	Manual registration
Rights required	the service account writes its own servicePrincipalName attribute	an administrator registers the SPN once; the service account needs no extra rights
Name in the SPN	the server name only	any name with an A record, including an instance name
Dynamic port	supported, SPN refreshed at every start	requires a static port
Risk	the service account can add any SPN to itself, including one belonging to another service	none

A static port instead of a dynamic one

A named instance listens on a dynamic port by default. SQL Server picks it at first start and tries to keep it, but after a restart the port may change if another process has taken it. Clients find the port through SQL Browser (UDP 1434), so the connection still works, but a manually registered SPN and the firewall rule no longer match.

The static port is set in SQL Server Configuration Manager: SQL Server Network Configuration, Protocols for CRM, TCP/IP, the IP Addresses tab, the IPAll section (with the default Listen All set to Yes). The TCP Dynamic Ports field must be empty, and TCP Port holds the port number. The same in PowerShell:

$instance = 'CRM'
# Instance ID, e.g. MSSQL16.CRM, read from the registry
$id = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$instance
$ipAll = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$id\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"

Set-ItemProperty -Path $ipAll -Name TcpDynamicPorts -Value ''
Set-ItemProperty -Path $ipAll -Name TcpPort -Value '14331'

# The new port takes effect only after a service restart
Restart-Service -Name 'MSSQL$CRM' -Force

Clients connecting with name,port do not need the SQL Browser service. It is only needed for connections in the form server\INSTANCE.

A dedicated DNS name for each instance

Several instances on one server can be published under the server name with different ports. A separate name for each instance is more convenient, however. The application then does not know the server name, and moving the instance to another server comes down to changing a DNS record. When both servers use the same gMSA account, the SPN stays unchanged as well.

Instance	DNS name (A record)	TCP port	SPN
CRM	sql-crm.corp.example.com	14331	MSSQLSvc/sql-crm.corp.example.com:14331
ERP	sql-erp.corp.example.com	14332	MSSQLSvc/sql-erp.corp.example.com:14332
HR	sql-hr.corp.example.com	14333	MSSQLSvc/sql-hr.corp.example.com:14333

# All names point to the address of srv-sql-01
'sql-crm', 'sql-erp', 'sql-hr' | ForEach-Object {
    Add-DnsServerResourceRecordA -ZoneName corp.example.com -Name $_ -IPv4Address 10.10.20.11 -ComputerName dc01.corp.example.com
}

The application’s connection string then contains the full name and the port, for example Server=sql-crm.corp.example.com,14331.

Registering SPNs manually

SPNs are registered on the service account with setspn. The -S switch checks before adding whether the SPN already exists on another account and refuses if it does. Registration requires domain administrator rights or a delegated right to write servicePrincipalName on the service account.

setspn -S MSSQLSvc/sql-crm.corp.example.com:14331 'CORP\svc-sql$'
# Only when clients connect over named pipes
setspn -S MSSQLSvc/srv-sql-01.corp.example.com:CRM 'CORP\svc-sql$'

The remaining switches are for checking: -L lists the account’s SPNs, -Q shows which account holds a given SPN, and -X searches the domain for duplicates. When an instance moves to a server with a different service account, the SPN is first removed from the old account (setspn -D) and only then added to the new one.

Delegation for linked servers

Kerberos is a necessary condition for the scenario at the start of this post, but not a sufficient one. When the CRM instance runs a query on a linked server in the ERP instance on behalf of a user, the CRM service account must have the right to pass that user’s identity on to the ERP service. The safe option is Kerberos-only constrained delegation: the service account can delegate only to the listed SPNs, and only on the basis of a ticket the user presented.

# The service account may pass user identities on to the ERP instance only
Set-ADServiceAccount -Identity svc-sql -Add @{ 'msDS-AllowedToDelegateTo' = 'MSSQLSvc/sql-erp.corp.example.com:14332' }

An entry in msDS-AllowedToDelegateTo without the TrustedToAuthForDelegation flag means Kerberos-only delegation, without protocol transition. The linked server login must use the user’s current security context: in SSMS this is the “Be made using the login’s current security context” option, in T-SQL sp_addlinkedsrvlogin with @useself = 'TRUE'. The identity cannot be passed on for accounts with “Account is sensitive and cannot be delegated” set, or for members of the Protected Users group, which is the intended behaviour for administrative accounts.

Do not enable unconstrained delegation instead (in the console: Trust this user for delegation to any service). The service account could then act on behalf of every signed-in user towards any service in the domain.

The firewall through Group Policy

With static ports, the firewall on SQL servers needs simple port rules instead of program rules for each instance’s sqlservr.exe. The rules are best kept in one GPO linked to the organisational unit of the SQL servers. Cmdlets from the NetSecurity module write them directly into the GPO when the -PolicyStore parameter points to the domain and the object name:

$gpo = 'corp.example.com\SQL Servers - Firewall'

New-NetFirewallRule -PolicyStore $gpo -DisplayName 'SQL Server - instances (TCP 14331-14333)' `
    -Direction Inbound -Protocol TCP -LocalPort '14331-14333' -Action Allow -Profile Domain

# Only when clients connect with server\INSTANCE
New-NetFirewallRule -PolicyStore $gpo -DisplayName 'SQL Server Browser (UDP 1434)' `
    -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow -Profile Domain

The -RemoteAddress parameter narrows a rule down to the subnets where the applications run. Reading the rules from the GPO works the same way: Get-NetFirewallRule -PolicyStore $gpo. The -GPOSession parameter takes a session opened earlier with Open-NetGPO, not a GPO name, which is why -PolicyStore is simpler for individual operations.

Check your own environment

The service account’s SPNs, the owner of a specific SPN, and duplicates in the domain. A good result is one MSSQLSvc/name:port SPN for each name and port the clients use, Existing SPN found! on the correct account, and found 0 group of duplicate SPNs in the -X output.

setspn -L 'CORP\svc-sql$'
setspn -Q MSSQLSvc/sql-crm.corp.example.com:14331
setspn -X

Test from a client computer, not from the SQL server: a connection from the server itself shows NTLM even with a correct SPN. klist get asks the domain controller for a ticket for the instance SPN, and an error means no ticket was issued, most often because the SPN is missing. The sqlcmd query should return KERBEROS, TCP and the instance port.

klist get MSSQLSvc/sql-crm.corp.example.com:14331

# -E: Windows authentication; -C: trust the server certificate when it is self-signed
sqlcmd -S sql-crm.corp.example.com,14331 -E -C -Q "SELECT auth_scheme, net_transport, local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID;"

Current TCP connections using Windows authentication, and SPN entries in the current SQL Server error log. Every NTLM row from another machine is a client connecting by a name or port without an SPN, through a CNAME alias, or by IP address. In the log, successfully registered means automatic registration is working, and code 0x2098 is expected with manually registered SPNs.

SELECT s.login_name, s.host_name, c.client_net_address, c.auth_scheme, c.local_tcp_port
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
WHERE c.net_transport = 'TCP' AND c.auth_scheme <> 'SQL'
ORDER BY c.auth_scheme, s.host_name;

EXEC xp_readerrorlog 0, 1, N'SPN';

Delegation settings of the service account. A good result is False in both TrustedForDelegation and TrustedToAuthForDelegation, and only the SPNs of instances that really need the user’s identity in msDS-AllowedToDelegateTo. True in TrustedForDelegation means unconstrained delegation that should be turned off.

Get-ADServiceAccount -Identity svc-sql -Properties TrustedForDelegation, TrustedToAuthForDelegation, 'msDS-AllowedToDelegateTo' |
    Select-Object Name, TrustedForDelegation, TrustedToAuthForDelegation, 'msDS-AllowedToDelegateTo'