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

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.

  • how about just answering the question so the community can all benefit. – mdpc May 17 '14 at 10:30

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.

  • 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
  • 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 7 '20 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.