SQL Server SPNs and Kerberos: A Practical, End-to-End Guide — 2026 Update

---

If Windows logins to SQL Server keep falling back to NTLM, linked servers fail with “Login failed for user NT AUTHORITY\ANONYMOUS LOGON,” or the error log shows SSPI handshake messages, there is 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, with a hands-on workshop at the end.

1Kerberos and SPNs in Plain English

Kerberos is the preferred Windows authentication protocol for mutual authentication and delegation. SQL Server uses the Windows SSPI stack, which tries Kerberos first when possible.

An SPN is a unique string that identifies a running service and the Active Directory security principal that owns it. For SQL Server, the service class is MSSQLSvc. The SPN ties together the service class, the host name or FQDN, and an optional port or instance name.

2SQL Server SPN Formats

Scenario	SPN Format
Default instance, default port (1433)	MSSQLSvc/<FQDN> and MSSQLSvc/<hostname>
Default instance, non-default port	MSSQLSvc/<FQDN>:<port> and MSSQLSvc/<hostname>:<port>
Named instance, TCP/IP connection	MSSQLSvc/<FQDN>:<port> — the port number is what differentiates instances, since both named and default instances register under the same MSSQLSvc service class
Named instance, non-TCP connection (named pipes, shared memory)	MSSQLSvc/<FQDN>:<instancename>
AG listener or cluster name	Register on the listener DNS name for connections made through the listener; see Section 7 for what this does and does not cover

3Who 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 confirm it has permission to manage its own SPNs.

Auto-registration vs. manual

If the SQL service account has Write servicePrincipalName permission on its own object, SQL Server attempts 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 (with the caveat covered in Section 6). SPNs can always be registered manually with setspn.

Who can make the change

Confirmed directly from Microsoft’s setspn reference documentation: modifying an SPN requires either Domain Admins-level rights, or delegated Validated write to service principal name permission on the specific target account object. This is a permissions requirement, not a location requirement. setspn.exe ships as part of the Active Directory Domain Services server role, so it is present on every domain controller by default; it can also be run from any domain-joined workstation or member server with the AD DS tools available. In practice, many environments simply run it wherever the person holding the required permissions already works, including directly on a domain controller, since there is nothing in Microsoft’s documentation restricting where the command is run.

4How to Register and Check SPNs

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

# Query which account owns a specific SPN (find "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

# Find duplicates across the forest
setspn -X

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

5Where to Verify Kerberos Is Actually Used

On the server:

-- For the 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. NTLM means Kerberos did not engage.

On the client:

# See Kerberos tickets for the current logon session
klist

# Clear and force a fresh ticket request
klist purge
# Then reconnect and re-run the DMV query above

6Missing vs. Wrong-Account SPNs: Two Different Failures

The original version of this guide stated that a missing SPN and a wrong-account SPN both simply cause a fallback to NTLM. That is not accurate, and Microsoft’s own documentation describes two genuinely different failure modes.

Condition	What actually happens	Source
SPN is missing entirely	In most cases, Kerberos authentication fails and the connection falls back to NTLM, but Microsoft’s own documentation explicitly notes this is not universal, with documented exceptions where it does not fall back	Microsoft, “Understanding Kerberos and NTLM authentication in SQL Server Connections”
SPN is registered, but on the wrong account	The server cannot decrypt the Kerberos ticket, since it was encrypted with the wrong account’s secret. This produces a hard authentication failure, logged as Kerberos Event ID 4 with error KRB_AP_ERR_MODIFIED, not a fallback	Microsoft, “Kerberos SPN is on wrong account”
SPN is missing, and NTLM is disabled by policy	No fallback is possible at all. The connection fails outright.	Consistent with how NTLM restriction policies interact with SSPI negotiation; confirm current NTLM policy before assuming a fallback path exists

7Special Cases: AGs, Named Instances, SSRS/SSAS, MSA/gMSA

Always On AG listeners and Failover Cluster Instances

The listener needs its own SPN registered on the listener DNS name for any connection made through the listener. This is necessary but not sufficient by itself.

Named instances and dynamic ports

Either fix the port and register the port-based SPN, or ensure the client resolves the correct dynamic port through the SQL Server Browser service. Fixed ports simplify SPN management in practice.

SSRS and SSAS

These services have their own SPN classes and may require additional registrations for Windows integrated authentication. SQL Server Analysis Services in particular cannot use a port-based SPN at all; its registration is instance-name only, and a default AS instance on a non-default port cannot use Kerberos, confirmed directly in Microsoft’s Analysis Services SPN documentation.

