sqmSQLTool › Commands › Get-sqmTlsStatus

Get-sqmTlsStatus Security

Audits TLS/SSL configuration for all SQL Server instances on one or more computers: certificate thumbprint, ForceEncryption, cert expiry/trust/SAN, and SCHANNEL TLS 1.0–1.3 protocol state. Works locally and remotely via WinRM. No dbatools required.

Execution Flow

START foreach $computer in $ComputerName [pipeline] $remoteCollectBlock (local or Invoke-Command via WinRM) Read SQL instance names from HKLM:\...\Instance Names\SQL SCHANNEL registry: TLS 1.0/1.1/1.2/1.3 Server key → Enabled/Disabled/NotConfigured Per instance: SuperSocketNetLib → CertThumbprint, ForceEncryption Return $collected[] (hashtable per instance) foreach $entry in $rawData — cert lookup (local or Invoke-Command) Cert:\LocalMachine\My\<Thumbprint> → Found/Subject/Expiry/SAN X509Chain.Build() (RevocationMode=NoCheck) → ChainValid/ChainStatus CertDaysLeft = (Expiry - Now).TotalDays HasPrivateKey, SANEntries (Subject Alternative Name extension) Status: Critical / Warning / OK per instance Add to $allResults[] Write CSV + TXT (summary + per-instance detail) DONE

Synopsis

Reads SQL Server TLS/SSL configuration entirely from the Windows Registry and certificate store — no SQL connection, no dbatools needed. For each instance, the certificate thumbprint bound in SuperSocketNetLib is resolved against Cert:\LocalMachine\My, chain-validated, and its expiry and SAN entries are recorded. SCHANNEL TLS protocol state (TLS 1.0–1.3) is read per computer. Remote execution uses Invoke-Command (WinRM).

No dbatools required. Remote access via WinRM (Invoke-Command). Use -Credential for non-default remote credentials. Certificate chain validation uses RevocationMode=NoCheck to avoid network dependency.

Status Logic

StatusCondition
CriticalCert thumbprint set but not found in LocalMachine\My, OR cert chain not trusted, OR cert expired.
WarningCert expires within WarnDaysBeforeExpiry days, OR ForceEncryption=0, OR TLS 1.0/1.1 Enabled, OR no cert bound (self-signed auto-cert).
OKAll checks passed.

Syntax

Get-sqmTlsStatus
    [-ComputerName <String[]>]    # pipeline
    [-Credential <PSCredential>]
    [-OutputPath <String>]
    [-WarnDaysBeforeExpiry <Int>]
    [-NoOpen]

Parameters

ParameterTypeDefaultDescription
-ComputerNameString[]$env:COMPUTERNAMETarget computer(s). Pipeline-capable. For remote: WinRM must be enabled.
-CredentialPSCredential—Credential for Invoke-Command on remote computers.
-OutputPathStringC:\System\WinSrvLog\MSSQLDirectory for CSV and TXT report files.
-WarnDaysBeforeExpiryInt60Certificate expiry warning threshold in days.
-NoOpenSwitchfalseSuppress automatic report opening.

Return Value

PropertyDescription
ComputerNameSource computer.
InstanceNameSQL Server instance name (e.g. MSSQLSERVER or NAMED).
ForceEncryption0 = optional, 1 = required.
CertThumbprintThumbprint from SuperSocketNetLib. Empty = auto self-signed.
CertSubjectCertificate Subject CN.
CertExpiry / CertDaysLeftExpiry date and days remaining.
CertTrusted / CertInStoreChain validation result; whether cert was found in store.
SANEntriesSubject Alternative Name extension text.
HasPrivateKeyBoolean — true if private key is present.
TLS10 / TLS11 / TLS12 / TLS13Enabled / Disabled / NotConfigured per SCHANNEL Server key.
Status / StatusDetailOK / Warning / Critical with pipe-separated detail text.

Examples

# Audit local machine
Get-sqmTlsStatus

# Multiple servers, save to custom path
Get-sqmTlsStatus -ComputerName "SQL01","SQL02" -OutputPath "D:\Reports"

# Filter non-OK results, remote with credentials
$cred = Get-Credential
Get-sqmTlsStatus -ComputerName "SQL01" -Credential $cred | Where-Object Status -ne "OK"