SQL Server SPNs and Kerberos: a practical, end-to-end guide

Intro

If Windows logins to SQL Server keep falling back to NTLM, linked servers fail with “Login failed for user NT AUTHORITY\ANONYMOUS LOGON,” or you see SSPI handshake messages in the error log, you probably have a Service Principal Name (SPN) problem. This guide explains what SPNs are, how SQL Server uses them with Kerberos, how to register and verify them, and how to troubleshoot the usual traps. I included examples, scripts, and a hands-on workshop at the end.

What you will learn

• SPN basics and correct formats for default, named, multi-instance, and AG listener setups
• Who should own the SPN and how auto-registration works
• How to prove Kerberos is in use from both client and server
• Tools and scripts to find duplicates, fix ownership, and test end to end

SPNs map a service instance to the AD account that runs it. Kerberos uses that mapping to issue tickets. If the SPN is missing or mapped to the wrong account, connections fall back to NTLM. Microsoft Learn+1

---

Kerberos and SPNs in plain English

• Kerberos is the preferred Windows authentication protocol for mutual auth and delegation. SQL Server uses the Windows SSPI stack, which tries Kerberos first when possible, then falls back to NTLM if it cannot find a valid SPN. SQLServerCentral+1
• An SPN is a unique string that identifies a running service and the AD security principal that owns it. For SQL Server the service class is MSSQLSvc. The SPN ties together service class + host name or FQDN + optional port or instance. Microsoft Learn

SQL Server SPN formats you will actually use

• Default instance, default port (1433):
  MSSQLSvc/<FQDN> and MSSQLSvc/<hostname>
• Default instance, non-default port:
  MSSQLSvc/<FQDN>:<port> and MSSQLSvc/<hostname>:<port>
• Named instance (dynamic or fixed port):
  Use the port form. Example: MSSQLSvc/sql01.contoso.com:51433
• AG listener or cluster name:
  Register SPNs on the listener DNS name, not the node names. Microsoft Learn+1

---

Who should own the SPN

• The SPN must be registered on the service account that runs the SQL Server instance:
  • Domain user account: register on CONTOSO\sqlsvc
  • Managed service account (MSA) or gMSA: register on the MSA account object (ends with $) and ensure it has permission to manage its own SPNs. Microsoft Learn+1

Auto-registration vs manual

• If the SQL service account has Write servicePrincipalName permission on its own object, SQL Server will attempt to register and remove its SPNs at start and stop. If that fails, the error log shows an informational message and connections may fall back to NTLM. You can always register SPNs manually with setspn. Microsoft Learn+1

---

How to register and check SPNs

Common setspn commands

# List SPNs on an account
setspn -L CONTOSO\sqlsvc

# Query which account owns a specific SPN (great for finding “who has it”)
setspn -Q MSSQLSvc/sql01.contoso.com:1433

# Add required SPNs (examples)
setspn -S MSSQLSvc/sql01.contoso.com CONTOSO\sqlsvc
setspn -S MSSQLSvc/sql01.contoso.com:1433 CONTOSO\sqlsvc
# -S checks for duplicates before adding. Use -A on very old systems.

# Find duplicates across the forest
setspn -X

These patterns are documented and widely used in Microsoft and community guidance. Microsoft Learn+1

Where to verify Kerberos is actually used

On the server:

-- For your current session
SELECT auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

-- For all sessions
SELECT session_id, net_transport, encrypt_option, auth_scheme, client_net_address
FROM sys.dm_exec_connections;

auth_scheme = KERBEROS confirms success. If you see NTLM, Kerberos did not engage. SQL Shack

On the client (Windows):

# See Kerberos tickets for the current logon session
klist

# Clear and force a fresh ticket request
klist purge
# Then connect again with SSMS or sqlcmd and run the DMV query above

Special cases you should plan for

• Always On AG listeners and Failover Cluster Instances: Register SPNs on the listener or cluster name the clients use, not on node names. Each SQL Server instance needs its own SPN pair. Microsoft Learn
• Named instances and dynamic ports: Either fix the port and register the :port SPN, or ensure the client resolves the correct dynamic port via the Browser service. In practice, fixed ports simplify SPN management. SQL Shack
• SSRS and SSAS: These services have their own SPN classes and may require additional registrations if they use Windows integrated auth. Kerberos Configuration Manager helps detect gaps. Microsoft Learn
• MSA / gMSA: Confirm the account’s SELF permissions include Write servicePrincipalName so auto-registration works. Microsoft Learn

