Sql-server – NTLM vs Kerberos authentication – questions

active-directorykerberossql serverwindows-authentication

We have 4 servers, all are members of the same Domain:

1. SQL 2012 on Windows Server 2016
2. SQL 2012 on Windows Server 2012
3. SQL 2017 on Windows Server 2016
4. SQL 2017 on Windows Server 2016

I noticed that on first two servers, domain users are connecting using NTLM only (sys.dm_exec_connections DMV, auth_scheme column)

Researching how to change auth_scheme to Kerberos, learned that "Write ServicePrincipalName" privilege must be granted to SQL Server Service Account in the Active Directory Service Interfaces (ADSI Edit) 

Right now privilege "Write ServicePrincipalName" is NOT granted to the service account

Before granting that privilege to SQL Service Account, I decided to check auth_scheme on the 3rd and 4th servers, and found out that domain users are actually connecting using Kerberos auth_scheme to those servers

Question is – how can this be that while "Write ServicePrincipalName" privilege is turned off for the SQL service account, domain users are connecting using Kerberos auth to servers 3 and 4, and as NTLM to servers 1 and 2 at the same time ?

Is there some setting somewhere, that makes the difference between servers 1,2 and 3,4 and how users connect to them ?

Best Answer

Kerberos Authentication requires that you have Service Principal Names registered for the services being run by your service account to perform the exchange required for Kerberos authentication to work.

You can easily validate your SPNs using Microsoft's Kerberos Configuration Manager. Once you've validated and fixed any SPN discrepancies, confirm if your users are connecting in a double-hop scenario. If they're not, then NTLM may be the correct mechanism. Check this blog article to determine if your users should be using NTLM or Kerberos.

Related Question