Private/Get-EndpointCertRow.ps1
|
# ============================================================================= # Script : Private/Get-EndpointCertRow.ps1 # Author : Keith Ramsey # Created : 2026-09-07 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-07 Keith Ramsey Initial STUB (D1 skeleton). # 2026-09-07 Keith Ramsey Implemented: SELECT the endpoint authentication # certificates from sys.database_mirroring_endpoints # (also serves Always On AG) and # sys.service_broker_endpoints, joined to # master.sys.certificates on certificate_id (read-only, # via the shared query seam). One Found row per # certificate-secured endpoint; Skipped when none; # never-throw -> Failed. # ============================================================================= # D1 surface reader (GR-001, DR-027): endpoint certificates (DBM / AG / Broker). # ============================================================================= function Get-EndpointCertRow { <# .SYNOPSIS Reads the endpoint (mirroring / AG / Service Broker) certificate surface (D1). .DESCRIPTION Runs a read-only SELECT (via Invoke-SqlCertInventoryQuery) that unions the database-mirroring endpoints (which also carry Always On availability-group traffic) and the Service Broker endpoints, joining each certificate-secured endpoint to master.sys.certificates on certificate_id, and emits one SqlCert.Inventory row per certificate-secured endpoint. The certificate thumbprint comes back as varbinary and is normalised to the same 40-char hex string the store surfaces use. Skipped when no endpoint is certificate-secured; Failed (with the reason, including "no query provider") on any read error. Never throws. .PARAMETER SqlInstance The instance to query. .PARAMETER Node The host computer (recorded on the row). Default local machine. .PARAMETER ThresholdDays Expiry threshold passed through to the row factory. .PARAMETER Credential Credential for the SQL connection. .OUTPUTS SqlCert.Inventory .NOTES Steps: 1. Run the read-only endpoint SELECT (DBM + Service Broker endpoints -> master.sys.certificates) via the shared query seam. 2. No certificate-secured endpoint -> one Skipped row. 3. One Found row per certificate-secured endpoint: normalise the varbinary thumbprint to hex, carry subject/expiry, name the endpoint and its type. 4. Any read error (including no query provider) -> one Failed row. Never throws. #> [CmdletBinding()] [OutputType('SqlCert.Inventory')] param( [string] $SqlInstance = '', [string] $Node = $env:COMPUTERNAME, [int] $ThresholdDays = 30, [PSCredential] $Credential ) $rowArgs = @{ Surface = 'Endpoint'; SqlInstance = $SqlInstance; Node = $Node; ThresholdDays = $ThresholdDays } $query = @' SELECT dme.name AS EndpointName, 'DATABASE_MIRRORING' AS EndpointType, c.name AS CertName, c.thumbprint AS Thumbprint, c.subject AS Subject, c.expiry_date AS NotAfter FROM sys.database_mirroring_endpoints AS dme INNER JOIN master.sys.certificates AS c ON c.certificate_id = dme.certificate_id WHERE dme.certificate_id IS NOT NULL UNION ALL SELECT sbe.name, 'SERVICE_BROKER', c.name, c.thumbprint, c.subject, c.expiry_date FROM sys.service_broker_endpoints AS sbe INNER JOIN master.sys.certificates AS c ON c.certificate_id = sbe.certificate_id WHERE sbe.certificate_id IS NOT NULL; '@ try { # 1. Read-only query via the shared seam. $qArgs = @{ SqlInstance = $SqlInstance; Query = $query; Database = 'master' } if ($Credential) { $qArgs.Credential = $Credential } $rows = @(Invoke-SqlCertInventoryQuery @qArgs) # 2. No certificate-secured endpoint. if ($rows.Count -eq 0) { return New-SqlCertInventoryRow @rowArgs -Status Skipped ` -Detail "No certificate-secured endpoint (mirroring / AG / Service Broker) on '$SqlInstance'." } # 3. One Found row per certificate-secured endpoint. foreach ($r in $rows) { $tp = if ($r.Thumbprint -is [byte[]]) { ([System.BitConverter]::ToString([byte[]]$r.Thumbprint) -replace '-', '') } else { [string]$r.Thumbprint } $na = if ($r.NotAfter -is [datetime]) { [datetime]$r.NotAfter } else { $null } $typeLabel = if ($r.EndpointType -eq 'DATABASE_MIRRORING') { 'database mirroring / Always On' } else { 'Service Broker' } New-SqlCertInventoryRow @rowArgs -Thumbprint $tp -Subject ([string]$r.Subject) -NotAfter $na -Status Found ` -Detail "Endpoint '$($r.EndpointName)' ($typeLabel) authentication certificate '$($r.CertName)'." } } catch { # 4. Never-throw. New-SqlCertInventoryRow @rowArgs -Status Failed ` -Detail "Endpoint read failed on '$SqlInstance': $($_.Exception.Message)" } } |