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 errors (K38, K47)
klist get MSSQLSvc/sqlnode1.contoso.com:1433
# Clock offset against the domain controller (K45)
w32tm /stripchart /computer:DC01.contoso.com /samples:3 /dataonly

Run this on the SQL Server instance to confirm whether a live connection actually negotiated Kerberos (K20):

SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

For SQL Server on Linux (K49–K51), capture the keytab configuration instead of setspn output:

/opt/mssql/bin/mssql-conf validate-ad-config /var/opt/mssql/secrets/mssql.keytab
klist -kte /var/opt/mssql/secrets/mssql.keytab
ls -l /var/opt/mssql/secrets/mssql.keytab

Thresholds Reference

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> (the documented default-instance SPN for protocols other than TCP — named pipes and shared memory)
• Severity: Critical
• Fix: Register the port form for TCP clients and the portless form for named-pipe / shared-memory clients: 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. SQLCHECK reports all four expected forms (FQDN:port, FQDN, NetBIOS:port, NetBIOS) with an Exists/Status column — use it to confirm coverage

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:1433)
• Severity: Warning
• Fix: setspn -S MSSQLSvc/<host.domain.com>: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/<netbios>:1433 DOMAIN\sqlsvc; clients using NetBIOS name in the connection string will fall back to NTLM

K5 — SPN on Wrong Port

• Trigger: A MSSQLSvc/<host>:<port> 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/<host>:<wrong-port> DOMAIN\sqlsvc then setspn -S MSSQLSvc/<host>:<correct-port> 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/<VNN>: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/<host>:<port> SPN is registered on an account other than the SQL Server service account currently running the instance
• Severity: Critical
• Fix: setspn -D MSSQLSvc/<host>:<port> DOMAIN\wrongaccount then setspn -S MSSQLSvc/<host>:<port> 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/<host>:<port> registered on 2 or more accounts — see Thresholds Reference
• Severity: Critical
• Fix: setspn -D MSSQLSvc/<host>:<port> 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/<host>:<port> 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/<host>:<port> SPN found on a former service account after the service account was changed
• Severity: Warning
• Fix: setspn -D MSSQLSvc/<host>:<port> 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/<host>:port and MSSQLSvc/<host.domain.com>: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/<listener>:<port> SPN is registered
• Severity: Critical
• Fix: Register the listener SPN once, against the service account of the instances hosting the availability replicas: setspn -S MSSQLSvc/<listener-name>.<domain>:1433 DOMAIN\sqlsvc. Registering it separately per replica creates the duplicate SPN that K8 flags as Critical. Prerequisite: for the SPN to work across all replicas, every instance in the WSFC cluster hosting the availability group must run under the same service account. If the listener uses a non-default port, the SPN and the client connection string must both carry that port

K13 — Named Instance Using Port 1433

