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)

1. AD attribute output — paste output from Get-ADUser or Get-ADComputer showing

delegation attributes (TrustedForDelegation, TrustedToAuthForDelegation, msDS-AllowedToDelegateTo, msDS-AllowedToActOnBehalfOfOtherIdentity, memberOf)

1. 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/:1433, MSSQLSvc/:1433, or the portless form MSSQLSvc/ (used by clients that do not specify a port in their connection string)
• Severity: Critical
• Fix: Register all three forms: setspn -S MSSQLSvc/:1433 DOMAIN\sqlsvc, setspn -S MSSQLSvc/:1433 DOMAIN\sqlsvc, and setspn -S MSSQLSvc/ DOMAIN\sqlsvc (portless form for legacy or unspecified-port clients)

K2 — Missing Named-Instance SPN

• Trigger: Named SQL instance present but no MSSQLSvc/: SPN exists for the instance's TCP port AND no MSSQLSvc/: 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/: DOMAIN\sqlsvc (using the actual TCP port from SQL Server Configuration Manager) AND setspn -S MSSQLSvc/: 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:1433)
• Severity: Warning
• Fix: setspn -S MSSQLSvc/:1433 DOMAIN\sqlsvc; clients using FQDN in their connection string fail Kerberos without the FQDN variant

K4 — Missing Short-Hostname SPN

• Trigger: FQDN SPN exists (MSSQLSvc/SQLNODE1.domain.com:1433) but no short-hostname SPN (MSSQLSvc/SQLNODE1:1433)
• Severity: Warning
• Fix: setspn -S MSSQLSvc/:1433 DOMAIN\sqlsvc; clients using NetBIOS name in the connection string will fall back to NTLM

K5 — SPN on Wrong Port

• Trigger: A MSSQLSvc/: SPN exists but the port does not match the SQL Server's actual TCP listening port
• Severity: Critical — see Thresholds Reference (exact match required)
• Fix: setspn -D MSSQLSvc/: DOMAIN\sqlsvc then setspn -S MSSQLSvc/: DOMAIN\sqlsvc

K6 — Missing VNN SPN for FCI

• Trigger: Failover Cluster Instance (FCI) detected but no SPN registered for the Virtual Network Name (VNN)
• Severity: Critical
• Fix: Register MSSQLSvc/:1433 (or appropriate port) on the service account; the VNN, not the physical node name, is what clients connect to

---

Service Account Binding Checks (K7–K11)

K7 — SPN on Wrong Account

• Trigger: MSSQLSvc/: SPN is registered on an account other than the SQL Server service account currently running the instance
• Severity: Critical
• Fix: setspn -D MSSQLSvc/: DOMAIN\wrongaccount then setspn -S MSSQLSvc/: DOMAIN\sqlsvc; verify the SQL Server service account in SQL Server Configuration Manager

K8 — Duplicate SPN

• Trigger: setspn -X or setspn -Q reveals the same MSSQLSvc/: registered on 2 or more accounts — see Thresholds Reference
• Severity: Critical
• Fix: setspn -D MSSQLSvc/: DOMAIN\duplicate-account; only one account should own the SPN; the KDC cannot disambiguate and will reject all Kerberos tickets for that target

K9 — SPN Under Computer Account

• Trigger: SQL Server runs under a domain account but an identical MSSQLSvc/: SPN is found on the machine (computer) account
• Severity: Warning
• Fix: Remove SPN from computer account; move to service account; disable automatic SPN registration to prevent re-registration by NETWORK SERVICE logic

K10 — Stale SPN from Old Account

• Trigger: MSSQLSvc/: SPN found on a former service account after the service account was changed
• Severity: Warning
• Fix: setspn -D MSSQLSvc/: DOMAIN\oldsqlsvc; stale SPNs cause K8 (duplicate) even after a planned account migration

K11 — MSA/gMSA Auto-Registration Gap

