Private/Get-CellCertRow.ps1
|
# ============================================================================= # Script : Private/Get-CellCertRow.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: enumerate accessible online user # databases, then read each database's sys.certificates # (excluding the ##MS_* system certificates), read-only # via the shared query seam. One Found row per user-DB # certificate; Skipped when none; never-throw -> Failed. # ============================================================================= # D1 surface reader (GR-001, DR-027): cell-level / column-encryption certificates. # ============================================================================= function Get-CellCertRow { <# .SYNOPSIS Reads the cell-level (column-encryption) certificate surface for the inventory (D1). .DESCRIPTION Cell-level (column) encryption certificates live in each user database's sys.certificates, not in master, so this reader is two-phase: it first lists the accessible online user databases (database_id > 4, state ONLINE, HAS_DBACCESS), then reads sys.certificates in each -- excluding the built-in ##MS_* system certificates -- and emits one SqlCert.Inventory row per user-database certificate. Both phases go through the shared read-only query seam. Database names are bracket-escaped before they are used to qualify the per-database read. Skipped when no accessible user database carries a certificate; Failed (with the reason) 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. List accessible online user databases (read-only) via the shared query seam. 2. For each database, read its sys.certificates (excluding ##MS_* system certs), qualifying the read with the bracket-escaped database name. 3. One Found row per user-database certificate: normalise the varbinary thumbprint to hex, carry subject/expiry, name the database and certificate. 4. No user-database certificate -> one Skipped row. Any read error -> one Failed row. Never throws. #> [CmdletBinding()] [OutputType('SqlCert.Inventory')] param( [string] $SqlInstance = '', [string] $Node = $env:COMPUTERNAME, [int] $ThresholdDays = 30, [PSCredential] $Credential ) $rowArgs = @{ Surface = 'Cell'; SqlInstance = $SqlInstance; Node = $Node; ThresholdDays = $ThresholdDays } $credSplat = @{} if ($Credential) { $credSplat.Credential = $Credential } $dbListQuery = 'SELECT name FROM sys.databases WHERE state = 0 AND database_id > 4 AND HAS_DBACCESS(name) = 1;' try { # 1. Accessible online user databases. $dbs = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Query $dbListQuery -Database 'master' @credSplat) # 2. Per-database sys.certificates (exclude the built-in ##MS_* system certs). $found = [System.Collections.Generic.List[object]]::new() foreach ($db in $dbs) { $dbName = [string]$db.name $dbEsc = $dbName -replace '\]', ']]' $certQuery = "SELECT '$($dbName -replace "'", "''")' AS DatabaseName, name AS CertName, thumbprint AS Thumbprint, subject AS Subject, expiry_date AS NotAfter FROM [$dbEsc].sys.certificates WHERE name NOT LIKE '##MS%';" $certs = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Query $certQuery -Database 'master' @credSplat) foreach ($c in $certs) { $found.Add($c) } } # 3/4. Emit rows. if ($found.Count -eq 0) { return New-SqlCertInventoryRow @rowArgs -Status Skipped ` -Detail "No cell-level certificate in any accessible user database on '$SqlInstance'." } foreach ($r in $found) { $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 "Cell-level certificate '$($r.CertName)' in database '$($r.DatabaseName)'." } } catch { # Never-throw. New-SqlCertInventoryRow @rowArgs -Status Failed ` -Detail "Cell-level read failed on '$SqlInstance': $($_.Exception.Message)" } } |