Public/Test-SqlEndpointCertAuth.ps1
|
# ============================================================================= # Script : Public/Test-SqlEndpointCertAuth.ps1 # Author : Keith Ramsey # Created : 2026-09-08 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-08 Keith Ramsey Initial (C1, GR-006, DR-038): read-only status of a # database-mirroring / Service Broker endpoint's # certificate authentication. Reuses the read seam; # never-throw. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md DR-038) # ----------------------------------------------------------------------------- # Must : read-only (no ShouldProcess); report endpoint presence/state, whether # it authenticates by certificate, the certificate + expiry; endpoint # view chosen from a ValidateSet (no injection); one never-throw # SqlCert.Result; absent endpoint -> Success (not an error). # ============================================================================= function Test-SqlEndpointCertAuth { <# .SYNOPSIS Reports certificate authentication on a mirroring / Service Broker endpoint (C1, read-only). .DESCRIPTION Reads -- and changes -- nothing. Reports whether the database-mirroring (also used by Always On availability groups) or Service Broker endpoint exists, its state, whether it authenticates by certificate, and which certificate (with expiry). Use it to see whether a replica's data endpoint is set up for certificate auth before -- or after -- wiring the cross-partner trust with the other C1 commands. Never throws. .PARAMETER SqlInstance The instance to inspect. Default 'localhost'. .PARAMETER EndpointType Which endpoint to inspect: DatabaseMirroring (mirroring / AG, the default) or ServiceBroker. .PARAMETER Credential SQL or Windows credential for the connection. .OUTPUTS SqlCert.Result Data keys: EndpointType, EndpointExists, EndpointName, State, IsCertAuthenticated, AuthMethod, CertName, Thumbprint, NotAfter. .EXAMPLE Test-SqlEndpointCertAuth -SqlInstance node1 Reports whether node1's database-mirroring/AG endpoint authenticates by certificate and which certificate it uses. .EXAMPLE Test-SqlEndpointCertAuth -SqlInstance node1 -EndpointType ServiceBroker Same check for the Service Broker endpoint. .NOTES Steps: 1. Pick the endpoint catalog view from -EndpointType (ValidateSet -> no injection). 2. Read (via the shared read seam) the endpoint joined to sys.endpoints and, LEFT JOIN, master.sys.certificates. 3. No endpoint -> Success reporting absence; otherwise report state + certificate-auth + certificate/expiry (varbinary thumbprint -> hex). 4. Any read error -> Failed. Never throws. #> [CmdletBinding()] [OutputType('SqlCert.Result')] param( [string] $SqlInstance = 'localhost', [ValidateSet('DatabaseMirroring', 'ServiceBroker')] [string] $EndpointType = 'DatabaseMirroring', [PSCredential] $Credential ) $credSplat = @{} if ($Credential) { $credSplat.Credential = $Credential } $hexOf = { param($v) if ($v -is [byte[]]) { ([System.BitConverter]::ToString([byte[]]$v) -replace '-', '') } else { [string]$v } } # 1. Endpoint view from the ValidateSet (safe -- not caller free-text). $view = if ($EndpointType -eq 'DatabaseMirroring') { 'sys.database_mirroring_endpoints' } else { 'sys.service_broker_endpoints' } $typeLabel = if ($EndpointType -eq 'DatabaseMirroring') { 'DATABASE_MIRRORING' } else { 'SERVICE_BROKER' } $query = @" SELECT e.name AS EndpointName, e.state_desc AS State, ep.connection_auth_desc AS AuthMethod, c.name AS CertName, c.thumbprint AS Thumbprint, c.expiry_date AS NotAfter FROM $view AS ep JOIN sys.endpoints AS e ON e.endpoint_id = ep.endpoint_id LEFT JOIN master.sys.certificates AS c ON c.certificate_id = ep.certificate_id; "@ $data = [pscustomobject]@{ EndpointType = $EndpointType EndpointExists = $false EndpointName = $null State = $null IsCertAuthenticated = $false AuthMethod = $null CertName = $null Thumbprint = $null NotAfter = $null } try { # 2. Read. $rows = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' -Query $query @credSplat) # 3. Report. if ($rows.Count -eq 0) { return New-SqlCertResult -Stage Verify -Status Success ` -Detail "No $typeLabel endpoint on '$SqlInstance'." -Data $data } $r = $rows[0] $auth = [string]$r.AuthMethod $isCert = ($auth -match 'CERTIFICATE') $data.EndpointExists = $true $data.EndpointName = [string]$r.EndpointName $data.State = [string]$r.State $data.AuthMethod = $auth $data.IsCertAuthenticated = $isCert if ($isCert) { $data.CertName = [string]$r.CertName $data.Thumbprint = & $hexOf $r.Thumbprint $data.NotAfter = if ($r.NotAfter -is [datetime]) { [datetime]$r.NotAfter } else { $null } } $detail = if ($isCert) { "$typeLabel endpoint '$($data.EndpointName)' ($($data.State)) authenticates by certificate '$($data.CertName)' (thumbprint $($data.Thumbprint))." } else { "$typeLabel endpoint '$($data.EndpointName)' ($($data.State)) does NOT authenticate by certificate (auth: $auth). Use Set-SqlEndpointCertAuthentication to switch it." } New-SqlCertResult -Stage Verify -Status Success -Detail $detail -Data $data } catch { New-SqlCertResult -Stage Verify -Status Failed ` -Detail "Test-SqlEndpointCertAuth could not read the $typeLabel endpoint on '$SqlInstance': $($_.Exception.Message)" -Data $data } } |