- 8 minutes to read

SQL Server SPNs (MSSQLSvc): Kerberos for Nodinite Databases and Linked Servers

When a Nodinite service connects to SQL Server with Windows authentication, it asks Active Directory for a Kerberos ticket for an MSSQLSvc/... SPN. When the Configuration Database then queries a remote Log Database through a Linked Server, SQL Server has to forward that identity in a second hop. If an SPN is missing, duplicated, or on the wrong account, you see:

  • "Cannot generate SSPI context" when a Nodinite service, the Web API, or an install tool connects to SQL Server
  • "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" on linked server queries
  • Kerberos that works on one cluster node, or before a failover, and fails afterwards

This guide covers the SQL Server side of SPNs. For browser and Web Client authentication, see HTTP SPNs for IIS. For the concepts, the shared setspn commands, and the service account rules both sides share, see the SPN hub.

Understanding SQL Server Kerberos in Nodinite

SQL Server SPNs are about service-to-service authentication. Users never connect to SQL Server directly; Nodinite services do, and SQL Server itself becomes a client when it follows a linked server.

sequenceDiagram opt Kerberos TGT Logging Service ->> Domain Controller: Service account credentials Domain Controller ->> Logging Service: Kerberos TGT end opt Service ticket Logging Service ->> Domain Controller: Send TGT to request a ticket for MSSQLSvc/sql-config Domain Controller ->> Logging Service: Service ticket end Logging Service ->> SQL (Configuration Database): Client TGT and service ticket opt Delegated service ticket SQL (Configuration Database) ->> Domain Controller: Client TGT to request a ticket for MSSQLSvc/sql-log Domain Controller ->> SQL (Configuration Database): Service ticket end SQL (Configuration Database) ->> Remote SQL (Log Database): Client TGT and SQL service ticket Remote SQL (Log Database) ->> SQL (Configuration Database): Response SQL (Configuration Database) ->> Logging Service: Response

Sequence: the Logging Service authenticates to the Configuration Database, which uses delegation to reach a remote Log Database through a linked server. Each SQL Server instance needs its own MSSQLSvc SPN.

Key Components Explained

Component Role Requirement
SQL Server service account Owns the MSSQLSvc SPNs and decrypts incoming tickets Domain account or gMSA for distributed Kerberos
MSSQLSvc SPN Identifies one SQL Server instance by host, port, or instance name One per name clients use, on the SQL service account
Linked server Lets the Configuration Database query Log Databases on other instances Configured with "be made using the login's current security context"
Delegation Lets the Configuration Database SQL Server forward the caller's identity Trusted for delegation on the SQL service account
Cluster and listener names Virtual names clients use to reach the active node Their own SPNs, in addition to each node

MSSQLSvc SPN Formats

Format When the client builds it
MSSQLSvc/host.domain.com Default instance, TCP, port not specified
MSSQLSvc/host.domain.com:1433 Default instance, or any instance on a known TCP port
MSSQLSvc/host.domain.com:InstanceName Named instance, resolved through SQL Server Browser or named pipes
MSSQLSvc/host and the variants above Clients that connect with the NetBIOS short name

Built-In Account vs Domain Account

Many SQL Server installations start with a built-in virtual account such as NT SERVICE\MSSQLSERVER (default instance) or NT SERVICE\MSSQL$<InstanceName> (named instance). That account registers SPNs on the computer account, which works for local or single-hop scenarios but not for distributed Kerberos and delegation.

Scenario Built-in SQL account Recommended action
Single server, no linked servers, NTLM allowed Often works Keep temporarily if the risk is acceptable
Nodinite distributed across servers High risk of Kerberos and SSPI issues Move SQL Server to a domain account or gMSA
Linked servers or double-hop authentication Not suitable for constrained delegation Move SQL Server to a domain account or gMSA
NTLM disabled (Kerberos-only policy) Commonly fails in production topologies Move SQL Server to a domain account or gMSA before go-live

Important

Treat a built-in SQL service account (NT SERVICE\..., LocalSystem, NetworkService) as a transition state. For production Nodinite deployments with linked servers, delegation, BizTalk, or Kerberos-only policies, run SQL Server as a dedicated domain account or gMSA.

What You Need Before Starting

  • The SQL Server service account for every instance Nodinite uses, from SQL Server Configuration Manager
  • Every name clients use to reach each instance: server names, cluster virtual names, Always On listener names, and the ports
  • Domain Admin rights, or an Active Directory administrator who can run setspn
  • The service account requirements in the hub: domain account or gMSA, not marked sensitive, delegation configured where needed
  • Firewall ports open between the servers, see SQL Server Firewall Configuration

Register MSSQLSvc SPNs

The setspn flags (-S, -L, -Q, -X, -D) are described once in the SPN hub. This section shows only the SQL Server SPN values.

Default Instance 

setspn -S MSSQLSvc/sql-prod-01.contoso.com CONTOSO\svc-sql-prod
setspn -S MSSQLSvc/sql-prod-01.contoso.com:1433 CONTOSO\svc-sql-prod

Named Instance 

setspn -S MSSQLSvc/sql-prod-01.contoso.com:BIZTALK CONTOSO\svc-sql-biztalk
setspn -S MSSQLSvc/sql-prod-01.contoso.com:50123 CONTOSO\svc-sql-biztalk

Note

If several named instances on the same server run as different service accounts, register each instance's SPNs on its own account.

Verify the Registration 

setspn -L CONTOSO\svc-sql-prod

Confirm that every host, port, and instance variant appears on the SQL Server service account and on no other account.

SPN Requirements by Scenario

Single Nodinite Instance on One Machine

The Configuration Database and Log Databases are on one SQL Server instance, on the same machine as Nodinite.

  • Built-in SQL account: no SPNs to register manually.
  • Domain account: register the default instance SPNs shown above.

Nodinite with a BizTalk Failover Cluster

Nodinite uses a standalone SQL Server; BizTalk uses a two-node failover cluster with a default and a named instance. You need 7 SPN registrations:

  1. SQL Server name for the default instance with the Nodinite Configuration Database
  2. First node name for the default instance with the BizTalk databases
  3. Second node name for the default instance with the BizTalk databases
  4. Cluster name for the default instance with the BizTalk databases
  5. First node name for the named instance with the BizTalk MessageBox database
  6. Second node name for the named instance with the BizTalk MessageBox database
  7. Cluster name for the named instance with the BizTalk MessageBox database

Tip

Each physical node and the cluster virtual name need SPNs, or Kerberos breaks after a failover.

Always On Availability Groups (AOAG)

With Always On Availability Groups, register SPNs for the listener name and for each node. Even when the instances use a custom port (for example 50123), the listener typically uses port 1433, so you need SPNs for both ports.

setspn -S MSSQLSvc/AOAGListener:1433 CONTOSO\svc-sql-aoag
setspn -S MSSQLSvc/AOAGListener CONTOSO\svc-sql-aoag
setspn -S MSSQLSvc/NodeA:50123 CONTOSO\svc-sql-aoag
setspn -S MSSQLSvc/NodeB:50123 CONTOSO\svc-sql-aoag
setspn -S MSSQLSvc/NodeA:InstanceName CONTOSO\svc-sql-aoag
setspn -S MSSQLSvc/NodeB:InstanceName CONTOSO\svc-sql-aoag

Verify with setspn -L CONTOSO\svc-sql-aoag.

Expected result:

MSSQLSvc/AOAGListener:1433
MSSQLSvc/AOAGListener
MSSQLSvc/NodeA:50123
MSSQLSvc/NodeB:50123
MSSQLSvc/NodeA:InstanceName
MSSQLSvc/NodeB:InstanceName

Important

Every replica must run as the same SQL Server service account for the listener SPN to work on all nodes, and each node needs the SQL logins configured locally. See Always On Availability Groups.

Nodinite uses Linked Servers to reach Log Databases from the Configuration Database. When they're on different SQL Server instances, the linked server query is a Kerberos double hop, and it needs all of these:

  • SPNs - registered for every SQL Server instance in the chain
  • Delegation - Trusted for delegation configured on the Configuration Database SQL Server service account
  • Not sensitive - the service account isn't marked Account is sensitive and cannot be delegated
  • Firewall - ports open between the instances, see SQL Server Firewall Configuration
  • Linked server security - configured as described in Linked Servers

If any of these is missing, the remote instance receives the connection as NT AUTHORITY\ANONYMOUS LOGON.

Move SQL Server from a Built-In Account to a Domain Account

If SQL Server runs as NT SERVICE\MSSQLSERVER or another built-in account:

  1. Create a dedicated Active Directory service account or gMSA for the SQL Server instance.
  2. Grant it the Logon as a Service right on the SQL Server host.
  3. Change the service identity in SQL Server Configuration Manager.
  4. Restart SQL Server in a maintenance window.
  5. Register SPNs for every node, cluster, and listener name on the new account, and remove any stale ones from the computer account or old account.
  6. Validate with the Kerberos Configuration Manager and setspn -L.
  7. Re-test linked servers and every Nodinite authentication path.

Important

When you upgrade or migrate an older Nodinite installation, do this as part of the upgrade. Older installations often break when service identities, SQL Server names, or cluster and listener names change.

Validate with Microsoft Kerberos Configuration Manager

The Microsoft Kerberos Configuration Manager for SQL Server is the recommended tool for finding and fixing SQL Server SPN problems:

  • Detects missing and duplicate SPNs
  • Validates delegation settings
  • Generates scripts that Domain Admins can run to fix issues
  • Runs locally on the SQL Server host, without connection details

SPN Tab

Every Nodinite SQL Server instance that takes part in a linked server hop should be listed in the SPN tab with status OK.

Kerberos Configuration Manager SPN tab listing SQL Server SPNs with status OK
All SQL Server instances in the linked server chain show their SPNs with status OK.

Delegation Tab

The Delegation tab should report no issues.

Kerberos Configuration Manager Delegation tab reporting no issues
No delegation issues reported for the SQL Server service account.

Common SQL Server SPN Issues

Issue Cause Solution
"Cannot generate SSPI context" Missing or duplicate MSSQLSvc SPN Run Kerberos Configuration Manager; register or remove SPNs
NT AUTHORITY\ANONYMOUS LOGON on linked servers Delegation not configured, or SPN missing on one instance Register SPNs for every instance; configure Trusted for delegation
SQL Server connections fall back to NTLM SPN not registered for the name or port the client used Register SPNs for every node, cluster, listener, port, and instance name
Kerberos works on node A, fails on node B SPNs registered for the cluster but not for each node Register SPNs for each physical node
Kerberos fails after an Always On failover Listener SPN missing, or replicas run as different accounts Register the listener SPN; run all replicas as the same account
setspn -S returns 0x21c7/8647 SPN already owned by another account See the duplicate SPN section in the SPN hub
SPN registration errors in the SQL Server error log Service account can't register its own SPNs A Domain Admin registers them, see Microsoft's SPN registration guide

Next Steps