• Trigger: SQL Server runs as a Managed Service Account (MSA) or group Managed Service Account (gMSA) but the FQDN variant of the SPN is absent from the account's ServicePrincipalNames attribute
• Severity: Info
• Fix: Verify both MSSQLSvc/:port and MSSQLSvc/:port exist; MSA/gMSA auto-registration creates the short-hostname SPN but sometimes skips the FQDN variant

---

AG Listener and Alias Checks (K12–K16)

K12 — Missing AG Listener SPN

• Trigger: An Always On Availability Group listener name is referenced in the input but no MSSQLSvc/: SPN is registered
• Severity: Critical
• Fix: Register setspn -S MSSQLSvc/:1433 DOMAIN\sqlsvc on each replica's service account; the listener name resolves differently than the node hostname

K13 — Named Instance Using Port 1433

• Trigger: Named instance SPN is registered with port 1433 (MSSQLSvc/:1433) but the instance is not the default instance — see Thresholds Reference
• Severity: Critical
• Fix: Remove wrong-port SPN; determine the actual dynamic port in SQL Server Configuration Manager; register the correct port SPN

K14 — Missing SQL Browser Signal

• Trigger: Named instance exists and no confirmation that SQL Browser service is running is provided
• Severity: Info
• Fix: Verify SQL Browser service is running (Start-Service SQLBrowser); named instances depend on SQL Browser for port resolution when clients omit the explicit port from the connection string

K15 — Alias Without SPN

• Trigger: A SQL Server alias (via cliconfg or SQL Server Configuration Manager) is configured using a name that has no corresponding MSSQLSvc/: SPN
• Severity: Warning
• Fix: Register setspn -S MSSQLSvc/: DOMAIN\sqlsvc; Kerberos ticket requests use the connection target name, not the resolved hostname

K16 — Multi-Subnet AG Single-IP SPN

• Trigger: AG listener has multiple IP addresses (multi-subnet AG) but SPN is registered for only one hostname variant
• Severity: Warning
• Fix: Register SPN for each DNS name that resolves to the listener across subnets; clients on the secondary subnet may connect using a different name resolution path

---

Configuration and Permissions Checks (K17–K20)

K17 — HTTP SPN Missing

• Trigger: Delegation to a Reporting Services (SSRS) endpoint or linked server using HTTP is described, but no HTTP/ SPN is registered on the relevant service account
• Severity: Warning
• Fix: setspn -S HTTP/ DOMAIN\svcaccount; Kerberos delegation to HTTP targets requires the HTTP SPN on the target service account

K18 — SPN Registration Permission Gap

• Trigger: Service account lacks the Write ServicePrincipalName permission on its own AD user object, preventing self-registration of SPNs; or SQL Server ERRORLOG contains error 17806, 17807, or Windows return code 0x2098 in an SPN registration failure message
• Severity: Warning
• Fix: Grant the service account Self-Write SPN permission via ADSI Edit or dsacls; alternatively, a Domain Admin can register SPNs manually using setspn -S

K19 — Unconstrained Delegation Enabled

• Trigger: TrustedForDelegation = True on the SQL Server service account — see Thresholds Reference
• Severity: Critical
• Fix: Disable unconstrained delegation in AD Users and Computers; configure Kerberos Constrained Delegation (KCD) instead by populating msDS-AllowedToDelegateTo with only the target service SPNs; unconstrained delegation allows credential forwarding to any service

K20 — NTLM Fallback Signal

• Trigger: NTLM authentication is observed despite SPNs appearing to exist (sys.dm_exec_connections shows auth_scheme = NTLM); or Kerberos ticket requests fail with "target principal name is incorrect"; or the connection is a loopback (SQL Agent job, SSIS package on the same host, OPENQUERY to (local)) where Windows loopback detection blocks Kerberos regardless of SPN state
• Severity: Info
• Fix: Verify SPN matches the exact hostname in the client connection string (case-insensitive but must be character-for-character the same); check that SQL Server encryption settings do not redirect the connection to a different hostname; confirm the SPN is on the active service account

---

Kerberos Delegation — Service Account Checks (K21–K25)

K21 — Constrained Delegation Not Configured

• Trigger: A double-hop scenario is described (client → SQL A → SQL B or SQL → SSRS/linked server) but msDS-AllowedToDelegateTo is empty on the middle-tier service account
• Severity: Critical
• Fix: Configure KCD via AD Users and Computers → service account → Delegation tab → "Trust this user for delegation to specified services only"; add the target service SPNs

K22 — Delegation Target Missing SPN

• Trigger: KCD is configured (msDS-AllowedToDelegateTo is populated) but one or more listed target SPNs do not exist on any AD account — see Thresholds Reference
• Severity: Critical
• Fix: Register the missing SPN on the target service account; a KCD entry pointing to a non-existent SPN will fail silently at the KDC

K23 — Protocol Transition Not Enabled

• Trigger: Middle-tier application (SSRS, web service) needs to delegate credentials for users who authenticated via non-Kerberos means (NTLM, forms, certificate), but TrustedToAuthForDelegation is absent from the service account
• Severity: Warning
• Fix: Enable "Use any authentication protocol" on the service account in AD Users and Computers → Delegation tab; this enables S4U2Self (protocol transition) so the service can obtain a forwardable ticket for any user

K24 — RBCD Misconfigured

• Trigger: Resource-based Constrained Delegation (RBCD) is intended: msDS-AllowedToActOnBehalfOfOtherIdentity is present on the target computer, but the initiating service account is not in the ACL
• Severity: Warning
• Fix: Add the initiating service account's SID to the RBCD ACL on the target computer object: Set-ADComputer -PrincipalsAllowedToDelegateToAccount

K25 — Delegation Scope Too Broad

• Trigger: msDS-AllowedToDelegateTo contains service SPNs beyond MSSQLSvc/* — for example, cifs/* or host/* — that are not required for the intended SQL Server delegation path
• Severity: Info
• Fix: Narrow the delegation scope to only the specific target SPNs required; broad delegation targets reduce the security benefit of constrained delegation

---

AD Account and Computer Sensitivity Checks (K26–K30)

K26 — Connecting User Delegation-Sensitive

• Trigger: AccountNotDelegated = True is set on the end-user AD account that needs to authenticate through a delegating SQL Server
• Severity: Critical
• Fix: Remove AccountNotDelegated flag if delegation is intentional for this user (Set-ADUser -AccountNotDelegated 0). Note: RBCD (K24) uses S4U2Proxy which has different ticket requirements than classic KCD, but if the user is also a member of Protected Users (K27), neither KCD nor RBCD will work — Protected Users membership blocks ALL delegation regardless of type.

K27 — User in Protected Users Group

• Trigger: The end-user whose credentials need to be delegated is a member of the Protected Users security group — see Thresholds Reference
• Severity: Critical
• Fix: Remove the user from Protected Users if Kerberos delegation is required; note that Protected Users disables NTLM authentication, RC4 encryption, and all Kerberos delegation for the user — these protections require Windows Server 2012 R2 or later domain functional level (DFL) to take effect; review security implications before removing the user from the group

K28 — Computer Account SPN Conflict

• Trigger: SQL Server runs under a domain service account but the host computer account also holds MSSQLSvc/: SPNs — both accounts have the same SPN
• Severity: Warning
• Fix: Choose one owner: service account (preferred for security) or computer account; remove SPNs from the non-authoritative account using setspn -D

K29 — Computer Account Unconstrained Delegation

• Trigger: The SQL Server host computer account has TrustedForDelegation = True — see Thresholds Reference
• Severity: Critical
• Fix: Disable uncons

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: vanterx
• Source: vanterx/mssql-performance-skills
• License: MIT

Install and usage instructions live in the source repository linked above.