Public/Test-SqlColumnMasterKey.ps1
|
# ============================================================================= # Script : Public/Test-SqlColumnMasterKey.ps1 # Author : Keith Ramsey # Created : 2026-09-08 # ============================================================================= # Change Log # ----------------------------------------------------------------------------- # 2026-09-08 Keith Ramsey Initial (B3, GR-008, DR-041): read-only audit of a # database's Always Encrypted Column Master Keys -- # provider, key path, dependent CEK count, and (for # the certificate-store provider) whether the # referenced certificate is present locally. Reuses # the read seam; never-throw. # ============================================================================= # Decision Contract (Docs/DECISIONS_PHASE7.md DR-041) # ----------------------------------------------------------------------------- # Must : read-only; per CMK report provider / key path / dependent CEK count; # for MSSQL_CERTIFICATE_STORE parse key_path (location/store/thumbprint) # and check local cert presence + expiry; one never-throw SqlCert.Result; # does NOT create CEKs, generate/distribute the cert, or handle Key Vault. # ============================================================================= function Test-SqlColumnMasterKey { <# .SYNOPSIS Audits a database's Always Encrypted Column Master Keys, read-only (B3). .DESCRIPTION Reports each Column Master Key (CMK) defined in a database: its key-store provider, its key path, and how many Column Encryption Keys depend on it -- and, for the certificate-store provider (MSSQL_CERTIFICATE_STORE), whether the certificate the key path points at is actually present in the local certificate store, with its expiry. This answers the question that otherwise only surfaces when a query fails: is the certificate my Always-Encrypted columns depend on present here, and how much depends on it? Read-only; never throws. It does not create Column Encryption Keys, generate or distribute the CMK certificate, or handle the Azure Key Vault provider -- see the module help / DR-041; CEK creation is the SqlServer module's New-SqlColumnEncryptionKey. .PARAMETER SqlInstance The instance to query. Default 'localhost'. .PARAMETER Database The database whose Column Master Keys to audit. .PARAMETER Credential SQL or Windows credential for the connection. .OUTPUTS SqlCert.Result Data key: ColumnMasterKeys = @(Name, Provider, KeyPath, CekCount, IsCertificateStore, CertPresentLocally, NotAfter). .EXAMPLE Test-SqlColumnMasterKey -SqlInstance sql01 -Database Sales Lists the Sales database's column master keys and, for certificate-store keys, whether the backing certificate is present on this machine. .EXAMPLE (Test-SqlColumnMasterKey -SqlInstance sql01 -Database Sales).Data.ColumnMasterKeys | Where-Object { $_.IsCertificateStore -and -not $_.CertPresentLocally } Surfaces the CMKs whose certificate is missing here -- the columns those keys protect cannot be decrypted on this machine until the certificate is restored. .NOTES Steps: 1. Read the database's column master keys (name / provider / key path) and per-CMK dependent CEK count via the read seam. 2. For each MSSQL_CERTIFICATE_STORE CMK, parse the key path (location/store/thumbprint) and look the certificate up in the local Cert: store for presence + expiry. 3. Return a Success SqlCert.Result; the Detail flags any cert-store CMK whose certificate is absent locally. 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 } $query = @' SELECT cmk.name AS CmkName, cmk.key_store_provider_name AS Provider, cmk.key_path AS KeyPath, (SELECT COUNT(DISTINCT cekv.column_encryption_key_id) FROM sys.column_encryption_key_values AS cekv WHERE cekv.column_master_key_id = cmk.column_master_key_id) AS CekCount FROM sys.column_master_keys AS cmk; '@ try { # 1. Read the CMKs + dependent CEK counts. $rows = @(Invoke-SqlCertInventoryQuery -SqlInstance $SqlInstance -Database $Database -Query $query @credSplat) $cmks = foreach ($r in $rows) { $provider = [string]$r.Provider $keyPath = [string]$r.KeyPath $isCertStore = ($provider -eq 'MSSQL_CERTIFICATE_STORE') $present = $false $notAfter = $null if ($isCertStore) { # 2. key_path is 'Location/Store/Thumbprint' e.g. 'CurrentUser/My/<40 hex>'. $parts = $keyPath -split '/' if (@($parts).Count -ge 3) { $loc = $parts[0]; $store = $parts[1]; $tp = $parts[-1] $cert = Get-ChildItem "Cert:\$loc\$store" -ErrorAction SilentlyContinue | Where-Object { $_ -and $_.Thumbprint -ieq $tp } | Select-Object -First 1 if ($cert) { $present = $true; $notAfter = $cert.NotAfter } } } [pscustomobject]@{ Name = [string]$r.CmkName Provider = $provider KeyPath = $keyPath CekCount = [int]$r.CekCount IsCertificateStore = $isCertStore CertPresentLocally = $present NotAfter = $notAfter } } $cmks = @($cmks) # 3. Report. if ($cmks.Count -eq 0) { return New-SqlCertResult -Stage Verify -Status Success ` -Detail "No Always Encrypted column master key in database '$Database' on '$SqlInstance'." ` -Data ([pscustomobject]@{ ColumnMasterKeys = @() }) } $missing = @($cmks | Where-Object { $_.IsCertificateStore -and -not $_.CertPresentLocally }) $detail = "$($cmks.Count) column master key(s) in '$Database' on '$SqlInstance'." if ($missing.Count -gt 0) { $detail += " $($missing.Count) certificate-store CMK(s) whose certificate is NOT present here ($($missing.Name -join ', ')) -- columns they protect cannot be decrypted on this machine." } New-SqlCertResult -Stage Verify -Status Success -Detail $detail ` -Data ([pscustomobject]@{ ColumnMasterKeys = $cmks }) } catch { New-SqlCertResult -Stage Verify -Status Failed ` -Detail "Test-SqlColumnMasterKey could not read column master keys in '$Database' on '$SqlInstance': $($_.Exception.Message)" ` -Data ([pscustomobject]@{ ColumnMasterKeys = @() }) } } |