Commands / Get-sqmCertificateReport
Get-sqmCertificateReport
SecuritysqmSQLTool v1.8.2+ · Certificate Audit✓ Expiration Status  TXT/CSV Export
Comprehensive audit of all SQL Server certificates: Database Master Key status, instance certificates (AlwaysOn/TDE/ServiceBroker/Backup), TDE encryption certificates, and user database certificates. Calculates remaining days to expiration with traffic-light status (OK/Warning/Critical/Expired). Exports results as TXT report and CSV lists including critical-only subset.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestring[]Optional$env:COMPUTERNAMEOne or more instances. Pipeline-capable.
-SqlCredentialPSCredentialOptional—Credentials for the connection.
-WarningThresholdDaysintOptional90Days until expiration for Warning status.
-CriticalThresholdDaysintOptional30Days until expiration for Critical status.
-IncludeUserDatabasesswitchSwitch$falseAlso audit certificates in user databases (not just master).
-OutputPathstringOptionalGet-sqmDefaultOutputPathDirectory for TXT and CSV reports.
-EnableExceptionswitchSwitch$falseRe-throw exceptions immediately.

Execution Flow

START foreach $instance in $SqlInstance Connect to instance and verify master database access Requires: db_owner or equivalent role Check Database Master Key (master) • Exists? • Encrypted by SMK? • Ready for auto-open? Result: DMK Status + Thumbprint Query sys.certificates + sys.dm_cryptographic_provider_properties Name, Type, Subject, Issuer, Thumbprint, Expiry, Private Key status For each cert: identify purpose (AlwaysOn/TDE/ServiceBroker/Backup/User) For each TDE-encrypted database: certificate binding Query sys.dm_database_encryption_keys for bound certificate thumbprint Track encryption state (ENCRYPTED/DECRYPTION_IN_PROGRESS/etc.) -IncludeUserDatabases? NO YES Query user databases for sys.certificates (column encryption, signing) Per database: certificate name, owner, expiry date, purpose Collect and merge with instance-level results Calculate expiration status per certificate OK (green) / Warning (yellow) / Critical (orange) / Expired (red) Days until expiration Export to -OutputPath: full TXT/CSV + critical-only CSV Return [PSCustomObject[]] all certificates sorted by expiration DONE

Examples

Audit instance certificates
Get-sqmCertificateReport -SqlInstance "SQL01"
Check for expiring certificates (Warning + Critical)
Get-sqmCertificateReport -SqlInstance "SQL01" -IncludeUserDatabases | Where-Object ExpirationStatus -in "Warning","Critical","Expired"
← Back
Command Index