Checking Certificates That Expire Soon in Every Database

Certificates that expire are easy to forget, because nothing warns you until something stops working. A date alone does not tell you what will break. So I collect the expiry date, the thumbprint and the private key state, then find out who owns each one.

The Sunday nobody saw coming

Picture a Sunday morning page. A feature that worked all year has stopped, and the error mentions a certificate. Nobody remembers creating it. The person who did has left the company.

A certificate lives inside a database, or in master. Nobody checks them, because there is no screen that lists all of them. Let me show you how to build that list yourself, starting with two test certificates.

Create two test certificates

The first, ExpiryDemo, expires on December 31, 2030. The second, ExpiryDemoSoon, expires in 30 days, so it shows up in a “soon” report. Both live in the current database and are dropped at the end. The password protects demo material only.

IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'ExpiryDemo')
    DROP CERTIFICATE ExpiryDemo;
IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'ExpiryDemoSoon')
    DROP CERTIFICATE ExpiryDemoSoon;

CREATE CERTIFICATE ExpiryDemo
    ENCRYPTION BY PASSWORD = N'DemoOnly_Certificate_9!'
    WITH SUBJECT = N'Certificate inventory demonstration', EXPIRY_DATE = '20301231';

DECLARE @soon char(8) = CONVERT(char(8), DATEADD(DAY, 30, SYSUTCDATETIME()), 112);
DECLARE @sql nvarchar(max) =
    N'CREATE CERTIFICATE ExpiryDemoSoon
      ENCRYPTION BY PASSWORD = N''DemoOnly_Certificate_9!''
      WITH SUBJECT = N''Expires in about 30 days'', EXPIRY_DATE = ''' + @soon + N''';';
EXEC (@sql);

Read the certificate metadata

Everything starts in sys.certificates. Three columns matter first. The name tells you what someone called it. The expiry date is your deadline. And pvt_key_encryption_type_desc tells you how the private key is protected.

SELECT name, expiry_date, pvt_key_encryption_type_desc
FROM sys.certificates
WHERE name = N'ExpiryDemo';

ENCRYPTED_BY_PASSWORD means the private key needs that password to be used. If you lose the password, you lose the key. That matters on renewal day. The thumbprint column, which this picture leaves out, identifies the certificate even when two copies share a name.

Search every database you can reach

One database is not enough. The loop below visits each online database you can access and copies its certificates into a temp table. If a database cannot be read, the CATCH block reports it instead of silently skipping it. Then the last query lists everything expiring within 90 days.

SET NOCOUNT ON;
DROP TABLE IF EXISTS #CertificateInventory;
CREATE TABLE #CertificateInventory (DatabaseName sysname, CertificateName sysname,
    ExpiryDate datetime, Thumbprint varbinary(64), PrivateKeyState nvarchar(60));

DECLARE @db sysname, @sql nvarchar(max);
DECLARE certs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE state_desc = N'ONLINE' AND HAS_DBACCESS(name) = 1 ORDER BY name;
OPEN certs;
FETCH NEXT FROM certs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'USE ' + QUOTENAME(@db) + N'; INSERT #CertificateInventory
        SELECT DB_NAME(), name, expiry_date, thumbprint, pvt_key_encryption_type_desc
        FROM sys.certificates;';
    BEGIN TRY
        EXEC sys.sp_executesql @sql;
    END TRY
    BEGIN CATCH
        SELECT @db AS UncollectedDatabase, ERROR_MESSAGE() AS CollectionMessage;
    END CATCH;
    FETCH NEXT FROM certs INTO @db;
END;
CLOSE certs;
DEALLOCATE certs;

SELECT DatabaseName, CertificateName, ExpiryDate,
       DATEDIFF(DAY, SYSUTCDATETIME(), ExpiryDate) AS DaysLeft, Thumbprint, PrivateKeyState
FROM #CertificateInventory
WHERE ExpiryDate <= DATEADD(DAY, 90, SYSUTCDATETIME())
ORDER BY ExpiryDate, DatabaseName, CertificateName;

Your list will differ from mine. It should contain ExpiryDemoSoon with about 30 days left. ExpiryDemo is not listed, because 2030 is far away. Offline databases and databases you cannot open are not covered, so say so whenever you share this report. Limited permissions can also hide certificates inside a database you can open.

Read the list, then find an owner

On my test server the list also had certificates named ##MS_SchemaSigningCertificate, one in master and one in msdb. Those belong to SQL Server. Leave them alone, and do not renew them as if they were yours.

For the rest, ask what each one is used for. It could sign a module, protect a backup, secure an endpoint or something else. Match copies by thumbprint, because the same name can hide different certificates. And never throw away a certificate or its private key if an old encrypted backup might still need it. Creating a new certificate does not switch anything over by itself.

DROP TABLE IF EXISTS #CertificateInventory;
IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'ExpiryDemo')
    DROP CERTIFICATE ExpiryDemo;
IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'ExpiryDemoSoon')
    DROP CERTIFICATE ExpiryDemoSoon;

Give every certificate on your list an owner and a tested next step.

An expiry date is not a forecast, it is a prompt to ask who depends on it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.