Home / server / Questions / 596659
Accepted
Ryan Ries
Asked: 2014-05-17 18:50:12 +0800 CST

Why is MS SQL Server Using NTLM Authentication?

  • 772

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.

sql-server

1 Answers

  1. Best Answer

    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.

    • 17