/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, 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 40 checks (K1–K40) covering SPN presence, service account binding, AG listener and alias, permissions, Kerberos delegation, AD account sensitivity, Azure AD hybrid, and advanced gMSA/FCI/delegation scenarios.
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 40 checks (K1–K40) across seven 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** — Azure AD / hybrid and advanced scenarios: Entra ID hybrid SPN gap, Entra-only

auth with orphaned AD SPN, Azure SQL MI on-premises SPN, gMSA rollover drift, FCI node SPN leak, distributed AG forwarder SPN, S4U2Proxy without protocol transition, Kerberos FAST incompatibility, AdminSDHolder SPN write block, DNS CNAME alias without SPN

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` or `Get-ADComputer` showing delegation attributes (`TrustedForDelegation`, `TrustedToAuthForDelegation`, `msDS-AllowedToDelegateTo`, `msDS-AllowedToActOnBehalfOfOtherIdentity`, `memberOf`) 3. **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

setspn -Q MSSQLSvc/*
setspn -L DOMAIN\sqlsvc
setspn -X
Get-ADUser DOMAIN\sqlsvc -Properties TrustedForDelegation, TrustedToAuthForDelegation, msDS-AllowedToDelegateTo, ServicePrincipalNames, MemberOf
Get-ADComputer SQLNODE1 -Properties TrustedForDelegation, msDS-AllowedToActOnBehalfOfOtherIdentity, ServicePrincipalNames
# 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

---

Thresholds Reference

| Threshold | Value | Used by | |-----------|-------|---------| | Duplicate SPN | 2 or more accounts holding identical SPN | K8 — Critical | | Port mismatch tolerance | Exact match required between SPN port and SQL TCP port | K5, K13 | | Unconstrained delegation | Any account with TrustedForDelegation = True | K19, K29 — Critical | | Delegation target missing SPN | Any missing target SPN in msDS-AllowedToDelegateTo | K22 — Critical | | Protected Users membership | Any SQL service account or end-user in Protected Users | K27, K30 — Critical |

---

MSSQLSvc SPN Presence Checks (K1–K6)

Run these first. They confirm the KDC can resolve the SQL Server target.

K1 — Missing Default-Instance SPN

• **Trigger:** SQL Server is the default instance (port 1433) but none of the required SPN forms are present on the service account: `MSSQLSvc/<host>:1433`, `MSSQLSvc/<FQDN>:1433`, or the portless form `MSSQLSvc/<FQDN>` (used by clients that do not specify a port in their connection string)
• **Severity:** Critical
• **Fix:** Register all three forms: `setspn -S MSSQLSvc/<host>:1433 DOMAIN\sqlsvc`, `setspn -S MSSQLSvc/<host.domain.com>:1433 DOMAIN\sqlsvc`, and `setspn -S MSSQLSvc/<host.domain.com> DOMAIN\sqlsvc` (portless form for legacy or unspecified-port clients)

K2 — Missing Named-Instance SPN

• **Trigger:** Named SQL instance present but no `MSSQLSvc/<host>:<port>` SPN exists for the instance's TCP port AND no `MSSQLSvc/<host>:<instancename>` SPN exists for named-pipe / shared-memory connections. Both forms are valid per Microsoft documentation and both should be registered.
• **Severity:** Critical
• **Fix:** Register both SPN forms: `setspn -S MSSQLSvc/<host>:<port> DOMAIN\sqlsvc` (using the actual TCP port from SQL Server Configuration Manager) AND `setspn -S MSSQLSvc/<host>:<instancename> DOMAIN\sqlsvc` (using the instance name, e.g. `SQLNODE1\INST1`). Clients connecting via TCP use the port-based form; clients using named pipes or shared memory use the instance-name form.

K3 — Missing FQDN SPN

• **Trigger:** Short-hostname SPN exists (`MSSQLSvc/SQLNODE1:1433`) but no fully-qualified SPN (`MSSQLSvc/SQLNODE1.domain.com: