# Sqlspn Review

> 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 missin…

- **Type:** Skill
- **Install:** `agentstack add skill-vanterx-mssql-performance-skills-sqlspn-review`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [vanterx](https://agentstack.voostack.com/s/vanterx)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [vanterx](https://github.com/vanterx)
- **Source:** https://github.com/vanterx/mssql-performance-skills/tree/main/skills/sqlspn-review

## Install

```sh
agentstack add skill-vanterx-mssql-performance-skills-sqlspn-review
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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

```powershell
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](https://github.com/vanterx)
- **Source:** [vanterx/mssql-performance-skills](https://github.com/vanterx/mssql-performance-skills)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-vanterx-mssql-performance-skills-sqlspn-review
- Seller: https://agentstack.voostack.com/s/vanterx
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.