Public/Test-SqlTdeConfiguration.ps1
|
# ============================================================================= # Script : Public/Test-SqlTdeConfiguration.ps1 # Author : Keith Ramsey # Created : 2026-09-07 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-07 Keith Ramsey Initial (B1, GR-002, DR-029): read-only TDE preflight # / status. Reports the database master key, the TDE # certificates and their private-key backup status, and # the encrypted databases + DEK encryptors, as one # SqlCert.Result. Reuses the read seam; never throws. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md) # ----------------------------------------------------------------------------- # Must : read-only (no ShouldProcess) -- reports, changes nothing (DR-029); # never throws -> Failed SqlCert.Result on a read error (DR-005); # flags a TDE certificate whose private key is not backed up, because an # un-escrowed TDE certificate is the unrecoverable failure B1 prevents # (DR-030). Provider access via the shared read seam. # ============================================================================= function Test-SqlTdeConfiguration { <# .SYNOPSIS Reports the Transparent Data Encryption state of an instance, read-only (B1 preflight). .DESCRIPTION Reads -- and changes -- nothing. Reports, as one SqlCert.Result, everything the TDE management commands need to decide what to do: whether a database master key exists in master, which databases are TDE-encrypted and by which certificate, and -- the part that matters most -- whether each TDE certificate's private key has been backed up (pvt_key_last_backup_date). A TDE certificate whose private key was never escrowed is the single unrecoverable TDE failure, so this command calls it out explicitly. Never throws: a read error is a Failed result with the reason. Provider access is the shared read seam (dbatools / SqlServer module), so it is subject to the same optional-dependency probe. .PARAMETER SqlInstance The instance to inspect. Default 'localhost'. .PARAMETER Credential SQL or Windows credential for the connection; omitted for the caller's own token. .OUTPUTS SqlCert.Result Data keys: MasterKeyPresent (bool), EncryptedDatabases (Database / EncryptionState / EncryptorThumbprint / EncryptorType / EncryptorCertName), TdeCertificates (Name / Thumbprint / Subject / ExpiryDate / PrivateKeyLastBackup / PrivateKeyBackedUp), UnescrowedCertificateCount (int). .EXAMPLE Test-SqlTdeConfiguration -SqlInstance sql01 Reports whether sql01 has a database master key, which databases are TDE-encrypted, and whether every TDE certificate's private key is backed up. Safe to run anywhere: it changes nothing. .EXAMPLE (Test-SqlTdeConfiguration -SqlInstance sql01).Data.TdeCertificates | Where-Object { -not $_.PrivateKeyBackedUp } Lists the TDE certificates on sql01 that have never had their private key escrowed -- the certificates to back up immediately with Backup-SqlTdeCertificate. .NOTES Steps: 1. Read database-master-key presence, the encrypted databases + DEK encryptors, and the master certificates (with backup dates) via the read seam. 2. Normalise varbinary thumbprints to hex; resolve each DEK encryptor to its certificate; mark each TDE certificate as escrowed or not from pvt_key_last_backup_date. 3. Assemble a Success SqlCert.Result whose Data describes the DMK, encrypted databases, and TDE certificates; the Detail summarises TDE use and flags any un-escrowed certificate. 4. Any read error -> one Failed result. Never throws. #> [CmdletBinding()] [OutputType('SqlCert.Result')] param( [string] $SqlInstance = 'localhost', [PSCredential] $Credential ) $credSplat = @{} if ($Credential) { $credSplat.Credential = $Credential } $hexOf = { param($v) if ($v -is [byte[]]) { ([System.BitConverter]::ToString([byte[]]$v) -replace '-', '') } else { [string]$v } } $dateOf = { param($v) if ($v -is [datetime]) { [datetime]$v } else { $null } } try { # 1. Three read-only reads via the shared seam. $dmkRows = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' @credSplat ` -Query "SELECT CASE WHEN EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##') THEN 1 ELSE 0 END AS HasDmk;") $hasDmk = [bool]([int]$dmkRows[0].HasDmk) $dekRows = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' @credSplat ` -Query "SELECT DB_NAME(database_id) AS DatabaseName, encryption_state AS EncryptionState, encryptor_thumbprint AS EncryptorThumbprint, encryptor_type AS EncryptorType FROM sys.dm_database_encryption_keys WHERE database_id <> 2;") $certRows = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database 'master' @credSplat ` -Query "SELECT name AS CertName, thumbprint AS Thumbprint, subject AS Subject, expiry_date AS ExpiryDate, pvt_key_last_backup_date AS PvtKeyLastBackup FROM sys.certificates WHERE name NOT LIKE '##MS%';") # 2. Normalise + resolve. $certByTp = @{} foreach ($c in $certRows) { $tp = & $hexOf $c.Thumbprint $certByTp[$tp] = [pscustomobject]@{ Name = [string]$c.CertName Thumbprint = $tp Subject = [string]$c.Subject ExpiryDate = & $dateOf $c.ExpiryDate PrivateKeyLastBackup = & $dateOf $c.PvtKeyLastBackup PrivateKeyBackedUp = ($c.PvtKeyLastBackup -is [datetime]) } } $encDbs = foreach ($d in $dekRows) { $etp = & $hexOf $d.EncryptorThumbprint [pscustomobject]@{ Database = [string]$d.DatabaseName EncryptionState = [int]$d.EncryptionState EncryptorThumbprint = $etp EncryptorType = [string]$d.EncryptorType EncryptorCertName = if ($certByTp.ContainsKey($etp)) { $certByTp[$etp].Name } else { $null } } } # TDE certificates = the master certs that protect a DEK. $tdeThumbs = @($encDbs | Where-Object { $_.EncryptorType -like 'CERTIFICATE*' } | ForEach-Object EncryptorThumbprint | Sort-Object -Unique) # 'CERTIFICATE' or SQL 2025's 'CERTIFICATE_OAEP_256' $tdeCerts = foreach ($t in $tdeThumbs) { if ($certByTp.ContainsKey($t)) { $certByTp[$t] } } $unescrowed = @(@($tdeCerts) | Where-Object { -not $_.PrivateKeyBackedUp }) # 3. Assemble the result. $data = [pscustomobject]@{ MasterKeyPresent = $hasDmk EncryptedDatabases = @($encDbs) TdeCertificates = @($tdeCerts) UnescrowedCertificateCount = $unescrowed.Count } $detail = if (@($encDbs).Count -eq 0) { "No TDE-encrypted database on '$SqlInstance'. Database master key present: $hasDmk." } elseif ($unescrowed.Count -gt 0) { "$(@($encDbs).Count) TDE-encrypted database(s); $(@($tdeCerts).Count) TDE certificate(s) -- $($unescrowed.Count) with NO private-key backup ($($unescrowed.Name -join ', ')). Escrow before the certificate is lost." } else { "$(@($encDbs).Count) TDE-encrypted database(s); $(@($tdeCerts).Count) TDE certificate(s), all with a private-key backup on record." } New-SqlCertResult -Stage Preflight -Status Success -Detail $detail -Data $data } catch { # 4. Never-throw. New-SqlCertResult -Stage Preflight -Status Failed ` -Detail "Test-SqlTdeConfiguration could not read TDE state on '$SqlInstance': $($_.Exception.Message)" -Data $null } } |