Private/Get-BackupCertRow.ps1
|
# ============================================================================= # Script : Private/Get-BackupCertRow.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 DISTINCT the certificates that # msdb.dbo.backupset references as backup encryptors, # joined to master.sys.certificates (read-only, via the # shared Invoke-SqlCertInventoryQuery seam). One Found # row per distinct backup-encryption cert; Skipped when # none; never-throw -> Failed row. # ============================================================================= # D1 surface reader (GR-001, DR-027): backup-encryption certificate. # ============================================================================= function Get-BackupCertRow { <# .SYNOPSIS Reads the backup-encryption certificate surface for the inventory (D1). .DESCRIPTION Runs a read-only SELECT DISTINCT (via Invoke-SqlCertInventoryQuery) over msdb.dbo.backupset's encryptor columns joined to master.sys.certificates, and emits one SqlCert.Inventory row per distinct certificate that has encrypted a backup. The certificate thumbprint comes back as varbinary and is normalised to the same 40-char hex string the store surfaces use. Skipped when no encrypted backup references a certificate; Failed (with the reason, including "no query provider") on any read error. Never throws. Backup history is retained only as long as msdb keeps it, so an empty result means "none in retained history", not "never". .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 backup-encryption SELECT (msdb.dbo.backupset encryptor -> master.sys.certificates) via the shared query seam. 2. No encrypted backup references a certificate -> one Skipped row. 3. One Found row per distinct certificate: normalise the varbinary thumbprint to hex, carry subject/expiry. 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 = 'Backup'; SqlInstance = $SqlInstance; Node = $Node; ThresholdDays = $ThresholdDays } $query = @' SELECT DISTINCT c.name AS CertName, c.thumbprint AS Thumbprint, c.subject AS Subject, c.expiry_date AS NotAfter FROM msdb.dbo.backupset AS bs INNER JOIN master.sys.certificates AS c ON c.thumbprint = bs.encryptor_thumbprint WHERE bs.encryptor_type LIKE 'CERTIFICATE%' -- 'CERTIFICATE' or SQL 2025's 'CERTIFICATE_OAEP_256' AND bs.encryptor_thumbprint 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 encrypted backup references a certificate. if ($rows.Count -eq 0) { return New-SqlCertInventoryRow @rowArgs -Status Skipped ` -Detail "No encrypted backup in msdb history on '$SqlInstance' references a certificate." } # 3. One Found row per distinct backup-encryption certificate. 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 } New-SqlCertInventoryRow @rowArgs -Thumbprint $tp -Subject ([string]$r.Subject) -NotAfter $na -Status Found ` -Detail "Backup-encryption certificate '$($r.CertName)'." } } catch { # 4. Never-throw. New-SqlCertInventoryRow @rowArgs -Status Failed ` -Detail "Backup-encryption read failed on '$SqlInstance': $($_.Exception.Message)" } } |