Kerberos and SPN: Windows Authentication Deep Dive

How Kerberos Works: The 30-Second Version

Windows Authentication uses Kerberos (a network authentication protocol) to secure communication. Here's the flow:

1. User logs into workstation (caches credentials in Kerberos ticket-granting ticket)
2. User tries to connect to SQL Server
3. Client asks Kerberos DC: "Can I talk to SQLSERVER/hostname?"
4. DC checks: Does this SPN exist for the service account?
5. If SPN found: DC issues service ticket
6. Client sends service ticket to SQL Server
7. SQL Server validates ticket, grants access
8. Connection established (user never types password)

If the SPN is missing, Kerberos fails at step 4. The DC has no way to authenticate the server, so Kerberos falls back to NTLM (older, weaker authentication). If NTLM also fails, connection denied.

What Is a Service Principal Name (SPN)?

An SPN is an Active Directory object that maps a service to the account running that service. It's like a directory entry: "The service SQLSERVER running on hostname SERVER1 is owned by account DOMAIN\sqlagent."

SPN Format

Service/Hostname:Port
Service/Hostname

Examples:
MSSQLSvc/sqlserver1.contoso.com:1433
MSSQLSvc/sqlserver1.contoso.com
MSSQLSvc/sqlserver1:1433
MSSQLSvc/sqlserver1

Why Multiple SPNs?

-- SQL Server running on SQLSERVER1 with mixed hostnames:

MSSQLSvc/sqlserver1:1433          (NetBIOS, port 1433)
MSSQLSvc/sqlserver1.contoso.com:1433  (FQDN, port 1433)
MSSQLSvc/sqlserver1               (NetBIOS, default port)
MSSQLSvc/sqlserver1.contoso.com   (FQDN, default port)

-- Clients may connect via any of these names:
sqlcmd -S sqlserver1 -E
sqlcmd -S sqlserver1.contoso.com -E
sqlcmd -S sqlserver1:1433 -E
sqlcmd -S sqlserver1.contoso.com:1433 -E

-- Each requires a matching SPN for Kerberos auth

Common SPN Issues

Issue 1: SPN Not Registered

-- User tries to connect:
sqlcmd -S sqlserver1 -E

-- Error in SQL Server error log:
"SSPI context error"

-- Reality:
SPN not registered in Active Directory for DOMAIN\sqlagent account
→ Kerberos fails → Falls back to NTLM
→ NTLM also fails (networking issue, etc.)
→ Connection denied

Issue 2: SPN Registered Under Wrong Account

-- SPN registered for sqlagent account
-- But SQL Server running as svcaccount account
-- Result: Kerberos doesn't recognize the service
-- Fallback to NTLM (same failure chain)

Issue 3: Duplicate SPNs

-- Same SPN registered under two different accounts
-- Result: Ambiguous → Kerberos fails

Registering SPNs: The Right Way

Prerequisites

Domain admin access (to register SPNs)
SQL Server running under domain service account (not LOCAL SYSTEM or Network Service)
SQL Server instance name and port known
Service account name known

Manual SPN Registration

-- Run as domain admin on any domain-joined machine:

-- For SQL Server default instance (port 1433)
setspn -A MSSQLSvc/sqlserver1 DOMAIN\sqlagent
setspn -A MSSQLSvc/sqlserver1.contoso.com DOMAIN\sqlagent
setspn -A MSSQLSvc/sqlserver1:1433 DOMAIN\sqlagent
setspn -A MSSQLSvc/sqlserver1.contoso.com:1433 DOMAIN\sqlagent

-- For SQL Server named instance (port 1433)
setspn -A MSSQLSvc/sqlserver1\INSTANCE1:1433 DOMAIN\sqlagent
setspn -A MSSQLSvc/sqlserver1.contoso.com\INSTANCE1:1433 DOMAIN\sqlagent

-- Verify SPNs registered:
setspn -L DOMAIN\sqlagent

🔧 Automated SPN Report Tool: Instead of manual verification, use our sqmSQLTool's Get-sqmSPNReport command. This tool scans your SQL Server instances, compares registered SPNs against actual running services, and identifies missing, duplicate, or mismatched SPNs automatically.

