LLI Wiki — My Digital Knowledge Base

SQL Server 2019 cluster: install checklist, Kerberos SPNs and AD permissions

What to check before and after installing a SQL Server 2019 cluster, setspn commands and troubleshooting cluster permissions in Active Directory.

This checklist summarises what to verify before and after installing a high-availability SQL Server 2019 cluster (failover cluster instance and/or Always On availability group), with the setspn commands useful for Kerberos and a classic troubleshooting case for cluster permissions in Active Directory.

Before installation

Area Item to check
General SQL Server version (here 2019 Standard), number of replicas (3), replica location (same site, different virtualisation hosts), estimated number/size/activity of databases
Active Directory Delegation to the administrator to create/move computer objects in the dedicated OUs (SQL servers, service accounts, user accounts); deleting a SQL computer object
GPO (user rights) Perform volume maintenance tasks, Impersonate a client after authentication, Lock pages in memory, Log on as a service, SeSecurityPrivilege
Network Public network and private network (cluster heartbeat, dedicated VLAN with fixed IP), one IP dedicated to the cluster, one IP per availability group listener, dedicated listener port (agreed range), endpoint port (5022 by default), listener naming convention, DNS alias to be validated
Service accounts SQL engine, SQL Agent, SSIS and SSRS service accounts, a sysadmin AD account, a failover cluster account
Servers 8 vCPU, 64 GB RAM, 2 virtual SCSI adapters, 2 network adapters, same volumes and folder structure on every node, .NET Framework, required Windows hotfixes, each node added to the cluster, SPNs registered (see below), DTC configured
Cluster Cluster name, quorum (file share), dedicated heartbeat network to improve stability
Storage OS on C: (GPT, 100 GB); data G: (GPT, 64 KB allocation unit, 200 GB); system databases S: (64 KB, 40 GB); tempdb T: (GPT, SSD preferred, 40 GB); logs L: (64 KB, 60 GB); confirm with the system team that volumes can be extended online
Backup Third-party tool (Veeam-type) and native backup to a dedicated local drive
Instance Trace flag 3226 (suppresses successful-backup messages in the error log), named instance, a Latin1_General_CI_AS-type collation to confirm, tempdb data files = number of CPUs, monitoring
Naming Define the naming convention for databases and availability groups (cluster resource name)

For a separate SSIS server: 4 vCPU, 8 GB RAM, two 80 GB volumes; install only the local database engine for SSISDB.

Other server-side checks:

Post-installation

SQL Server Agent Alert System page with fail-safe operator

SPNs for Kerberos

If Kerberos is used, the SQL engine service account must hold the server's SPNs. Remove old SPNs first (-d), then add the new ones (-a, or -S which checks for duplicates). Use the port for a default instance and the instance name for a named instance.

# Remove existing SPNs
setspn -d MSSQLSvc/sqlserver01.contoso.com:1433 CONTOSO\svc_sql
setspn -d MSSQLSvc/sqlserver01.contoso.com:PROD CONTOSO\svc_sql
setspn -d MSSQLSvc/sqlserver01:1433 CONTOSO\svc_sql
setspn -d MSSQLSvc/sqlserver01:PROD CONTOSO\svc_sql

# Add SPNs
setspn -a MSSQLSvc/sqlserver01.contoso.com:1433 CONTOSO\svc_sql
setspn -a MSSQLSvc/sqlserver01.contoso.com:PROD CONTOSO\svc_sql
setspn -a MSSQLSvc/sqlserver01:1433 CONTOSO\svc_sql
setspn -a MSSQLSvc/sqlserver01:PROD CONTOSO\svc_sql

The same pattern applies to a DNS alias or a listener name (MSSQLSvc/alias.contoso.com:1433). For a web service (IIS):

setspn -S HTTP/app.contoso.com CONTOSO\app_pool_user
setspn -S HTTP/app.contoso.com:8091 CONTOSO\app_pool_user
setspn app.contoso.com     # list the SPNs of an account or host

To list the aliases (CNAME records) of a DNS zone, to decide which SPNs to register:

Get-WmiObject -Namespace 'root\MicrosoftDNS' -Class MicrosoftDNS_CNAMEType `
  -Filter "ContainerName = 'contoso.com'" -ComputerName dnsserver01 |
  Select-Object @{N='Alias'; E={$_.Ownername}}, Primaryname

Troubleshooting: the cluster identity cannot update computer objects

Typical message: password reset may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain. (Microsoft Learn discussion)

  1. Open Active Directory Users and Computers, then View > Advanced Features.
  2. Open the properties of the computer object the Cluster service must use, Security tab > Add.
  3. Add the Cluster service account (or a group it belongs to) and grant: Reset Password, Validated Write to DNS Host Name, Validated Write to Service Principal Name.

Notes

Mastodon LLIMastodon SocialFosstodon