13

Windows Server 2008 R2.

SQL Server 2008 R2 installed.

MSSQL Service runs as Local System.

Server FQDN is SQL01.domain.com.

SQL01 is joined to an Active Directory domain named domain.com.

The following is the output of setspn:

C:\> setspn -L sql01
...
MSSQLSvc/SQL01.domain.com:1433
MSSQLSvc/SQL01.domain.com
WSMAN/SQL01.domain.com
WSMAN/SQL01
TERMSRV/SQL01.domain.com
TERMSRV/SQL01
RestrictedKrbHost/SQL01    
RestrictedKrbHost/SQL01.domain.com
HOST/SQL01.domain.com
HOST/SQL01

I then launch SQL Server Management studio and connect to SQL01 thusly:

enter image description here

I then run the following query:

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

And the result is NTLM. Why is the result not Kerberos? The SPNs seem to be correct for using the Local System account. The server is not in a cluster or using a CNAME.

Ryan Ries
  • 55,011
  • 9
  • 138
  • 197

1 Answers1

17

It's because I was connecting to the SQL Server locally, from the same server that hosted SQL Server. When I connect from another machine on the network, the authentication mechanism used is Kerberos, as expected.

SQL Server will always use NTLM if connecting locally. Kerberos is only used if connecting remotely.

This post from the SQL Server Protocols Blog, while dated, says the same thing:

1) Kerberos is used when making remote connection over TCP/IP if SPN presents.

2) Kerberos is used when making local tcp connection on XP if SPN presents.

3) NTLM is used when making local connection on WIN 2K3.

4) NTLM is used over NP connection.

5) NTLM is used over TCP connection if not found SPN.

Ryan Ries
  • 55,011
  • 9
  • 138
  • 197
  • 3
    wow all this work (research) I have been doing for hours with SPN and delgation, and this was it thanks! :) – dynamiclynk May 28 '15 at 20:06
  • 1
    I also had NTLM (even if SPN is present and the client machine is Win 10), because I had an entry in the hosts file for the server name (although the IP was the same as the real one). – Razvan Socol Apr 07 '20 at 06:52