04 January,2021 by Tom Collins
There is a message found in SQL Server Error Logs similar to
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:60000 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
The line I'm interested in reviewing is Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. It's good to first understand the differences between Kerberos & NTLM - both supported by SQL Server during AD authentication
1) NTLM Authentication: Challenge- Response mechanism. versus Kerberos authentication: Trust-Third-Party Scheme.
2) For Kerberos to be initiated & used during a SQL Server logon
a) Server & Client must be members of a domain, and the trusted third party exists; if client and server are in different domain, these two domains must be configured as two-way trust .Typically the client & server are registered through the Active Directory
b) Registered SPN must exist. Service Principal Name(SPNs) are unique identifiers for services running on servers. Each service using Kerberos authentication needs to have an SPN set up for it so that clients can identify the service on the network. It's registered in Active Directory under either a computer account or a user account.
3)If the Service Principal Name (SPN) is present - Kerberos is used if connection over TCP/IP . If SPN is not present - NTLM is used.
NTLM is the fallback - which explains the message found in the SQL Server Error Logs
Here are some useful flow diagrams courtesy of Microsoft to illustrate the NTLM challenge-response v Kerberos third party
1) Kerberos
2) NTLM
Some more reading on Active Directory
Use Get-ADPrincipalGroupMembership with other Active Directory ...
SQL Server – Powershell Active Directory search
How to query Active Directory with xp_logininfo
How to find Active Directory users with Get-ADUser search filter
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |