Windows Server 2022 — SQL Server login failures after duplicate SPN registration — Fix & Prevention

📖 ~1 min read

Table of contents

1. Symptom & Impact
2. Environment & Reproduction
3. Root Cause Analysis
4. Quick Triage
5. Step-by-Step Diagnosis
6. Solution — Primary Fix
7. Solution — Alternative Approaches
8. Verification & Acceptance Criteria
9. Rollback Plan
10. Prevention & Hardening
11. Related Errors & Cross-Refs
12. References & Further Reading

Symptom & Impact

Integrated authentication to SQL fails intermittently with SSPI/Kerberos errors after duplicate SPN entries are introduced.

Environment & Reproduction

Usually after service account changes, instance migration, or manual SPN registration errors.

setspn -Q MSSQLSvc/*
Get-WinEvent -LogName System -MaxEvents 50 | Where-Object {$_.Message -match 'Kerberos|KDC'}
Invoke-Sqlcmd -ServerInstance '' -Query "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id=@@SPID"

Root Cause Analysis

KDC cannot determine correct service principal due duplicate SPN mappings, causing ticket issuance/auth mismatch for SQL service identity.

Quick Triage

Confirm duplicate SPNs and current SQL service account mapping.

setspn -X
sc.exe qc MSSQLSERVER
klist purge

Step-by-Step Diagnosis

Map each MSSQLSvc SPN to owning account and compare with running SQL service account.

setspn -L DOMAINsvc_sql
setspn -Q MSSQLSvc/:1433
Get-Service MSSQLSERVER

Solution — Primary Fix

Remove duplicate SPNs and register correct SPN set on active SQL service account, then refresh tickets.

setspn -D MSSQLSvc/:1433 DOMAINold_account
setspn -S MSSQLSvc/:1433 DOMAINsvc_sql
setspn -S MSSQLSvc/ DOMAINsvc_sql
klist purge

Solution — Alternative Approaches

If immediate AD changes are blocked, temporarily force SQL auth for critical app paths while Kerberos mapping is corrected.

Verification & Acceptance Criteria

Kerberos auth scheme is restored for integrated sessions and SSPI failures stop in logs.

Invoke-Sqlcmd -ServerInstance '' -Query "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id=@@SPID"
setspn -Q MSSQLSvc/*
Get-WinEvent -LogName System -MaxEvents 30 | Where-Object {$_.Message -match 'KDC'}

Rollback Plan

If authentication regressions continue, reapply previous SPN map and switch service account back to last known-good identity.

setspn -D MSSQLSvc/:1433 DOMAINsvc_sql
setspn -S MSSQLSvc/:1433 DOMAINold_account

Prevention & Hardening

Use controlled SPN automation and post-change duplicate scans for SQL service identity updates.

setspn -X
Get-ScheduledTask | Where-Object {$_.TaskName -match 'SPN|Kerberos'}

Related Errors & Cross-Refs

Related to Cannot generate SSPI context, KDC_ERR_S_PRINCIPAL_UNKNOWN, and NTLM fallback increasing auth latency.

View all Windows Server 2022 tutorials on the Tutorials Hub →

Browse all common problems & solutions on the Tutorials Hub.

References & Further Reading

Microsoft Kerberos/SPN troubleshooting guidance and SQL Server integrated authentication best practices.