Commands / Get-sqmSpnReport
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.
-NoOpenswitch$falseDo not automatically open the generated report after creation.
-SqlCredentialPSCredential, Optional SQL credential used ONLY for the AlwaysOn-listener SPN check, which queries the instance (sys.availability_group_listeners) via dbatools' Invoke-DbaQuery. The core SPN report (service accounts, expected/existing MSSQLSvc SPNs) is WMI/CIM/registry- and setspn-based and needs no SQL connection. If omitted, the listener query connects with the current Windows identity; if dbatools is not available, the listener check is skipped and the rest of the report is produced normally.

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

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