Detent Point

SqlCertForge TLS certificate binding for SQL Server and Reporting Services

Test-SqlColumnMasterKey

Reports each Always Encrypted column master key in a database — its provider and key path, how many column encryption keys depend on it, and, for certifica...

Domain: Always Encrypted · Risk: Read-only · Runs on: Windows, PowerShell 5.1 or 7

This answers a question that usually only surfaces when a query fails: is the certificate my encrypted columns depend on actually present on this machine, and how much depends on it? It reads the database’s column master keys and checks each one, without changing anything.

For a certificate-store key it also looks in the local certificate store to confirm the backing certificate is there. If it is missing, the columns that key protects cannot be read on this machine — and this command tells you that before an application hits the error.

Reads sys.column_master_keys for each column master key’s provider and key path, and counts the distinct column encryption keys that depend on it. For a MSSQL_CERTIFICATE_STORE key it parses the key path (Location/Store/Thumbprint) and looks the thumbprint up in the local Cert: store, reporting presence and expiry (NotAfter). Read-only; never throws; returns a SqlCert.Result.

It does not create column encryption keys, generate or distribute the certificate, or handle the Azure Key Vault provider.

Recipe 01 — List a database’s column master keys

Test-SqlColumnMasterKey -SqlInstance sql01 -Database Sales

For certificate-store keys the output shows whether the backing certificate is present here.

Recipe 02 — Find keys whose certificate is missing on this machine

(Test-SqlColumnMasterKey -SqlInstance sql01 -Database Sales).Data.ColumnMasterKeys |
    Where-Object { $_.IsCertificateStore -and -not $_.CertPresentLocally }

These are the keys whose columns cannot be decrypted here until the certificate is restored.

Watchpoint — “present” is local to where you run it. The certificate check reads the certificate store of the machine the command runs on. A key that reports its certificate missing here may still resolve on another machine that holds it, so run the command where the application connects from.

Always Encrypted has two key layers. The column master key is the outer key, kept in a key store outside the database; the database holds only a reference to it. This command reads those references and, for certificate-store keys, checks whether the certificate they point to is on this machine.

It also counts the column encryption keys under each master key — the inner keys that actually wrap the column data. A high count means a lot depends on that one certificate. Nothing is changed: this only looks.

Question Answer
Outbound calls? Connects to the SQL instance you name to read its metadata (through the dbatools/SqlServer provider), and reads the local certificate store. No other outbound calls.
What it changes Nothing — read-only.
Privileges Read access to the database’s column master key metadata; read access to the local certificate store.
Licence Free. Read-only audit commands need no licence.
Dependency Why Required?
Windows + PowerShell 5.1 or 7 Runs the query and reads the certificate store. Yes
Connectivity to the SQL instance The metadata is read from that instance. Yes
The certificate present locally Only affects the CertPresentLocally result; the command still runs and reports if it is absent. No
  • Read-only — it inventories Always Encrypted key configuration without changing anything.
  • Reports the dependent column encryption key count per master key, so the reach of a missing or expiring certificate is visible.
  • Flags each certificate-store key whose certificate is not present on this machine, surfacing a latent decryption failure before an application hits it.
  • Returns structured per-key rows (Name, Provider, KeyPath, CekCount, IsCertificateStore, CertPresentLocally, NotAfter) — evidence for an audit record.