Error 18452 and SSPI Handshake Failures: Kerberos, SPNs and Trust

Fix it now

Windows authentication was attempted and the security layer could not complete it. Microsoft publishes 18452 as the login being from an untrusted domain and unusable with integrated authentication; the server-side SSPI handshake failure is the same event seen from the other end. In most cases a service principal name is missing, duplicated, or on the wrong account.

setspn -L DOMAIN\svc_sql
setspn -Q MSSQLSvc/sqlhost.contoso.com:1433
setspn -X

1. Find out how connections that do work are authenticating: SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID; returns KERBEROS, NTLM or SQL.
2. Read the setspn output. You want exactly one MSSQLSvc/host.domain.com:1433 for a default instance on 1433, and MSSQLSvc/host.domain.com:<port> for a named instance, because the port is what ties an SPN to an instance when TCP is used.
3. If the SPN is missing, register it against the service account: setspn -S MSSQLSvc/sqlhost.contoso.com:1433 DOMAIN\svc_sql. -S checks for a duplicate first.
4. If setspn -X shows a duplicate, delete the wrong one: setspn -D MSSQLSvc/sqlhost.contoso.com:1433 DOMAIN\old_account. A duplicate breaks Kerberos for everyone who uses that name.
5. Reconnect and check auth_scheme again. Microsoft also publishes Kerberos Configuration Manager, which finds missing and duplicate SPNs and dynamic-port problems for you.

Do not test from the server console. A local connection usually goes over shared memory and needs no ticket, which is why SPN faults are invisible from the machine that has them.

If auth_scheme now reports KERBEROS from a client machine, you are done. If it does not, the next section covers what happens before the login and which of the four causes you have.

Why it happens

When a client connects with integrated security, the two ends negotiate through the Security Support Provider Interface and Kerberos is tried first. The client asks a domain controller for a ticket to a specific service, identified by its service principal name and built from the server name and the port or instance it is connecting to. If exactly one account holds that SPN, the ticket it issues can be decrypted by the server and authentication succeeds.

Everything that goes wrong here goes wrong in that sentence. No account holding the SPN means no ticket, and the pair fall back to NTLM. Two accounts holding the same SPN means the domain controller cannot tell which key to encrypt with. The wrong account holding it, because the service changed identity and the old registration was never removed, has the same result.

The SPN format is where most hand-written registrations go wrong. Microsoft documents MSSQLSvc/<FQDN>:<port> as the provider-generated SPN when TCP is used, for default and named instances alike, and MSSQLSvc/<FQDN>:<instancename> as the form used for a named instance when a protocol other than TCP is used. A named instance that clients reach over TCP therefore needs the port form. Registering only the instance-name form and expecting TCP clients to find it is a common and quiet failure.

That is also why a named instance on a dynamic port cannot work reliably with Kerberos. The port changes at every restart, the SPN does not, and the SPN the client asks for stops matching. Microsoft’s guidance is to give the instance a static TCP port under IPAll before troubleshooting anything else about it.

Full reference

Reading the symptom

SPN forms, as Microsoft publishes them

Microsoft also notes that the SPN format does not require a port number at all, and that a multiple-port server or a protocol without ports can still use Kerberos. What it does require is that the SPN the client constructs and the SPN registered in the directory are the same string, held by exactly one account. That is the whole test.

Command reference

Microsoft publishes two tools for this that are worth more than the command list: Kerberos Configuration Manager, which identifies missing and duplicate SPNs, dynamic-port problems and TCP configuration and can fix several of them, and SQLCHECK, which prints a suggested-SPN table showing which ones exist and which do not. Both save time over reading setspn output by eye.

The nearby error that is not this one

18483 is a different failure that turns up in the same investigations. Microsoft publishes it as being unable to connect to a server because the login is not defined as a remote login there, with advice to verify the login name. That is a linked-server or remote-server mapping question, not a Kerberos one, so if 18483 is the number you actually have, the SPN work in this article will not help.

What is not published

The server-side entry for an SSPI handshake failure carries error 17806 along with an error code and a state. Microsoft does not publish a page defining 17806 itself, so this article does not assign it a meaning beyond the class of failure it belongs to. What is published, and what you should work from, is the SSPI troubleshooting guidance and the SSPI status codes: the numeric code carried in that entry, such as 0x80090346, is where the real detail is.

Before you conclude Kerberos is unfixable here

• Check name resolution. Microsoft lists DNS problems as a cause of an invalid SPN being formed in the first place, so a client resolving the server to the wrong name asks for the wrong ticket.
• Check the clocks. Kerberos refuses beyond a few minutes of skew regardless of everything else.
• Check the trust. Test-ComputerSecureChannel on the client tells you whether its own channel to the domain is healthy.
• Give named instances a static port under IPAll before anything else, because without one the SPN is chasing a moving target.
• Treat a fallback to NTLM as a finding rather than a resolution. It hides the fault and takes delegation away with it.

Every code this article covers

Confirm the fix worked

1. From a client machine, SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID; reports KERBEROS.
2. setspn -X shows no duplicates for the SQL Server names.
3. setspn -L on the service account lists exactly the SPNs you intended, in the right form for the protocol clients use.
4. Restart the SQL Server service and check the error log for a clean SPN registration entry at startup.
5. A double-hop scenario, if you have one, reaches SQL Server as the end user rather than as an anonymous identity.

Questions people ask about this