Logins, SIDs, and Kerberos from First Principles, Part 6: SPNs and the Silent NTLM Fallback

The first five parts of this series were about who you are: SIDs, logins, groups. This one is about how you prove it. When a Windows principal connects to SQL Server with integrated authentication, one of two protocols does the proving: Kerberos or NTLM.^[3] You almost never choose between them explicitly. The client and server negotiate, and when Kerberos is not possible, the connection silently falls back to NTLM: no error, no warning to the application, just a different (older, weaker, less capable) protocol.

Whole estates run on NTLM without anyone deciding they should. The evidence is sitting in a DMV and in the errorlog the whole time. This post demonstrates the fallback live on a real instance, shows exactly why it happens, and covers the fix. Demos ran against SQL Server 2019 (CU32); the behavior is identical on every supported version.

Checking What You Are Actually Using

One DMV column answers the question for every current connection:^[1]

This connection came in over TCP using the server’s fully qualified name, the exact conditions under which Kerberos should be used, and it negotiated NTLM anyway. Run the query without the WHERE clause on a production instance and count the auth_scheme values; the result surprises most people who try it.

(One aside before the diagnosis: connections over Shared memory, i.e. local connections, always show NTLM. That is normal and unfixable; Kerberos is a network authentication protocol. Judge an instance only by its TCP connections.)

Why Kerberos Did Not Happen

Kerberos requires the client to obtain a ticket for the specific service it is connecting to, and it identifies that service by a Service Principal Name: for SQL Server, MSSQLSvc/<fqdn>:<port> or MSSQLSvc/<fqdn>:<instancename>. The SPN must be registered in the directory on the account the SQL Server service runs as. No registered SPN, no ticket; no ticket, NTLM.^[2]

Whether the SPN exists is checkable from the command line. setspn -L against the service account (here, the machine account, since the instance runs as a virtual account):

Plenty of SPNs, and not one MSSQLSvc entry. And SQL Server told us this would happen, at startup, in the errorlog:

Read that carefully: a security-relevant protocol downgrade is announced as “an informational message” requiring “no user action.” SQL Server attempts to self-register its SPNs at startup, and self-registration only succeeds when the service account has the Write servicePrincipalName permission on its own directory object. Virtual accounts and minimally privileged domain service accounts typically do not, which is the correct security posture, and also why the warning above appears on so many instances. The two failed registrations show the two SPN forms SQL Server wanted: FQDN + instance name, and FQDN + port.

The client side of the failure is visible too. Ask Windows for a ticket against the unregistered SPN:

The client cannot get a ticket for a service the directory has never heard of, so the connection negotiates down to NTLM, successfully, quietly.

The Fix: Register the SPNs

A domain administrator (or anyone delegated Validated write to service principal name) registers the SPNs on the service account. All four canonical forms, covering every way clients might address the instance:

Details that bite:

• Use -S, not -A. -S checks for duplicates first; a duplicate SPN (same SPN on two accounts) breaks Kerberos for everyone, which is worse than no SPN.
• Register on the service account, not the machine, unless the service runs as a virtual or machine account. If the service account ever changes, the SPNs must move with it; orphaned SPNs on the old account become duplicates the moment the new account registers.
• Named instances on dynamic ports re-randomize the port SPN target on restart. Fix the port (as this demo instance does at 50305) or expect intermittent Kerberos failures.
• Verify from the client afterwards: klist purge, reconnect over TCP with the FQDN, and confirm auth_scheme = KERBEROS in the DMV. A cached NTLM session will happily persist through your fix.

For estate-wide auditing, the errorlog warning shown above is itself a detection signature: sweep every instance’s errorlog for “could not register the Service Principal Name” and you have a list of servers running on NTLM fallback, before a single connection is inspected.

Does NTLM Fallback Actually Matter?

For a single default-configured instance, things work, which is exactly why the problem persists. It starts mattering when:

• Anything requires delegation. Double-hop scenarios (a middle tier connecting to SQL Server on a user’s behalf, linked servers with pass-through authentication) require Kerberos; NTLM cannot delegate. The resulting Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error is the classic symptom, appearing far from the missing SPN that caused it.
• Security policy tightens. NTLM is subject to relay and hash-based attacks that Kerberos resists, and organizations increasingly audit or block NTLM domain-wide. An estate silently dependent on NTLM fallback discovers that dependency during the enforcement rollout.
• Encryption expectations rise. Kerberos tickets support stronger cipher suites, though what “stronger” means has its own history: part 7 digs into the RC4 problem hiding inside many Kerberos deployments.

What to Take Away

• Check auth_scheme today. One query against sys.dm_exec_connections tells you what your estate actually negotiates.
• The errorlog announces the problem at every startup, politely, as an informational message. Treat “could not register the Service Principal Name” as a finding.
• Register all four SPN forms with setspn -S on the account the service runs as, and fix dynamic ports first.
• NTLM fallback is a dependency you have not tested. You find out where it matters when delegation breaks or NTLM gets blocked.

Next up: part 7 examines the encryption inside those Kerberos tickets, where a 1987 stream cipher may still be doing the work.

Found a fleet full of NTLM? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.

References

1. sys.dm_exec_connections (Transact-SQL) – Microsoft Learn. The auth_scheme column distinguishing Kerberos from NTLM per connection. ↩
2. Register a Service Principal Name for Kerberos connections – Microsoft Learn. SPN formats, self-registration permissions, and manual registration. ↩
3. Kerberos authentication overview – Microsoft Learn. Tickets, service principals, and how negotiation selects Kerberos versus NTLM. ↩