Get-SqlCertInventory

This answers “what certificates are in use across this SQL Server, on every surface, and which are close to expiring?” in a single command, without changing anything. It’s safe to run against any server.

It’s read-only and free, so it’s the natural first step for auditing an estate — a full picture of every certificate and its expiry before any renewal or migration work is planned.

Enumerates the six certificate surfaces present on the target — engine connection binding, Reporting Services / PBIRS, TDE, backup encryption, endpoints (mirroring / AG / Service Broker), and cell-level — and returns one SqlCert.Inventory row per certificate, plus a Skipped or Failed row for any surface that is absent or unreadable. Expiry is reported via DaysToExpiry / Expiring (Unknown when the certificate is undated), never acted on. -ThresholdDays (default 30) sets the days-to-expiry at or below which a row is flagged Expiring. Never throws — a reader that fails yields a Failed row carrying the reason. Rows are provenance-stamped.

Recipe 01 — Scan every surface as one table

Get-SqlCertInventory -SqlInstance sql01 |
    Format-Table Surface, Subject, NotAfter, DaysToExpiry, Expiring -AutoSize

Every certificate surface on sql01 in one table. Changes nothing.

Recipe 02 — The renewal shortlist across several instances

Get-SqlCertInventory -SqlInstance sql01, sql02, sql03 -ThresholdDays 45 |
    Where-Object Expiring |
    Sort-Object DaysToExpiry

Every certificate expiring within 45 days, soonest first — the list to schedule for renewal.

Recipe 03 — Surface the gaps

Get-SqlCertInventory -SqlInstance sql01 |
    Where-Object { $_.Status -ne 'Found' -or $null -eq $_.Expiring }

Skipped and Failed rows, plus any Found certificate whose expiry could not be read (DaysToExpiry and Expiring are Unknown). These never masquerade as fine.

Recipe 04 — Capture the estate to CSV

Get-SqlCertInventory -SqlInstance sql01, sql02 |
    Export-Csv -NoTypeInformation -Path .\cert-inventory.csv

Found, Skipped and Failed rows, each provenance-stamped (ModuleVersion / CommitSha / RunId / Timestamp), for a change ticket or audit record.

Recipe 05 — Remote reads with an explicit credential

Get-SqlCertInventory -SqlInstance sql01 -Credential (Get-Credential) |
    Group-Object Node, Surface

Uses a supplied credential for the remote registry / WMI / SQL reads (where Kerberos pass-through does not apply), grouped by host and surface.

Watchpoint — Unknown is not false. A row whose expiry could not be read reports Expiring as Unknown (null), not $true, so a bare Where-Object Expiring filter silently drops it. Check Status -ne 'Found' and a null Expiring separately (Recipe 03) so a gap doesn’t pass as fine.

A SQL Server can hold certificates in several independent places — the connection binding, TDE, backup encryption, Reporting Services, endpoints, and cell-level encryption. Each has its own way of being read.

This command walks all of them, lists every certificate it finds with its expiry, and marks any surface it couldn’t read — all without changing a thing. It’s the safe way to see where an estate stands before deciding what to fix.

• A non-mutating inventory across every certificate surface — suitable for a compliance sweep.
• Expiry is reported as Unknown when the certificate is undated, never assumed fine, so gaps surface rather than hide.
• Every row is provenance-stamped (ModuleVersion / CommitSha / RunId / Timestamp) for an audit record.
• Read-only and free, so estate-wide coverage isn’t gated by licensing.