---

Tools that make this easier

• Kerberos Configuration Manager for SQL Server validates SPNs, shows missing entries, and can generate setspn commands. It is Microsoft-provided and supports SQL Server, SSRS, and SSAS. Microsoft Learn+1
• If KCM cannot connect, check admin rights and SQL Browser. There are known reports for SQL 2022; in those cases validate SPNs manually and use the DMV plus klist. Microsoft Learn+1

---

End-to-end example: default instance on fixed port

Scenario

• SQL Server on sql01.contoso.com, default instance using port 1433
• Service account CONTOSO\sqlsvc

Steps

1. Confirm the service account and DNS name the clients use.
2. Register SPNs:

setspn -S MSSQLSvc/sql01.contoso.com CONTOSO\sqlsvc
setspn -S MSSQLSvc/sql01 CONTOSO\sqlsvc
setspn -S MSSQLSvc/sql01.contoso.com:1433 CONTOSO\sqlsvc
setspn -S MSSQLSvc/sql01:1433 CONTOSO\sqlsvc

3. From a domain client, purge Kerberos tickets, connect with SSMS, and verify:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;

4. If it still shows NTLM, use setspn -Q to locate duplicates and fix ownership. Microsoft Learn

---

Troubleshooting playbook

1. Missing SPNs
   • Symptom: NTLM auth, error log “could not register SPN,” SSPI handshake failures.
   • Fix: Add required SPNs on the service account with setspn -S. MSSQLTips.com
2. SPN on the wrong account
   • Symptom: Kerberos fails even though an SPN exists.
   • Fix: setspn -Q MSSQLSvc/sql01.contoso.com:1433 to see owner. Remove from wrong account with setspn -D, add to the correct one. Microsoft Learn
3. Duplicate SPNs
   • Symptom: Random success or failure, event log 0x2098, Kerberos chooses the “wrong” principal.
   • Fix: setspn -X to list duplicates. Remove extras. Microsoft Learn
4. Using the wrong DNS name
   • Symptom: Works by node name, fails by listener, or vice versa.
   • Fix: Register SPNs on the exact DNS name the client uses. For AGs, that means the listener. Microsoft Learn
5. Named instances with dynamic ports
   • Symptom: Kerberos is flaky because clients connect with different ports.
   • Fix: Assign a fixed port and register port-based SPNs. SQL Shack
6. Client driver behavior
   • Symptom: Kerberos unavailable over certain protocols or older drivers.
   • Fix: Use supported drivers. SPN support is documented for OLE DB and other providers. Microsoft Learn
7. Linked servers and the “double hop”
   • Symptom: Linked server to another SQL instance returns “Login failed for NT AUTHORITY\ANONYMOUS LOGON.”
   • Fix: Enable delegation for the SQL service account and use constrained delegation to the target SPN. Confirm Kerberos on both hops. (General Kerberos behavior; SPN correctness is prerequisite.) Microsoft Learn

---

Security notes

• Only register SPNs you need, on the correct service account.
• Prefer constrained delegation over unconstrained for linked servers and middle tiers.
• Limit who can write servicePrincipalName on service accounts. Microsoft Learn

---

Summary

SPNs are the hinge that lets Kerberos work with SQL Server. Get the SPN strings right, put them on the right account, avoid duplicates, and verify with both sys.dm_exec_connections and klist. Use Kerberos Configuration Manager to audit and script fixes. Do that, and SSPI handshake issues and NTLM fallbacks usually disappear. Microsoft Learn+1

---

Final thoughts

When in doubt, test the whole path like a client would. Purge tickets, connect by the exact DNS name your apps use, and check auth_scheme. For clustered and AG environments, build SPN management into your failover and patch runbooks so it does not break later. Most “mystery” Kerberos bugs come down to four things: wrong name, wrong owner, duplicate SPNs, or dynamic ports.

---

Hands-on workshop