• Trigger: Named instance SPN is registered with port 1433 (MSSQLSvc/<host>: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/<alias-name>:<port> SPN
• Severity: Warning
• Fix: Register setspn -S MSSQLSvc/<alias-name>:<port> 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 an HTTP target is described, the Report Server runs under a domain user account, and the HTTP/<host> SPN forms are absent from that account
• Severity: Warning
• Fix: Register both name forms — HTTP SPNs take no port: setspn -S HTTP/<host>.<domain> DOMAIN\svcaccount and setspn -S HTTP/<host> DOMAIN\svcaccount. Place the SPN on the identity the Report Server service runs under. When it runs as a Virtual Service Account or NETWORK SERVICE, no manual HTTP SPN is needed — the machine account's HOST SPN already covers HTTP — unless a virtual URL or load-balanced name is used, which needs its own SPN on the machine account. Registering an HTTP SPN grants tickets to every HTTP.SYS application on that host, so all of them must run under the same account or use host headers with separate SPNs. Pair with K54 (RSWindowsNegotiate)

K18 — SPN Registration Permission Gap

• Trigger: The SQL Server startup account lacks Read servicePrincipalName / Write servicePrincipalName on the SQL Server computer object in AD, so the DsWriteAccountSpn call made at service startup fails; or SQL Server ERRORLOG contains an SPN registration failure message
• Severity: Warning
• Fix: In Active Directory Users and Computers (View → Advanced), open the SQL Server computer object → Security → Advanced, add the startup account, and grant Validated write to service principal name plus the Read servicePrincipalName and Write servicePrincipalName properties. Note which identities self-register without this: built-in accounts (Local System, NETWORK SERVICE), virtual accounts, MSAs and gMSAs all register their own SPN. A domain administrator can otherwise register the SPN manually with setspn -S. Registration failure is logged to the SQL Server error log and the Application event log, and startup continues regardless

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 <target> -PrincipalsAllowedToDelegateToAccount <initiating-account>

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 <user> -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, after reviewing the security trade-off. The protections split by scope: device-side (no cached credentials for CredSSP, Windows Digest, NTLM or Kerberos long-term keys, no offline sign-in) apply when the user signs in to a Windows 8.1 / Windows Server 2012 R2 or later host; domain-controller-side (no NTLM authentication, no DES or RC4 in Kerberos preauthentication, no unconstrained or constrained delegation, TGT capped at a non-renewable 4 hours) apply only at Windows Server 2012 R2 or later domain functional level. Delegation is blocked regardless of type, so RBCD is no escape hatch. Diagnose with the ProtectedUserFailures-DomainController log: event 100 for NTLM failure, event 104 for a DES/RC4 preauthentication failure

K28 — Computer Account SPN Conflict

• Trigger: SQL Server runs under a domain service account but the host computer account also holds MSSQLSvc/<host>:<port> 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 unconstrained delegation on the computer account; configure KCD or RBCD on the computer object for only the specific service SPNs required

K30 — Service Account in Protected Users

• Trigger: The SQL Server service account is a member of the Protected Users security group — see Thresholds Reference
• Severity: Critical
• Fix: Remove the SQL Server service account from Protected Users immediately. Microsoft states plainly that accounts for services and computers should never be members: the group provides no protection there, because the password or certificate is always available on the host, and authentication fails with "the user name or password is incorrect". This is a certain failure, not a degradation. The domain-controller-side restrictions take effect at Windows Server 2012 R2 or later domain functional level. Protect service accounts with Authentication Policies and Authentication Policy Silos instead, which are designed for service, MSA and gMSA account classes

Azure AD / Hybrid and Advanced Checks (K31–K40)

K31 — Azure AD Hybrid Join SPN Gap

• Trigger: SQL Server instance is in an Entra ID (Azure AD) hybrid-joined environment but no MSSQLSvc/<host>:<port> SPN is registered in the on-premises AD for the SQL instance
• Severity: Critical
• Fix: Register the SPN in on-premises AD (setspn -S MSSQLSvc/<host>:<port> DOMAIN\sqlsvc); Entra Kerberos for hybrid-joined devices still resolves SPNs through on-premises AD for SQL Server targets — the SPN must exist on both legs

K32 — Entra-Only Auth With Orphaned AD SPN

• Trigger: SQL Server is configured for Azure AD–only authentication (CREATE LOGIN ... FROM EXTERNAL PROVIDER) but setspn -L shows a traditional AD MSSQLSvc SPN still registered on the service account — SQL 2022+ / Azure SQL
• Severity: Warning
• Fix: Remove the orphaned SPN (setspn -D MSSQLSvc/<host>:<port> DOMAIN\sqlsvc) to avoid confusing on-premises clients that attempt Kerberos against a SQL instance that no longer accepts Windows-integrated logins

K33 — Azure SQL MI Windows Authentication Flow Not Configured

• Trigger: Windows Authentication against an Azure SQL Managed Instance is described, but neither Microsoft Entra Kerberos authentication flow is in place — Azure SQL MI only. There is no MSSQLSvc SPN to register in on-premises AD for a managed instance; MI issues tickets through Microsoft Entra ID acting as its own Kerberos realm
• Severity: Critical
• Fix: Confirm AD is synchronised to Microsoft Entra ID (Microsoft Entra Connect), then enable whichever flow the clients qualify for. Modern interactive flow — Entra-joined or Entra hybrid-joined clients on Windows 10 20H1 / Windows Server 2022 or later, connecting from an interactive session; enable the Specify KDC proxy servers for Kerberos clients group policy mapping KERBEROS.MICROSOFTONLINE.COM to the tenant's KDC proxy URL. Incoming trust-based flow — AD-joined clients on Windows 10 / Windows Server 2012 or later with line of sight to a domain controller; create the Trusted Domain Object with Set-AzureADKerberosServer -Domain <domain> -DomainCredential <cred> -UserPrincipalName <upn> -SetupCloudTrust and deploy the Kerberos Proxy group policy. Then create the system-assigned service principal for the managed instance. Verify client eligibility with dsregcmd.exe /status. Not supported for Linux clients or for applications running as a service under the modern interactive flow

K34 — gMSA Password Rollover SPN Drift

• Trigger: SQL Server runs as a group Managed Service Account (gMSA) and the SPN list returned by setspn -L differs from the ServicePrincipalNames attribute in Get-ADServiceAccount output for the same gMSA
• Severity: Warning
• Fix: Re-register the missing SPNs manually; gMSA automatic password rollover can occasionally cause SPN registration to lag behind; verify with Test-ADServiceAccount and setspn -L

K35 — FCI Node-Specific SPN Leak

• Trigger: Physical node hostnames of a Failover Cluster Instance (FCI) appear in setspn -Q MSSQLSvc/* results alongside the Virtual Network Name (VNN) SPN
• Severity: Warning
• Fix: Remove SPNs registered against physical node names (setspn -D MSSQLSvc/<node-name>:<port> DOMAIN\sqlsvc); clients connecting to the physical node name may succeed with Kerberos while VNN connections fail, creating intermittent authentication failures after failover

K36 — Distributed AG Forwarder Listener SPN Missing

• Trigger: A Distributed Availability Group (DAG) is described and the forwarder replica's listener name has no MSSQLSvc/<forwarder-listener>:<port> SPN registered — SQL 2016+
• Severity: Critical
• Fix: Register setspn -S MSSQLSvc/<forwarder-listener-name>:<port> DOMAIN\sqlsvc on each replica's service account; the forwarder introduces a second AG whose listener is an additional Kerberos target distinct from either underlying AG's listener

K37 — TrustedToAuthForDelegation Set for an RBCD Path

• Trigger: Resource-based Constrained Delegation (RBCD) is in use (msDS-AllowedToActOnBehalfOfOtherIdentity populated on the target) and TrustedToAuthForDelegation has been enabled on the initiating account to make protocol transition work. RBCD cannot use that bit: the KDC always allows protocol transition when performing RBCD, as though the bit were set. Setting it grants unnecessary protocol-transition privilege without affecting the RBCD path
• Severity: Warning
• Fix: Clear the flag on the initiating account (Set-ADUser <account> -TrustedToAuthForDelegation $false) unless a separate classic-KCD path genuinely requires it — classic KCD (K23) does still need it. To control RBCD access by how the user authenticated, ACL the back-end service against the two well-known SIDs the KDC stamps into the ticket: S-1-18-1 (AUTHENTICATION_AUTHORITY_ASSERTED_IDENTITY, client proved possession of its own credentials) and S-1-18-2 (SERVICE_ASSERTED_IDENTITY, identity asserted by a service via protocol transition). See K41 before choosing RBCD for a linked-server path

K38 — Encryption Type Mismatch Between Account and KDC Policy

• Trigger: The SQL Server service account's msDS-SupportedEncryptionTypes bitmask does not intersect the encryption types the KDC will issue. Bit values: 0x4 (4) RC4-HMAC, 0x8 (8) AES128-CTS-HMAC-SHA1-96, 0x10 (16) AES256-CTS-HMAC-SHA1-96; 0x18 (24) is the hardened AES-only state and 0x1C (28) the transitional RC4+AES state. Includes the Kerberos FAST armoring case, where DCs enforce armoring and the account holds no AES keys — Windows Server 2012+
• Severity: Warning
• Fix: Set AES support on the account (Set-ADUser <sqlsvc> -KerberosEncryptionType AES128,AES256, or Set-ADServiceAccount for an MSA/gMSA). Confirm the resulting ticket type with klist get MSSQLSvc/<fqdn>:1433 — a KDC that cannot satisfy the request returns KDC_ERR_ETYPE_NOTSUPP (0xE), also visible as the error code in KDC events 4768 and 4769. See K47 for the specific RC4-only case under AES enforcement

K39 — Write-SPN Blocked by AdminSDHolder

• Trigger: The SQL Server service account's ServicePrincipalName attribute shows DENY on Write ServicePrincipalName or the account is a member of a privileged AD group (Domain Admins, Enterprise Admins, etc.) — AdminSDHolder resets ACLs hourly via SDProp
• Severity: Warning
• Fix: Move the SQL Server service account out of privileged AD groups (use a dedicated low-privilege domain account for SQL services); SDProp only resets ACLs on accounts in adminCount=1 groups; a dedicated service account is not subject to AdminSDHolder

K40 — DNS CNAME Alias Without SPN

• Trigger: The connection string or client configuration references a DNS CNAME alias that resolves to the SQL Server host's A record, but no MSSQLSvc/<cname>:<port> SPN is registered for the alias name
• Severity: Critical
• Fix: Register setspn -S MSSQLSvc/<cname>:<port> DOMAIN\sqlsvc; Kerberos ticket requests use the name from the client connection string, not the resolved A record; a CNAME alias requires its own SPN entry independent of the host's SPN

Double-Hop Platform Constraints (K41–K44)

SQL Server features differ in which delegation models they support. Evaluate these before recommending KCD (K21) or RBCD (K24) for any double-hop, because the general advice is wrong for linked servers and for SSISDB.

K41 — Linked Server Delegation Path Relies on RBCD

• Trigger: A linked-server double-hop is described (client → SQL A → linked server B) and the delegation path is built on Resource-Based Constrained Delegation — msDS-AllowedToActOnBehalfOfOtherIdentity populated on the target rather than msDS-AllowedToDelegateTo on the middle tier. Linked servers do not support RBCD in any version
• Severity: Critical
• Fix: Rebuild the path on a supported model. Either classic constrained delegation on the middle-tier service account (msDS-AllowedToDelegateTo listing the target's MSSQLSvc SPNs — requires SQL Server 2017 CU17 or later, see K42), or full delegation ("Trust this user for delegation to any service") on the middle-tier account where the version bar cannot be met. Clear the RBCD ACL on the target once the supported path works. Full delegation re-triggers K19, which is expected here — record the exception rather than reverting

K42 — Linked Server Constrained Delegation Below SQL 2017 CU17

• Trigger: Constrained delegation is configured for a linked-server pass-through path but the middle-tier instance is older than SQL Server 2017 (14.x) CU17. Active Directory pass-through authentication over a linked server supported only full delegation before that build — SQL 2017 CU17+
• Severity: Critical
• Fix: Patch the middle-tier instance to SQL Server 2017 CU17 or later, or switch that account to full delegation until you can. Confirm the build with SELECT SERVERPROPERTY('ProductVersion'). Symptom when unsupported: the second hop authenticates as NT AUTHORITY\ANONYMOUS LOGON despite SPNs and delegation appearing correct

K43 — SSISDB Package Double-Hop Under Constrained Delegation

• Trigger: An SSIS package stored in the SSISDB catalog is executed remotely and connects onward to a third machine, while the service account of the instance hosting SSISDB is configured for constrained delegation. SSISDB does not support constrained delegation — ISServerExec.exe cannot delegate the credentials onward
• Severity: Critical
• Fix: Grant "Trust this user for delegation to any service (Kerberos Only)" to the SQL Server service account on the machine hosting SSISDB. This is a documented exception to K19: report both, and state that unconstrained delegation is required here rather than flagging it as a defect to remove. Where Windows Credential Guard is enabled it mandates constrained delegation, which makes the two requirements mutually exclusive — in that case move the package out of the SSISDB catalog (file system or MSDB deployment) or collapse the hop

K44 — Named Instance Dynamic Port Prevents Kerberos

• Trigger: A named instance is left on its default dynamic-port configuration (TCP Dynamic Ports populated, TCP Port empty) in an environment that requires Kerberos. The port changes across restarts, so no port-form SPN stays valid. Distinct from K13 and K5, which cover a wrong port that is at least stable
• Severity: Critical
• Fix: Pin a static port: SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for the instance → TCP/IP → IP Addresses tab. If Listen All is Yes, clear TCP Dynamic Ports under IPAll and set TCP Port; if No, do the same for each enabled IP. Restart the instance, then register the SPN against that port. Kerberos Configuration Manager reports this condition explicitly as a Dynamic Port status

Kerberos Environment Prerequisites (K45–K48)

Failures in this group produce the same symptoms as a broken SPN — NTLM fallback, SSPI errors, refused logins — while the SPN set is entirely correct. Evaluate them before concluding that SPN registration is the root cause.

K45 — Clock Skew Beyond Kerberos Tolerance

• Trigger: The clock difference between the client or SQL Server host and the domain controller exceeds the Kerberos tolerance — see Thresholds Reference. Observable as KRB_AP_ERR_SKEW, or as event ID 4 with KERB_AP_ERR_SKEW in the System log
• Severity: Critical
• Fix: Resynchronise the affected host against a domain controller in its own domain (w32tm /resync /rediscover, then w32tm /stripchart to confirm). Windows normally corrects drift automatically; manual action is needed when the clock is out by more than 48 hours, or when the host is not using a domain controller in its domain as its time source. For the forest, point the forest-root PDC emulator at a reliable external time source and let every other DC, member server and client inherit down the domain hierarchy

K46 — Kerberos Token Size Exceeded

• Trigger: SQL Server logs error 17832, or a connecting principal's group membership is large enough that the Kerberos PAC exceeds MaxTokenSize — see Thresholds Reference. Estimate with TokenSize = 1200 + 40d + 8s, where d counts universal groups outside the account's domain plus sIDHistory SIDs, and s counts in-domain universal, domain-local and global group memberships
• Severity: Critical
• Fix: Prefer reducing the token: rationalise group membership and clear stale sIDHistory entries left over from a forest migration. Where the registry must be raised, set MaxTokenSize under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters on every machine in the authentication chain — client, any middle tier, and the SQL Server host — then restart each. Keep the value at or below 48000 where IIS is in the path, since IIS caps the HTTP request buffer at 64 KB and base64 expansion consumes the difference. Enable Computer Configuration\Administrative Templates\System\KDC\Warning for large Kerberos tickets to get event ID 31 ahead of the failure

K47 — RC4-Only Encryption Types Under AES Enforcement

• Trigger: The SQL Server service account resolves to RC4 only — msDS-SupportedEncryptionTypes is 4, or is unset while the KDC's DefaultDomainSupportedEncTypes has been hardened to AES — in a domain where RC4 has been disabled. Ticket requests fail with KDC_ERR_ETYPE_NOTSUPP (0xE) in KDC events 4768 and 4769
• Severity: Critical
• Fix: Set AES explicitly on the account rather than relying on the domain default: Set-ADUser <sqlsvc> -KerberosEncryptionType AES128,AES256 (or Set-ADServiceAccount for MSA/gMSA), which corresponds to a bitmask of 24. Use 28 while RC4 is still needed as a transitional fallback. An account created before AES support and never password-reset holds no AES keys at all — reset the password to generate them. Read the current state with Get-ADObject -Properties msDS-SupportedEncryptionTypes and confirm the fix with klist get MSSQLSvc/<fqdn>:1433

K48 — Client and Server Across a Forest Boundary

• Trigger: The connecting principal's domain and the SQL Server's domain are not the same domain and have no trust path, or sit in different forests. Kerberos for SQL Server requires client and server to be in the same Windows domain or in trusted domains
• Severity: Warning
• Fix: Establish or repair the trust, or move the principal into a trusted domain. Where a trust exists but tickets still fail with an unsupported-etype error, the trust object itself may not advertise AES — set it with ksetup /setenctypeattr <trustingdomain> AES128-CTS-HMAC-SHA1-96 AES256-CTS-HMAC-SHA1-96 on a DC in the trusted domain, and configure both sides of a two-way transitive trust. Note that classic KCD (K21) cannot cross a domain boundary at all; RBCD (K24) can

SQL Server on Linux Kerberos (K49–K51)

SQL Server on Linux does not self-register SPNs. It authenticates from a keytab file referenced by mssql-conf, so the Windows checks above are supplemented by keytab state — SQL 2017+ on Linux.

K49 — Keytab Not Configured in mssql-conf

• Trigger: SQL Server on Linux is joined to Active Directory but network.kerberoskeytabfile or network.privilegedadaccount is unset in /var/opt/mssql/mssql.conf, or mssql-conf validate-ad-config reports a failure
• Severity: Critical
• Fix: Create the keytab (/opt/mssql/bin/mssql-conf setup-ad-keytab /var/opt/mssql/secrets/mssql.keytab <aduser>, or adutil keytab createauto -k <path> -p <port> -H <fqdn> -s MSSQLSvc), then point the instance at it: /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab and /opt/mssql/bin/mssql-conf set network.privilegedadaccount <aduser>. Restart with systemctl restart mssql-server and re-run validate-ad-config. Register the SPNs separately with adutil spn addauto -n <aduser> -s MSSQLSvc -H <fqdn> -p <port> — omitting -p generates portless SPNs that only work on the default port

K50 — Keytab Encryption Types Mismatch the AD Account

• Trigger: klist -kte <keytab> shows encryption types that do not intersect what the AD account and domain will issue, or the keytab contains only arcfour-hmac in a domain enforcing AES
• Severity: Critical
• Fix: Recreate the keytab entries with an AES type the domain supports (adutil keytab createauto ... -e aes256-cts-hmac-sha1-96), and enable This account supports Kerberos AES 128/256 bit encryption on the privileged AD account. arcfour-hmac is documented as weak and not recommended for production — carry it only during transition. Note that AD reads operatingSystemVersion when deciding whether a host understands AES: a Linux account reporting a major version below 6 has msDS-SupportedEncryptionTypes ignored in favour of the domain assumption, which is a documented cause of RC4 tickets that setting the attribute alone will not fix. Relate

…(truncated)