Private/Get-TdeCertRow.ps1
|
# ============================================================================= # Script : Private/Get-TdeCertRow.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 sys.dm_database_encryption_keys # JOIN master.sys.certificates on the DEK's # encryptor_thumbprint (read-only, via the shared # Invoke-SqlCertInventoryQuery seam). One Found row per # TDE-protected database; Skipped when none; never-throw # -> Failed row (incl. no query provider). # ============================================================================= # D1 surface reader (GR-001, DR-027): TDE certificate protecting the DEK. # ============================================================================= function Get-TdeCertRow { <# .SYNOPSIS Reads the TDE certificate surface for the inventory (D1). .DESCRIPTION Runs a read-only SELECT (via Invoke-SqlCertInventoryQuery) that joins sys.dm_database_encryption_keys to master.sys.certificates on the database encryption key's protecting-certificate thumbprint, and emits one SqlCert.Inventory row per TDE-protected database. The certificate thumbprint comes back as varbinary and is normalised to the same 40-char hex string the store surfaces use. Skipped when no database is protected by a certificate; 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 TDE SELECT (DEK encryptor_thumbprint -> master.sys.certificates) via the shared query seam. 2. No protected database -> one Skipped row. 3. One Found row per protected database: 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 = 'Tde'; SqlInstance = $SqlInstance; Node = $Node; ThresholdDays = $ThresholdDays } $query = @' SELECT DB_NAME(dek.database_id) AS DatabaseName, c.name AS CertName, c.thumbprint AS Thumbprint, c.subject AS Subject, c.expiry_date AS NotAfter FROM sys.dm_database_encryption_keys AS dek INNER JOIN master.sys.certificates AS c ON c.thumbprint = dek.encryptor_thumbprint WHERE dek.encryptor_type LIKE 'CERTIFICATE%'; -- 'CERTIFICATE' or SQL 2025's 'CERTIFICATE_OAEP_256' '@ 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 TDE-protected database. if ($rows.Count -eq 0) { return New-SqlCertInventoryRow @rowArgs -Status Skipped ` -Detail "No database on '$SqlInstance' is protected by a TDE certificate." } # 3. One Found row per protected database. 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 "TDE encryptor for database '$($r.DatabaseName)' (certificate '$($r.CertName)')." } } catch { # 4. Never-throw. New-SqlCertInventoryRow @rowArgs -Status Failed ` -Detail "TDE read failed on '$SqlInstance': $($_.Exception.Message)" } } |