Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
how about just answering the question so the community can all benefit. –  mdpc May 17 at 10:30

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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