sqmSQLTool › Commands › Get-sqmSpnReport

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

START Verify setspn.exe in system path foreach $computer in $ComputerName [pipeline; aliases: Computer, Server] DNS.GetHostEntry → FQDN; CIM Win32_Service MSSQLSERVER/MSSQL$* foreach $svc in $sqlServices (filtered by -InstanceFilter) _ResolveSpnAccount: Domain / Computer (NT SERVICE\*, SYSTEM) / NoNetwork Computer account: domain via Win32_NTDomain → HOSTNAME$ UPN format (user@domain): LDAP DirectorySearcher → SAM SQL port: HKLM\...\SuperSocketNetLib\Tcp\IPAll (TcpPort / TcpDynamicPorts) Expected SPNs: MSSQLSvc/host:port, /fqdn:port, /host, /fqdn (default) or /host:inst, /fqdn:inst (named) _GetExistingSpns: setspn.exe -L <account> → existing MSSQLSvc entries Status: OK / Missing (+ setspn -S command) / Unexpected Optional: AG listener SPNs via sys.availability_group_listeners (dbatools) ShouldProcess → Write TXT (sections) + CSV per instance Return $allResults[] DONE

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

ParameterTypeDefaultDescription
-ComputerNameString[]$env:COMPUTERNAMETarget computer(s). Pipeline-capable. Aliases: Computer, Server.
-InstanceFilterString*Wildcard filter on instance name (e.g. 'MSSQLSERVER' for default only).
-OutputPathStringGet-sqmConfig 'OutputPath'Directory for TXT and CSV report files.
-ContinueOnErrorSwitchfalseContinue with the next instance on error.
-EnableExceptionSwitchfalseThrow terminating exceptions.

SPN Status Values

StatusMeaning
OKSPN exists and matches expected value.
MissingExpected SPN not found. SetSpnCommand contains the fix.
UnexpectedSPN exists but is not in the expected list (stale after port change).
NoNetworkLOCAL SERVICE account — no network identity, Kerberos not possible.

Examples

# Check all SQL instances on local computer
Get-sqmSpnReport

# Only the default instance on SQL01
Get-sqmSpnReport -ComputerName "SQL01" -InstanceFilter "MSSQLSERVER"

# Show missing SPNs only with setspn commands
$r = Get-sqmSpnReport -ComputerName "SQL01"
$r.DetailRows | Where-Object Status -eq "Missing" | Select-Object Spn, SetSpnCommand