MSA / gMSA

Confirm the account’s SELF permissions include Write servicePrincipalName so auto-registration works as expected.

8Tools: SQLCheck, KCM, and Which One to Use

Confirmed directly from Microsoft’s current troubleshooting documentation: KCM is offered without technical support or further updates, and it has not been revised to work with the WMI provider introduced in SQL Server 2022 and later. Microsoft’s own current guidance for diagnosing SQL Server Kerberos and SPN issues now points to SQLCheck instead.

The precise version line, traced through Microsoft’s own WMI provider reference table: SQL Server 2019 and every version back through 2005 all share the same underlying WMI provider file (sqlmgmproviderxpsp2up.mof). SQL Server 2022 introduced a new provider file (sqlmgmprovider.mof) that KCM was never updated to support.

SQL Server Version	WMI Provider	KCM Status
2019 and earlier	sqlmgmproviderxpsp2up.mof	KCM remains functional
2022 and later	sqlmgmprovider.mof	KCM was never updated for this provider; use SQLCheck instead

Current recommended toolchain

1. SQLCheck — the tool Microsoft’s own current documentation points to for diagnosing Kerberos and SPN configuration issues; run on both the SQL Server and the client machine.
2. setspn — the authoritative command-line tool for registering, querying, and removing SPNs, on every supported version.
3. klist — confirms what ticket the client actually holds.
4. The SQL Server DMV query (Section 5) — confirms what the server actually negotiated.
5. Active Directory verification — confirms SPN ownership directly at the source.
6. The SQL Server error log — the first signal that self-registration failed.

KCM remains a legitimate supplemental tool specifically for SQL Server 2019-era and earlier environments still running that WMI provider generation. It is not the primary recommendation for SQL Server 2022 or later.

9End-to-End Example: Default Instance on a Fixed Port

Scenario: SQL Server on sql01.contoso.com, default instance on port 1433, service account CONTOSO\sqlsvc.

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

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

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

If it still shows NTLM, use setspn -Q to locate duplicates or ownership issues, then check the client’s System event log for Kerberos Event ID 4 if the connection is failing outright rather than downgrading.

10Troubleshooting Playbook

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. If NTLM is disabled by policy, expect an outright failure instead of a downgrade.

SPN on the wrong account

Symptom: Kerberos authentication fails even though an SPN exists; Kerberos Event ID 4 in the client’s System event log.
Fix: setspn -Q MSSQLSvc/sql01.contoso.com:1433 to see the current owner. Remove from the wrong account with setspn -D, add to the correct one.

Changing the service account: the correct sequence

A common trigger for the “SPN on the wrong account” scenario above: the SQL Server service account was changed (for example, from Local System to a domain account), and the old SPN was never cleanly removed. Confirmed directly from Microsoft’s “Explicit misplaced SPN error in SQL Server” troubleshooting article, the documented sequence is:

# 1. Confirm current SPNs on the account in question
setspn -L domain\svcacct

# 2. Confirm exactly which account currently holds the SPN
setspn -Q MSSQLSvc/servername:port

# 3. Remove it from the old account
setspn -D MSSQLSvc/servername:port 

# 4. Add it to the correct account
setspn -S MSSQLSvc/servername:port 

Step 3 is not optional. Microsoft’s SPN registration documentation states directly: “If an SPN already exists, it must be deleted before it can be reregistered.” Attempting to add without removing first produces Duplicate SPN found, aborting operation!, Microsoft’s literal error text for this condition. The symptom that shows up for end users if the old SPN is left in place is exactly what it was in the original scenario this section addresses: “The target principal name is incorrect. Cannot generate SSPI context.”

If the actual Windows service logon account changed (not just correcting a misplaced SPN on an unchanged account), the SQL Server service also needs a restart for that account change to take effect. This is standard Windows Service Control Manager behavior, not specific to SPNs.

Verify afterward the same way as anywhere else in this guide: purge tickets on the client with klist purge, reconnect, and confirm auth_scheme = KERBEROS with the DMV query from Section 5.

Duplicate SPNs

Symptom: Inconsistent success or failure depending on which account the KDC resolves to.
Fix: setspn -X to list duplicates across the forest. A duplicate breaks the required one-to-one mapping between an SPN and its AD account; the KDC cannot deterministically resolve which account’s key to use, and authentication fails. Remove the extra registration.

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 includes the listener for listener connections and each node for direct connections, per Section 7.

Named instances with dynamic ports

Symptom: Kerberos is inconsistent because clients connect through different ports.
Fix: Assign a fixed port and register the port-based SPN for TCP connections.

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 is engaged on both hops before assuming delegation is the issue.

