Public/Get-SqlCertInventory.ps1
|
# ============================================================================= # Script : Public/Get-SqlCertInventory.ps1 # Author : Keith Ramsey # Created : 2026-09-07 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-07 Keith Ramsey Initial: D1 estate-wide read-only cert inventory # composer (GR-001, DR-026/027). Never-throw: each # surface reader is wrapped so a throw becomes one # Failed row, not an exception. Free / read-only. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md) # ----------------------------------------------------------------------------- # Must : one command, one row per cert (or per unreadable surface), never-throw, # read-only, free (DR-026); composes six surface readers (DR-027); # expiry reported via New-SqlCertInventoryRow, Unknown-not-false (DR-028). # ============================================================================= function Get-SqlCertInventory { <# .SYNOPSIS Estate-wide, read-only inventory of SQL Server certificate surfaces with expiry. .DESCRIPTION Enumerates the certificates on every cert surface present on the target -- engine connection binding, Reporting Services / PBIRS, TDE, backup-encryption, endpoints (mirroring / AG / Service Broker), and cell-level -- and returns one SqlCert.Inventory row per certificate, plus a Skipped/Failed row for any surface that is absent or unreadable. Read-only and free: it changes nothing and is safe on any server. Never throws -- a reader that fails yields a Failed row with the reason. Expiry is reported (DaysToExpiry / Expiring, Unknown when undated), not acted on. .PARAMETER SqlInstance The instance(s) to inventory. Default 'localhost'. For a named instance pass 'HOST\INSTANCE'; the host is derived for the host-level surfaces. .PARAMETER ThresholdDays Days-to-expiry at or below which a row is flagged Expiring. Default 30. .PARAMETER Credential Credential for remote surface reads (registry / WMI / SQL) where Kerberos pass-through does not apply. Threaded to each reader. .OUTPUTS SqlCert.Inventory (one per certificate; Skipped/Failed rows carry the reason). .EXAMPLE Get-SqlCertInventory -SqlInstance sql01 | Format-Table Surface, Subject, NotAfter, DaysToExpiry, Expiring -AutoSize Read-only scan of every certificate surface on sql01 -- engine binding, Reporting Services, TDE, backup encryption, endpoints and cell-level -- as one table. Safe to run against any server: it changes nothing. .EXAMPLE Get-SqlCertInventory -SqlInstance sql01, sql02, sql03 -ThresholdDays 45 | Where-Object Expiring | Sort-Object DaysToExpiry The renewal-planning view: every certificate across three instances that expires within 45 days, soonest first -- the shortlist to schedule for renewal. Rows whose expiry could not be read are excluded here (Expiring is Unknown, not $true); the next example surfaces those. .EXAMPLE Get-SqlCertInventory -SqlInstance sql01 | Where-Object { $_.Status -ne 'Found' -or $null -eq $_.Expiring } Surfaces the gaps: rows that were Skipped or Failed, plus any Found certificate whose NotAfter could not be read (DaysToExpiry and Expiring are Unknown). These never masquerade as "fine" -- investigate them rather than trusting a silent pass. .EXAMPLE Get-SqlCertInventory -SqlInstance sql01, sql02 | Export-Csv -NoTypeInformation -Path .\cert-inventory.csv Captures the full estate inventory -- Found, Skipped and Failed rows, each provenance-stamped (ModuleVersion / CommitSha / RunId / Timestamp) -- to a CSV to attach to a change ticket or an audit record. .EXAMPLE Get-SqlCertInventory -SqlInstance sql01 -Credential (Get-Credential) | Group-Object Node, Surface Uses an explicit credential for the remote registry / WMI / SQL reads (when Kerberos pass-through does not apply) and groups the result by host and surface for a per-server rollup. .NOTES Steps: 1. Map each surface to its private reader (DR-027). 2. For each instance, derive the host node for host-level surfaces. 3. Call each reader, wrapped so a throw becomes one Failed row (never-throw, DR-026). 4. Aggregate and return all SqlCert.Inventory rows. #> [CmdletBinding()] [OutputType('SqlCert.Inventory')] param( [string[]] $SqlInstance = @('localhost'), [int] $ThresholdDays = 30, [PSCredential] $Credential ) # 1. Surface -> reader (DR-027). Ordered so output is predictable. $readerMap = [ordered]@{ Engine = 'Get-SqlEngineCertRow' ReportingServices = 'Get-RsCertRow' Tde = 'Get-TdeCertRow' Backup = 'Get-BackupCertRow' Endpoint = 'Get-EndpointCertRow' Cell = 'Get-CellCertRow' } $results = [System.Collections.Generic.List[object]]::new() foreach ($inst in $SqlInstance) { # 2. Host node for the host-level surfaces (engine registry, RS WMI). $node = if ($inst -in @('localhost', '.', '', $env:COMPUTERNAME)) { $env:COMPUTERNAME } elseif ($inst -like '*\*') { $inst.Split('\')[0] } else { $inst } foreach ($surface in $readerMap.Keys) { $reader = $readerMap[$surface] try { # 3. Each reader emits SqlCert.Inventory rows; a throw here is caught below. $splat = @{ SqlInstance = $inst; Node = $node; ThresholdDays = $ThresholdDays } if ($Credential) { $splat.Credential = $Credential } $rows = & $reader @splat foreach ($r in $rows) { if ($r) { $results.Add($r) } } } catch { $results.Add((New-SqlCertInventoryRow -Surface $surface -SqlInstance $inst -Node $node ` -Status Failed -Detail "The $surface reader failed: $($_.Exception.Message)")) } } } # 4. All rows. $results.ToArray() } |