/sqlspn-review

name: sqlspn-review
description: Analyzes SQL Server SPN (Service Principal Name) configuration and Kerberos delegation settings to diagnose authentication failures, NTLM fallback, and double-hop connectivity problems. Use this skill when users receive Kerberos errors, "Cannot generate SSPI context", ANONYMOUS LOGON failures, linked servers fall back to NTLM, AG listener connections fail, or constrained delegation is needed for a middle-tier application, and you need to identify missing, duplicate, or misconfigured SPNs and delegation settings. Applies 54 checks (K1–K54) covering SPN presence, service account binding, AG listener and alias, permissions, Kerberos delegation, AD account sensitivity, Entra ID hybrid, gMSA/FCI scenarios, double-hop platform constraints for linked servers and SSISDB, clock skew and token size prerequisites, SQL Server on Linux keytabs, and client driver limitations.
triggers:
  - /sqlspn-review

SQL Server SPN and Kerberos Delegation Review Skill

Purpose

Analyze SQL Server SPN configuration and Active Directory delegation attributes to surface Kerberos authentication failures, NTLM fallback causes, and double-hop connectivity problems. Applies 54 checks (K1–K54) across eleven categories:

• **K1–K6** — MSSQLSvc SPN presence: default instance, named instance, FQDN variant,

short-hostname variant, port mismatch, and FCI Virtual Network Name

• **K7–K11** — Service account binding: SPN on wrong account, duplicate SPNs, machine account

vs domain account, stale SPNs from old accounts, MSA/gMSA auto-registration gaps

• **K12–K16** — AG listener and alias: listener SPN, named instance port conflict, SQL Browser,

alias SPN, multi-subnet listener coverage

• **K17–K20** — Configuration and permissions: HTTP SPN, registration permission gap,

unconstrained delegation, NTLM fallback signal

• **K21–K25** — Kerberos delegation — service account: constrained delegation (KCD) not

configured, missing target SPN, protocol transition, RBCD misconfiguration, delegation scope

• **K26–K30** — AD account and computer sensitivity: AccountNotDelegated on end-user, Protected

Users membership on end-user, computer account SPN conflict, computer account unconstrained delegation, service account in Protected Users

• **K31–K40** — Entra ID / hybrid and advanced scenarios: Entra ID hybrid SPN gap, Entra-only

auth with orphaned AD SPN, Azure SQL MI Windows Authentication flow, gMSA rollover drift, FCI node SPN leak, distributed AG forwarder SPN, TrustedToAuthForDelegation on an RBCD path, encryption type mismatch, AdminSDHolder SPN write block, DNS CNAME alias without SPN

• **K41–K44** — Double-hop platform constraints: linked server RBCD unsupported, linked server

constrained delegation version floor, SSISDB constrained delegation unsupported, named instance dynamic port

• **K45–K48** — Kerberos environment prerequisites: clock skew, token size, RC4-only accounts

under AES enforcement, forest boundary

• **K49–K51** — SQL Server on Linux: keytab not configured, keytab encryption type mismatch,

keytab ownership and permissions

• **K52–K54** — Client driver and service state: legacy provider over named pipes, service

account password change without restart, Report Server missing RSWindowsNegotiate

Input

Accept any of:

1. **setspn output** — paste output from one or more of:

• `setspn -L domain\sqlsvc` (SPNs registered on a specific account)
• `setspn -Q MSSQLSvc/*` (all MSSQLSvc SPNs in the domain)
• `setspn -X` (duplicate SPN report across all accounts)

2. **AD attribute output** — paste output from `Get-ADUser`, `Get-ADComputer` or `Get-ADServiceAccount` showing delegation and encryption attributes (`TrustedForDelegation`, `TrustedToAuthForDelegation`, `msDS-AllowedToDelegateTo`, `msDS-AllowedToActOnBehalfOfOtherIdentity`, `msDS-SupportedEncryptionTypes`, `memberOf`) 3. **Kerberos Configuration Manager or SQLCHECK output** — paste the KCM SPN tab results or the SQLCHECK `Suggested SPN / Exists / Status` table 4. **Linux keytab output** — paste `klist -kte <keytab>`, `mssql-conf validate-ad-config` results, or the `network.kerberoskeytabfile` / `network.privilegedadaccount` settings 5. **Natural language description** — describe the authentication failure, the SQL instance name, the service account, and any error messages observed

For best results, provide output from all capture commands below. When only partial data is available, state which checks cannot be evaluated and why.

Capture Commands

Microsoft ships two diagnostic tools for this problem class. Prefer them over hand-reading `setspn` output — both produce a structured verdict the analysis can consume directly.

• **Microsoft Kerberos Configuration Manager for SQL Server (KCM)** — connects to the instance, reports per-SPN Status (Good / Missing / Duplicate / Misplaced / Dynamic Port), and generates a fix script when the running account lacks AD write rights. This is the tool Microsoft's "Cannot generate SSPI context" guidance reaches for first.
• **SQLCHECK** — emits a `Suggested SPN / Exists / Status` table covering all four expected name forms, mapping directly onto K1, K3 and K4.

setspn -Q MSSQLSvc/*
setspn -L DOMAIN\sqlsvc
setspn -X
Get-ADUser DOMAIN\sqlsvc -Properties TrustedForDelegation, TrustedToAuthForDelegation, msDS-AllowedToDelegateTo, ServicePrincipalNames, MemberOf, msDS-SupportedEncryptionTypes
Get-ADComputer SQLNODE1 -Properties TrustedForDelegation, msDS-AllowedToActOnBehalfOfOtherIdentity, ServicePrincipalNames, msDS-SupportedEncryptionTypes
# gMSA / MSA service accounts (K11, K34)
Get-ADServiceAccount sqlsvc -Properties ServicePrincipalNames, msDS-SupportedEncryptionTypes
# Verify cached Kerberos tickets on the client machine (run as the connecting user)
klist
# Clear ticket cache to force fresh acquisition during testing
klist purge
# Request a service ticket directly — surfaces KDC_ERR_ETYPE_NOTSUPP and principal er