Kerberos vs NTLM in SQL Server: Best Practices

There are 2 ways in: a linked server failing as NT AUTHORITY\ANONYMOUS LOGON, and the discovery that an estate everyone assumed was on Kerberos is running on NTLM. Both are the same question, and one query answers it.

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

3 answers, and each means something different:

• KERBEROS, the connection authenticated with a ticket. Delegation is available, so a double hop can be made to work.
• NTLM, a Windows login that could not use Kerberos and fell back. Nothing failed and nothing was logged, and a double hop from this connection cannot work.
• SQL, a SQL-authenticated login. It sits outside this conversation entirely.

The lab row above is the lesson on its own: a standalone machine, a local connection or a missing SPN all produce NTLM with no error anywhere. 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 by SSPI Handshake Failed.

What the Error Log Already Told You

Before anyone goes near Active Directory, read what SQL Server wrote at startup. It tries to register its own SPNs every time the service starts, and it records the outcome:

EXEC sys.xp_readerrorlog 0, 1, N'SPN';

On an instance with no domain to register into that returns 3 rows, and the first 2 are the whole story:

That names the exact SPN it wanted, MSSQLSvc/HPAI01, and says in plain words that integrated authentication will fall back to NTLM. If a production error log says the same thing, the cause is settled without asking anybody for domain access. The healthy version of the message reports the SPN registered successfully, and that is the line to look for on a domain-joined instance: documented, not reproduced here, because a standalone machine has no domain to register into.

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 the query fails, which is that error and its fix in full. Kerberos with constrained 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. A domain is what registers one, so the setspn mechanics below are documented here, not reproduced:

setspn -L DOMAIN\sqlserviceaccount

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

setspn -X

Self-registration only succeeds when the service account holds 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. When it does not hold it, the error log says so at every startup.

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.