Public/Test-SqlCellKeyHierarchy.ps1
|
# ============================================================================= # Script : Public/Test-SqlCellKeyHierarchy.ps1 # Author : Keith Ramsey # Created : 2026-09-08 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-08 Keith Ramsey Initial (B4, GR-010, DR-043): read-only audit of a # user database's cell-level encryption hierarchy -- # each certificate, the symmetric key(s) it protects, # and which principals may use them (least-privilege # check). Reuses the read seam; never-throw. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md DR-043) # ----------------------------------------------------------------------------- # Must : read-only; per cell certificate report its symmetric key(s) and the # principals with permission on it; exclude ##MS system certs; one # never-throw SqlCert.Result. Provisioning is New-SqlCellCertificateKey. # ============================================================================= function Test-SqlCellKeyHierarchy { <# .SYNOPSIS Audits a user database's cell-level encryption certificate/key hierarchy (B4, read-only). .DESCRIPTION Reports the cell-encryption hierarchy in a database: each certificate (excluding the built-in ##MS_* certs), the symmetric key(s) that certificate protects, and which database principals hold permission to use the certificate. The last part is the one that quietly drifts -- more principals able to open the key than intended -- so it is the least-privilege check for hand-rolled column encryption. Read-only; never throws. .PARAMETER SqlInstance The instance to query. Default 'localhost'. .PARAMETER Database The user database to audit. .PARAMETER Credential SQL or Windows credential for the connection. .OUTPUTS SqlCert.Result Data key: Certificates = @(Name, SymmetricKeys @(Name/Algorithm), Principals @(Name/Permission)). .EXAMPLE Test-SqlCellKeyHierarchy -SqlInstance sql01 -Database Sales Lists Sales's cell certificates, the symmetric keys each protects, and who can use them. .EXAMPLE (Test-SqlCellKeyHierarchy -SqlInstance sql01 -Database Sales).Data.Certificates | Where-Object { @($_.Principals).Count -gt 2 } Surfaces cell certificates usable by more than two principals -- candidates for tightening. .NOTES Steps: 1. Read the certificate -> symmetric-key mapping (sys.certificates / sys.key_encryptions / sys.symmetric_keys), excluding ##MS_* certs, via the read seam. 2. Read the principals with permission on those certificates (sys.database_permissions class 25 -> sys.database_principals). 3. Aggregate per certificate: its symmetric keys and its grantees. Success SqlCert.Result; any read error -> Failed. Never throws. #> [CmdletBinding()] [OutputType('SqlCert.Result')] param( [string] $SqlInstance = 'localhost', [Parameter(Mandatory)] [string] $Database, [PSCredential] $Credential ) $credSplat = @{} if ($Credential) { $credSplat.Credential = $Credential } $mapQuery = @' SELECT c.name AS CertName, sk.name AS SymKeyName, sk.algorithm_desc AS Algorithm FROM sys.certificates AS c LEFT JOIN sys.key_encryptions AS ke ON ke.thumbprint = c.thumbprint LEFT JOIN sys.symmetric_keys AS sk ON sk.symmetric_key_id = ke.key_id WHERE c.name NOT LIKE '##MS%'; '@ $permQuery = @' SELECT c.name AS CertName, dp.name AS Principal, perm.permission_name AS Permission FROM sys.certificates AS c JOIN sys.database_permissions AS perm ON perm.major_id = c.certificate_id AND perm.class = 25 JOIN sys.database_principals AS dp ON dp.principal_id = perm.grantee_principal_id WHERE c.name NOT LIKE '##MS%'; '@ try { # 1/2. Read the cert->symkey mapping and the cert permissions. $mapRows = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database $Database -Query $mapQuery @credSplat) $permRows = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database $Database -Query $permQuery @credSplat) # 3. Aggregate per certificate. $byCert = [ordered]@{} foreach ($m in $mapRows) { $cn = [string]$m.CertName if (-not $byCert.Contains($cn)) { $byCert[$cn] = [pscustomobject]@{ Name = $cn SymmetricKeys = [System.Collections.Generic.List[object]]::new() Principals = [System.Collections.Generic.List[object]]::new() } } $sk = [string]$m.SymKeyName if ($sk -and -not (@($byCert[$cn].SymmetricKeys) | Where-Object { $_.Name -eq $sk })) { $byCert[$cn].SymmetricKeys.Add([pscustomobject]@{ Name = $sk; Algorithm = [string]$m.Algorithm }) } } foreach ($p in $permRows) { $cn = [string]$p.CertName if (-not $byCert.Contains($cn)) { $byCert[$cn] = [pscustomobject]@{ Name = $cn SymmetricKeys = [System.Collections.Generic.List[object]]::new() Principals = [System.Collections.Generic.List[object]]::new() } } $byCert[$cn].Principals.Add([pscustomobject]@{ Name = [string]$p.Principal; Permission = [string]$p.Permission }) } $certs = @(foreach ($c in $byCert.Values) { [pscustomobject]@{ Name = $c.Name SymmetricKeys = @($c.SymmetricKeys) Principals = @($c.Principals) } }) if ($certs.Count -eq 0) { return New-SqlCertResult -Stage Verify -Status Success ` -Detail "No cell-level (non-system) certificate in database '$Database' on '$SqlInstance'." ` -Data ([pscustomobject]@{ Certificates = @() }) } New-SqlCertResult -Stage Verify -Status Success ` -Detail "$($certs.Count) cell certificate(s) in '$Database' on '$SqlInstance' (with their symmetric keys and grantees)." ` -Data ([pscustomobject]@{ Certificates = $certs }) } catch { New-SqlCertResult -Stage Verify -Status Failed ` -Detail "Test-SqlCellKeyHierarchy could not read the cell key hierarchy in '$Database' on '$SqlInstance': $($_.Exception.Message)" ` -Data ([pscustomobject]@{ Certificates = @() }) } } |