The report shows: registered vs. expected SPNs, which service account owns each SPN, potential Kerberos issues, and recommended fixes.

Kerberos vs. NTLM

Aspect	Kerberos	NTLM
Strength	Strong (mutual authentication)	Weak (vulnerable to relay attacks)
Requirement	SPNs registered in AD	Works without SPN (fallback)
Performance	Faster (delegated, cached tickets)	Slower (challenge-response per connection)
Delegation	Supports Kerberos delegation (multi-hop)	No true delegation
Audit Trail	Better (SPN-based)	Harder to trace

Delegation: The Three-Hop Problem

Scenario: Reporting Server

User (Workstation)
  → Reporting Server (SSRS)
    → SQL Server Database
    → File Server (for export)

This is "three hops": User to SSRS, SSRS to SQL, SSRS to File.
NTLM stops after the first hop (credentials consumed).
Kerberos with delegation allows all three hops.

Enabling Delegation

-- In Active Directory Users and Computers:
1. Right-click SSRS service account
2. Properties → Delegation tab
3. Choose: "Trust this user for delegation to any service (Kerberos only)"
   OR "Trust this user for delegation to specified services only"
      (more secure: specify only SQL Server SPN)

⚠ Delegation is a security risk: Only enable for service accounts that truly need it. Unconstrained delegation allows the account to impersonate users on ANY service. Constrained delegation is safer (restricts to specific SPNs).

Troubleshooting Kerberos Failures

Step 1: Verify SPNs Exist

-- Check what SPNs are registered for the SQL service account:
setspn -L DOMAIN\sqlagent

-- Should list multiple SPNs (MSSQLSvc/hostname, etc.)
-- If empty: SPNs not registered (main cause of Kerberos failures)

Step 2: Check SQL Server Error Log

-- Look for "SSPI context error" or "Kerberos" messages
-- These indicate Kerberos failure, fallback to NTLM

-- Error log location: SSMS → Management → SQL Server Logs
-- Or check: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG

Step 3: Test with Kerberos Debugging

-- Enable Kerberos logging on client:
reg add HKLM\System\CurrentControlSet\Control\Lsa /v LogLevel /t REG_DWORD /d 1

-- Attempt connection:
sqlcmd -S sqlserver1 -E

-- Check Event Viewer: Windows Logs → System
-- Look for Kerberos events (event ID 6, 27, 36, etc.)

-- Disable logging when done:
reg delete HKLM\System\CurrentControlSet\Control\Lsa /v LogLevel

Step 4: Use setspn to Check for Conflicts

-- Check entire domain for duplicate SPNs:
setspn -F -Q MSSQLSvc/*

-- Output shows all MSSQLSvc SPNs in domain
-- If same SPN appears twice, that's a problem

Best Practices

☐ Always run SQL Server under a domain service account (not LOCAL SYSTEM)
☐ Register SPNs immediately after configuring the service account
☐ Register SPNs for both NetBIOS and FQDN hostnames
☐ Register SPNs for both default and custom ports
☐ Audit SPN registration periodically (especially after account changes)
☐ Use automated tools (Get-sqmSPNReport) to validate SPN configuration
☐ Document which account owns which SPN (avoid confusion with multiple instances)
☐ Enable Kerberos delegation only when necessary, and use constrained delegation
☐ If Kerberos fails, investigate before accepting NTLM (it's a symptom, not a solution)
☐ Replicate SPN configuration across all replicas in AlwaysOn AGs

The Bottom Line

Kerberos and SPNs are not optional—they're the foundation of secure Windows Authentication. A missing SPN doesn't mean "use NTLM instead"; it means "authentication will fail or fall back to weaker security."

Register SPNs correctly from the start. Audit them periodically (especially after account changes, hostname changes, or port changes). Use tools like Get-sqmSPNReport to automate the verification. Done right, your users connect seamlessly and securely. Done wrong, you get mysterious "SSPI context error" messages at 2 AM.