List the differences between Kerberos and NTLM

04 January,2021 by Jack Vamvas

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

Kerberos

 

2) NTLM 

 

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on List the differences between Kerberos and NTLM


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer