Fixing “Cannot generate SSPI context” (Event 5586) between SharePoint Server 2019 and SQL Server 2019

Executive summary

If your SharePoint Server 2019 farm suddenly can’t talk to SQL Server 2019 and throws Event 5586 with “The target principal name is incorrect. Cannot generate SSPI context.”, the culprit is almost always Kerberos failing because of a broken Service Principal Name (SPN) for the SQL Server service. Repairing or re-registering the correct MSSQLSvc SPNs on the SQL Server service account resolves it.

Symptoms

  • SharePoint ULS / Windows Application log: Event 5586 (SharePoint Foundation) with Unknown SQL Exception 0 and “The target principal name is incorrect. Cannot generate SSPI context.”
  • SQL Server ERRORLOG may note it could not register SPNs and that Kerberos will fall back to NTLM (or fail outright if policy requires Kerberos).

What’s actually wrong

Kerberos identifies SQL Server using an SPN. If the SPN is missing, registered on the wrong account, duplicated, or you’re connecting through a name (CNAME/alias) that lacks a matching SPN, Kerberos can’t obtain a ticket and SSPI errors out.

In short: SharePoint is fine; fix the SQL Server SPNs.

A Microsoft Q&A thread outlines the same pattern and resolution steps administrators commonly use in the field (validate name resolution, remove misplaced SPNs, and re-add them on the SQL service account)

Before you start

  • Identify the SQL Server service account (domain user or gMSA) actually running the SQL Server (MSSQLSERVER) or named instance service.
  • Know the hostname(s) and port clients use:
    • Default instance: typically TCP 1433
    • Named instance / non-default: use the explicit TCP port you configured
  • Know all client names used to connect (FQDN, short name, any CNAME/SQL alias)—you need SPNs for every name in use.

Step 1 — Confirm the problem is SPN/Kerberos

On the SharePoint server, quickly sanity-check:

cmdCopyEdit:: See if you already hold a Kerberos ticket to SQL (nothing = bad SPN)
klist tickets | findstr /I MSSQLSvc

:: Clear any cached tickets (optional while testing)
klist purge

On the SQL Server, check for SPN warnings and auth scheme:

sqlCopyEdit-- Look for SPN errors in the error log
EXEC xp_readerrorlog 0, 1, 'SPN';

-- When you connect again, verify Kerberos vs NTLM
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;

“KERBEROS” = good; “NTLM” (when Kerberos is required) or connection failure = investigate SPNs.

Step 2 — Inspect current SPNs

Run these on a domain controller or any box with RSAT/AD tools:

cmdCopyEdit:: List all SPNs on the SQL service account
setspn -L CONTOSO\sqlsvc

:: Find which account (if any) has a given SPN
setspn -Q MSSQLSvc/sql01.contoso.local:1433
setspn -Q MSSQLSvc/sql01.contoso.local
setspn -Q MSSQLSvc/sql01:1433

:: Find duplicate SPNs in the domain/forest
setspn -X

What you’re looking for

  • The MSSQLSvc/ SPNs must exist only on the SQL service account—not on the computer account or a retired account.
  • No duplicates. Duplicates break Kerberos.

Step 3 — Fix the SPNs (manual method)

Prefer -S to add; it validates duplicates before writing.

A) Remove misplaced SPNs (if any)

cmdCopyEdit:: Example: SPN was wrongly registered on the SQL computer account
setspn -D MSSQLSvc/sql01.contoso.local:1433 CONTOSO\SQL01$
setspn -D MSSQLSvc/sql01.contoso.local CONTOSO\SQL01$
setspn -D MSSQLSvc/sql01 CONTOSO\SQL01$

B) Register correct SPNs on the SQL service account

Default instance on 1433:

cmdCopyEditsetspn -S MSSQLSvc/sql01.contoso.local:1433 CONTOSO\sqlsvc
setspn -S MSSQLSvc/sql01.contoso.local      CONTOSO\sqlsvc
setspn -S MSSQLSvc/sql01                    CONTOSO\sqlsvc

Named instance / non-default port (example TCP 51433):

cmdCopyEditsetspn -S MSSQLSvc/sql01.contoso.local:51433 CONTOSO\sqlsvc
setspn -S MSSQLSvc/sql01:51433               CONTOSO\sqlsvc

If clients use an alias/CNAME like sqlapp.contoso.local, add SPNs for that alias too:

cmdCopyEditsetspn -S MSSQLSvc/sqlapp.contoso.local:1433 CONTOSO\sqlsvc
setspn -S MSSQLSvc/sqlapp.contoso.local      CONTOSO\sqlsvc

Correct SPN formats and when to include port are documented by Microsoft.

C) Give SQL service account permission to self-register SPNs (optional but recommended)

So SQL can keep SPNs current at service startup, grant the service account Write servicePrincipalName on itself or manage it per your policy.

D) Restart & retest

  • Restart SQL Server service.
  • On a SharePoint server: klist purge, recycle IIS app pools (or restart the server during a maintenance window).
  • Reconnect and check auth_scheme = KERBEROS. (Microsoft, 2023).
Tags: