Get-sqmSpnReport Security

Checks expected MSSQLSvc SPNs for all SQL Server instances on one or more computers. Detects missing and unexpected SPNs, resolves the service account type (Domain / Computer / NoNetwork), reads the SQL port from the registry, and optionally checks AlwaysOn listener SPNs. Produces ready-to-use setspn.exe commands for missing entries.

Execution Flow

Synopsis

Determines all SQL Server services on each target computer via Win32_Service (CIM), resolves the service account type, reads the SQL TCP port from the registry, and derives the four expected MSSQLSvc SPNs per instance. Existing SPNs are read via setspn.exe -L. Missing SPNs include a ready-to-use setspn -S command. Unexpected SPNs (stale after port changes) include a setspn -D command. The AD module is not required.

Requires setspn.exe in the system path (standard on domain-joined Windows). No dbatools required for the main SPN check; dbatools is used optionally for AlwaysOn listener SPN detection. Local admin rights on the target computer required for WMI/CIM queries.

Syntax

Get-sqmSpnReport
    [-ComputerName <String[]>]    # pipeline; aliases: Computer, Server
    [-InstanceFilter <String>]   # wildcard, default '*'
    [-OutputPath <String>]
    [-ContinueOnError]
    [-EnableException]

Parameters

Parameter	Type	Default	Description
-ComputerName	String[]	$env:COMPUTERNAME	Target computer(s). Pipeline-capable. Aliases: Computer, Server.
-InstanceFilter	String	*	Wildcard filter on instance name (e.g. 'MSSQLSERVER' for default only).
-OutputPath	String	Get-sqmConfig 'OutputPath'	Directory for TXT and CSV report files.
-ContinueOnError	Switch	false	Continue with the next instance on error.
-EnableException	Switch	false	Throw terminating exceptions.

SPN Status Values

Status	Meaning
OK	SPN exists and matches expected value.
Missing	Expected SPN not found. SetSpnCommand contains the fix.
Unexpected	SPN exists but is not in the expected list (stale after port change).
NoNetwork	LOCAL SERVICE account — no network identity, Kerberos not possible.

Examples

Example 1 — Check all SQL instances on local computer

Get-sqmSpnReport

Example 2 — Only the default instance on SQL01

Get-sqmSpnReport -ComputerName "SQL01" -InstanceFilter "MSSQLSERVER"

Example 3 — Show missing SPNs only with setspn commands

$r = Get-sqmSpnReport -ComputerName "SQL01"
$r.DetailRows | Where-Object Status -eq "Missing" | Select-Object Spn, SetSpnCommand

⬆ Index

All Commands