Kerberos vs NTLM in SQL Server: Best Practices

Every Windows-authenticated connection to SQL Server uses one of two schemes, and most teams
could not say which without looking. That is usually fine, right up until a linked server
double-hop fails, a security baseline starts restricting NTLM, or an SSPI error appears and
nobody knows what “normal” looked like. This post is the five-minute version of knowing.

---

How to Check What You Are Actually Using

One query, safe anywhere:

SELECT auth_scheme,
       net_transport,
       COUNT(*) AS connections
FROM sys.dm_exec_connections
GROUP BY auth_scheme, net_transport;

Real output from this site’s lab instance:

auth_scheme  net_transport  connections
-----------  -------------  -----------
NTLM         Shared memory  1

SQL rows are SQL-authenticated logins and sit outside this conversation. For Windows logins
you will see KERBEROS or NTLM, and the split is the finding. That lab result is itself the
first lesson: a standalone machine, a local connection, or a missing SPN all produce NTLM
without any error. NTLM working quietly is what Kerberos silently not working looks like.

---

When NTLM Fallback Happens

Negotiation prefers Kerberos and falls back to NTLM whenever Kerberos cannot be used:

• No SPN is registered for the SQL Server service account, or the client connected by
  IP address, so there was no name to look up an SPN with.
• The client or server is not domain-joined, shared memory local connections included.
• The ticket path is broken: time skew, an unreachable KDC, or a stale machine channel,
  the territory covered in the SSPI handshake post.

---

When the Difference Actually Matters

• Double-hop delegation. The famous one: a client connects to server A, and A queries
  linked server B as that user. NTLM cannot forward an identity across that second hop, so B
  sees NT AUTHORITY\ANONYMOUS LOGON and fails. Kerberos with delegation is the supported
  path through.
• Policy. Security baselines increasingly restrict or audit NTLM domain-wide. A SQL estate
  quietly running on NTLM fallback becomes a wall of authentication failures the day such a
  policy switches from audit to enforce, which is a bad day to learn about SPNs.
• Auditing quality. Kerberos failures name their causes; broad NTLM usage flattens the
  audit trail.
• Ordinary single-hop performance and function: barely. If nothing above applies, NTLM
  fallback is not an incident. Fix it deliberately, not in a panic.

---

SPN Registration, the Part Everyone Defers

Kerberos finds SQL Server by its Service Principal Name. Documented mechanics, standalone-lab
honesty note applies:

setspn -L DOMAIN\sqlserviceaccount

MSSQLSvc/server.domain.com
MSSQLSvc/server.domain.com:1433

setspn -X

SQL Server tries to register its own SPNs at startup and quietly logs success or failure in
the error log (search it for “SPN”). It only succeeds when the service account has the
Validated write to service principal name permission, which is exactly what a gMSA gives you
for free, and one of the better arguments for using one.

A missing SPN degrades to NTLM
silently; a duplicate SPN breaks Kerberos loudly. If you register manually after a service
account change, remove the old account’s SPNs first.

And if you are working through this with
an AI assistant, the useful handoff is the auth_scheme output plus the setspn -L listing,
with one question: does the SPN set explain the NTLM rows? If your assistant speaks MCP, the sqldba MCP server gives it this site’s verified reference to check against while you work.

---

Delegation in One Paragraph

For the double-hop to work, the service account for server A must be trusted to delegate to
the SQL service on server B. Use constrained delegation, listing the specific services,
rather than unconstrained, which hands the account a blank cheque worth flagging in any
security review. This is Active Directory configuration, not SQL Server configuration, so it
belongs in change control with the domain team in the room.

---

Best Practices, the Short List

• Run the auth_scheme query on every production instance once, record what normal is, and
  recheck after service account or DNS changes.
• Connect by DNS name, not IP, everywhere a human can influence the connection string.
• Give the service account SPN self-registration rights (or use a gMSA) instead of managing
  SPNs by hand and by memory.
• Treat setspn -X duplicates as defects to fix immediately; treat NTLM fallback as technical
  debt to fix deliberately.
• Before any NTLM-restriction policy tightens, run the auth_scheme query estate-wide; the
  NTLM rows are the outage list.

---

Related

The error series this post connects: Login Failed (18456) ·
Untrusted Domain (18452) ·
Account Disabled (18470) ·
SSPI Handshake Failed (17806) ·
Network Path Not Found (Error 53).
For the layers underneath: Default Ports and
Connection Encryption and Protocol.

If the connection is failing outright rather than authenticating the wrong way, start at Cannot Connect to SQL Server: The Checks in the Order That Finds It, which routes every connection error to its fix in the order that finds it.