11Security Notes

• Register only the SPNs actually needed, on the correct service account.
• Prefer constrained delegation over unconstrained for linked servers and middle tiers.
• Limit who can write servicePrincipalName on service accounts.

12Hands-On Workshop

Lab 1: Prove Kerberos with a standalone default instance

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 and 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 the DMV query from Section 5. Expected: KERBEROS.
5. If not, run setspn -Q for the target and check for duplicates with setspn -X.

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 and setspn -S MSSQLSvc/sql01:51433 CONTOSO\sqlsvc2.
3. Connect with sql01.contoso.com,51433, verify KERBEROS.

Lab 3: AG listener, and the direct-node path

1. AG listener sqlag-lstn.contoso.com, service account CONTOSO\agsql.
2. Register SPNs on the listener name: setspn -S MSSQLSvc/sqlag-lstn.contoso.com CONTOSO\agsql and setspn -S MSSQLSvc/sqlag-lstn.contoso.com:1433 CONTOSO\agsql.
3. Connect by listener, verify KERBEROS.
4. Separately, connect directly to one of the underlying node names. If that path also needs to authenticate with Kerberos, confirm the node itself has its own instance-level SPN; the listener SPN registered in step 2 does not cover this path.
5. Fail over and repeat step 3 to confirm listener behavior survives node changes.

Lab 4: Use SQLCheck (SQL Server 2022 and later)

1. Run SQLCheck on both the SQL Server and the client machine, per Microsoft’s current guidance.
2. Review the output for missing or duplicate SPN entries.
3. Apply any corrections in a change window.

13Troubleshooting 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
SELECT auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

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
setspn -D MSSQLSvc/sql01.contoso.com:1433 CONTOSO\wrongacct

# After fixes, force fresh tickets before testing
klist purge

Error log clue to watch for

SQL Server’s own error log records this as an informational message when self-registration fails: the SPN could not be registered, and the log entry itself notes this may cause integrated authentication to fall back to NTLM. Treat this as the signal to confirm permissions or register manually, keeping in mind the fallback assumption in that message does not hold if NTLM itself is disabled.

Kerberos Event ID 4 in the client’s System log

The specific signal for an SPN registered to the wrong account. The error KRB_AP_ERR_MODIFIED means the server could not decrypt the ticket presented to it, because it was encrypted with a different account’s secret than the one the server actually has.

14Quick Inline Checklist

1. Decide the exact DNS name clients use for each connection path.
2. Identify the service account for each instance and listener.
3. Register required SPNs on that account: default, port-based, instance-name (for non-TCP), and listener where applicable.
4. Remember the listener SPN does not cover direct node connections; register those separately if used.
5. Check for duplicates with setspn -X and remove them.
6. Purge tickets and test with the DMV auth_scheme query.
7. If a connection fails outright rather than downgrading to NTLM, check the client’s System log for Kerberos Event ID 4 before assuming the SPN is simply missing.
8. For linked servers, configure constrained delegation after SPNs are confirmed correct.
9. On SQL Server 2022 and later, use SQLCheck as the primary diagnostic tool; reserve KCM for 2019-era and earlier environments.

The technical information in this article was verified against Microsoft documentation at the time of publication. SQL Server features, cloud service capabilities, licensing terms, and configuration requirements can change between versions and cumulative updates. Always validate implementation details against current Microsoft Learn documentation before deploying to production. References in this article link directly to the authoritative Microsoft sources.

References

• Microsoft Docs: Using Kerberos Configuration Manager for SQL Server (confirms KCM’s SQL Server 2022+ WMI provider limitation and points to SQLCheck)
• Microsoft Docs: Register a Service Principal Name for Kerberos Connections (confirms both TCP and non-TCP SPN formats for named instances)
• Microsoft Docs: Kerberos SPN Is on Wrong Account (confirms the KRB_AP_ERR_MODIFIED / Event ID 4 failure mode)
• Microsoft Docs: Understanding Kerberos and NTLM Authentication in SQL Server Connections
• Microsoft Docs: Kerberos Authentication Problems: SPN Issues, Part 2 (duplicate SPN scenario)
• Microsoft Docs: SPN Registration for an Analysis Services Instance
• Microsoft Docs: Fix “Cannot Generate SSPI Context” Error in SQL Server
• Microsoft Docs: Setspn (confirms required permissions and the -S over -A recommendation)
• Microsoft Docs: Explicit Misplaced SPN Error in SQL Server (documented remove-then-add sequence for moving an SPN to a new account)