Lab 1. Prove Kerberos with a standalone default instance

Register SPNs:

1. Pick a test server sql01.contoso.com, default instance on 1433, service account CONTOSO\sqlsvc.
2. Register SPNs:

setspn -S MSSQLSvc/sql01.contoso.com CONTOSO\sqlsvc
setspn -S MSSQLSvc/sql01.contoso.com:1433 CONTOSO\sqlsvc

3. On a domain client: klist purge, then connect with SSMS using sql01.contoso.com.

4. Run:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;

5. Expected: KERBEROS. If not, run setspn -Q for the target, check for duplicates with setspn -X, and fix.

Reference for registration details and verification techniques. Microsoft Learn+1

Lab 2. Named instance on a fixed port

1. Instance sql01\sales listening on 51433 under CONTOSO\sqlsvc2.
2. Register:

setspn -S MSSQLSvc/sql01.contoso.com:51433 CONTOSO\sqlsvc2
setspn -S MSSQLSvc/sql01:51433 CONTOSO\sqlsvc2

3. Connect with sql01.contoso.com,51433, verify KERBEROS.

If you must use instance name, ensure clients can resolve port through SQL Browser, but the port-based SPN is still what Kerberos uses. SQL Shack

Lab 3. AG listener

1. AG listener sqlag-lstn.contoso.com, service account CONTOSO\agsql.
2. Register SPNs only on the listener name:

setspn -S MSSQLSvc/sqlag-lstn.contoso.com CONTOSO\agsql
setspn -S MSSQLSvc/sqlag-lstn.contoso.com:1433 CONTOSO\agsql

3. Connect by listener, verify KERBEROS.

4. Fail over and repeat to confirm it survives node changes. Microsoft Learn

Lab 4. Use Kerberos Configuration Manager

1. Run KCM, scan the target server.
2. Review SPN tab for missing or duplicate entries.
3. Export the generated fix script and apply it in a change window. Microsoft Learn+1

---

Troubleshooting scripts and notes

T-SQL: who is using what

-- Current auth scheme per session
SELECT session_id, client_net_address, auth_scheme, encrypt_option, net_transport
FROM sys.dm_exec_connections
ORDER BY session_id;

-- Quick “am I Kerberos” check for me
SELECT auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

PowerShell / CMD: SPN hygiene

# Find duplicates across the forest
setspn -X

# See who owns a specific SPN
setspn -Q MSSQLSvc/sql01.contoso.com:1433

# Remove a wrong SPN (be careful)
setspn -D MSSQLSvc/sql01.contoso.com:1433 CONTOSO\wrongacct

# After fixes, force fresh tickets before testing
klist purge

Error log clue to watch for

“The SQL Server Network Interface library could not register the Service Principal Name (SPN)… Failure to register an SPN may cause integrated authentication to fall back to NTLM.” This is your signal to confirm permissions or register manually. MSSQLTips.com

Driver awareness

If you are mixing OLE DB, ODBC, and different driver versions, confirm SPN support and encryption options in the driver docs. Kerberos support across protocols is documented. Microsoft Learn

---

References and further reading

• Microsoft Docs: Register a Service Principal Name for Kerberos connections. Clear guidance on formats, ownership, and registration. Microsoft Learn
• Microsoft Docs: SPN support in client connections. Driver behaviors and protocol notes. Microsoft Learn
• Microsoft Docs: What is an SPN (Windows). Baseline definition. Microsoft Learn
• SQLShack: Overview of SPN and Kerberos authentication in SQL Server. Good conceptual overview and examples. SQL Shack
• MSSQLTips: Kerberos Configuration Manager for SQL Server and Register a SPN for SQL Server. Tooling and examples. MSSQLTips.com+1
• SQLServerCentral: Configuring Kerberos Authentication and SPN how-to script. Community tips and background. SQLServerCentral+1

---

Quick inline checklist (print me)

• Decide the exact DNS name clients use.
• Identify the service account.
• Register required SPNs on that account: default, port-based, and listener where applicable.
• Check for duplicates and remove them.
• Purge tickets and test with DMV auth_scheme.
• For linked servers, configure constrained delegation after SPNs are correct. Microsoft Learn