How to Rotate a Database Encryption Certificate in SQL Server (Replacing an Expiring SQL Server Encryption Key)

​Certificates are a crucial component of securing SQL Server. They are used to encrypt data, secure communications, and protect sensitive information.

SQL Server certificates have a defined expiration date. When a certificate nears expiration, it must be replaced. However, SQL Server does not permit direct modification of an existing certificate. Instead, a new certificate must be created and used to replace the old one.

This article provides a step-by-step guide on replacing an expiring database encryption certificate in SQL Server.

Steps to Replace the Database Encryption Certificate

Check the Current Certificate Details

To check the details of the existing certificate, including its expiration date, run the following query:

USE [master];

SELECT

name AS CertName,

certificate_id AS CertID,

subject,

expiry_date,

pvt_key_encryption_type_desc AS EncryptType,

issuer_name AS Issuer

FROM sys.certificates;

This query retrieves the list of certificates and their expiration dates. Identify the certificate that needs replacement.

Backup the Current Certificate

Before replacing a certificate, back it up to ensure it can be restored if needed.

BACKUP CERTIFICATE [YourCertificateName]

TO FILE = 'C:\Backup\YourCertificateName.cer'

WITH PRIVATE KEY (

FILE = 'C:\Backup\YourCertificateName.key',

ENCRYPTION BY PASSWORD = 'YourStrongPassword'

);

GO

Important: Store these backup files securely.

Generate a New Certificate

Create a new certificate with an updated expiration date:

CREATE CERTIFICATE [NewCertificateName]

WITH SUBJECT = 'Database Encryption Certificate',

EXPIRY_DATE = 'YYYY-MM-DD';

GO

Note: By default, the new certificate expires in one year. To extend its validity, generate the certificate externally using a third-party tool and import it into SQL Server.

Reminder: A certificate used in Transparent Data Encryption (TDE) remains functional even after expiration.

Replace the Certificate in Use

If the certificate is used for Transparent Data Encryption (TDE), follow these steps to replace it:

Add the New Certificate to the Database

USE [YourDatabaseName];

GO

ALTER DATABASE ENCRYPTION KEY

ENCRYPTION BY SERVER CERTIFICATE [NewCertificateName];

GO

This command configures the database to use the new encryption certificate.

Drop the Old Certificate (Optional)

Once the new certificate is in use and verified, you may remove the old certificate:

USE [master];

GO

DROP CERTIFICATE [OldCertificateName];

GO

Caution: Ensure the old certificate is no longer required before deleting it.

Test the Configuration

After replacing the certificate, verify that encryption remains functional:

• Query the certificate details to confirm the new certificate is in use.
• Test database access and connectivity.

To verify encryption status, run:

USE [YourDatabaseName];

GO

SELECT * FROM sys.dm_database_encryption_keys;

GO

Backup the New Certificate

Always back up the new certificate and private key to ensure database recoverability:

BACKUP CERTIFICATE [NewCertificateName]

TO FILE = 'C:\Backup\NewCertificateName.cer'

WITH PRIVATE KEY (

FILE = 'C:\Backup\NewCertificateName.key',

ENCRYPTION BY PASSWORD = 'YourNewStrongPassword'

);

GO

Recommendations

• Test in a Staging Environment: Always perform certificate replacement in a test environment before deploying it in production.
• Monitor the Transition: Ensure seamless encryption functionality after switching certificates.
• Maintain Secure Backups: Keep both old and new certificates backed up securely.

By following these steps, you can smoothly transition to a new database encryption certificate while maintaining the security of your SQL Server environment. Do you need more help with your SQL Server security and encryption? Get in touch with Datavail’s dedicated SQL Server team.