When connecting to SQL Server 2008 R2 (Windows Server 2008 R2) with correctly configured SPNs, you might unexpectedly encounter NTLM authentication instead of Kerberos. Here's a deep dive into why this happens and how to enforce Kerberos.

SQL Server follows this authentication sequence when SPNs are registered:

1. Client attempts Kerberos authentication
2. If Kerberos fails (for any reason), falls back to NTLM
3. If NTLM fails, connection terminates

First, verify your current authentication method:

-- Check current connection's auth scheme
SELECT auth_scheme FROM sys.dm_exec_connections 
WHERE session_id = @@spid;

-- List all active connections and their auth methods
SELECT session_id, auth_scheme 
FROM sys.dm_exec_connections;

Even with proper SPNs, these factors can trigger NTLM:

• DNS resolution issues - Kerberos requires proper forward/reverse lookup
• Time synchronization problems - Kerberos requires <5 minutes clock skew
• SPN duplication - Multiple accounts owning the same SPN
• Localhost connections - Using localhost/127.0.0.1 forces NTLM

Use this PowerShell script to validate SPN configuration:

# Get SQL Server service account
$service = Get-WmiObject Win32_Service | Where-Object { 
    $_.Name -like "*SQLServer*" -or $_.Name -eq "MSSQLSERVER" 
}

# Verify SPNs for the service account
setspn -L $service.StartName

# Check for duplicate SPNs
setspn -X

To ensure Kerberos is used:

-- 1. Always use FQDN when connecting
Server name: SQL01.domain.com (not just SQL01)

-- 2. Configure client connection string explicitly
"Server=SQL01.domain.com,1433;Integrated Security=SSPI;Authentication=Kerberos"

Check these event logs for Kerberos errors:

1. Windows Security Event Log (Event ID 4768, 4769 for Kerberos tickets)
2. SQL Server Error Log (look for authentication warnings)
3. System Event Log (DNS/time service issues)

Capture a network trace during connection attempt:

netsh trace start scenario=NetConnection capture=yes tracefile=C:\kerberos_trace.etl
# Reproduce the issue
netsh trace stop

Look for Kerberos AS-REQ/AS-REP exchanges (port 88) in the trace.

When connecting to SQL Server 2008 R2 on Windows Server 2008 R2 with proper SPN configuration, we'd expect Kerberos authentication to take precedence. However, the sys.dm_exec_connections query reveals NTLM being used. Let's examine why this occurs and how to enforce Kerberos.

-- Check authentication methods in use
SELECT 
    session_id,
    auth_scheme,
    client_net_address,
    client_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@spid;

-- Verify SPN registration
EXEC xp_cmdshell 'setspn -L sql01';

Even with correct SPNs, several factors can trigger NTLM usage:

• DNS resolution issues (forward/reverse lookup mismatch)
• TCP port specification in connection string
• Localhost connections bypassing network stack
• Missing delegation permissions

-- 1. Verify SPN registration matches exactly
EXEC xp_cmdshell 'setspn -Q MSSQLSvc/sql01.domain.com';

-- 2. Check Kerberos ticket details
EXEC xp_cmdshell 'klist';

-- 3. Test connectivity with FQDN
SELECT net_transport FROM sys.dm_exec_connections 
WHERE session_id = @@spid;

Add these registry entries on the SQL Server:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0]
"BackConnectionHostNames"=hex(7):73,00,71,00,6c,00,30,00,31,00,2e,00,64,00,6f,\
00,6d,00,61,00,69,00,6e,00,2e,00,63,00,6f,00,6d,00,00,00,00,00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\mssqlserver]
"DisableLoopbackCheck"=dword:00000001

Ensure your connection strings use the FQDN:

Server=sql01.domain.com;Database=master;Integrated Security=SSPI;

Avoid these problematic patterns:

-- Problematic:
Server=sql01;Database=master;Integrated Security=SSPI;
Server=localhost;Database=master;Integrated Security=SSPI;
Server=127.0.0.1;Database=master;Integrated Security=SSPI;

Create an extended events session to track authentication patterns:

CREATE EVENT SESSION [Auth_Scheme_Monitor] ON SERVER 
ADD EVENT sqlserver.login(
    WHERE ([result]=(1)))
ADD TARGET package0.event_file(SET filename=N'Auth_Scheme_Monitor')
WITH (MAX_MEMORY=4096 KB,MAX_DISPATCH_LATENCY=30 SECONDS);