AWS Database Blog

TDE certificate rotation on Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) for SQL Server supports Transparent Data Encryption (TDE) to encrypt stored data on your DB instances running Microsoft SQL Server. TDE automatically encrypts data before it’s written to storage, and decrypts data when the data is read from storage.

TDE certificates expire depending on when the certificate is generated and associated with the expiry date. In the case of Amazon RDS for SQL Server, the TDE certificate expires 1 year from the date you enabled TDE on the instance via the option group. This TDE certificate expiration results in the RDS for SQL Server instance being out of compliance from an audit perspective. However, the TDE doesn’t stop working if the certificate expires.

As a good practice and to ensure proper audit and compliance on Amazon RDS for SQL Server enabled with TDE, you must rotate the TDE certificate on a yearly basis on your RDS for SQL Server instances. This involves creating a new certificate and using the new certificate before the existing one expires.

In this post, we show you how to rotate TDE certificates on Amazon RDS for SQL Server.

Solution overview

The TDE encryption hierarchy involves multiple levels of protection, starting with the Windows operating systems level data protection API (DPAPI), which encrypts the Service Master Key (SMK). This SMK encrypts the Database Master Key (DMK) in the primary database, which protects the private key of the certificate created for TDE. The certificate protects the Database Encryption Key (DEK), which in turn encrypts and decrypts the data.

The following figure shows the full encryption hierarchy (architecture reference from Microsoft).

Adding for TDE blog

The DEK is a symmetric key and doesn’t change, which is the case for the SMK and DMK. However, as part of the creation process, the certificate gets generated with an expiry date and needs rotation. The following diagram shows the reference architecture for the high-level implementation of TDE on Amazon RDS for SQL server.

TDE blog post

Implementation

To rotate TDE certificates periodically on TDE-enabled Amazon RDS for SQL Server, complete the following steps. Note that this process doesn’t involve downtime; however, the performance of the instance needs to be monitored if a large database is involved.

  1. If the RDS for SQL Server instance is in Multi-AZ mode, convert it to Single-AZ mode.

This is required because the TDE option is a persistent option that you can’t remove from an option group unless all DB instances and backups are disassociated from the option group.

  1. Verify the TDE certificate detail: 
    USE [master] 
GO 
SELECT name FROM sys.certificates WHERE name LIKE 'RDSTDECertificate%' 
GO
  2. Verify the encrypted databases: 
    USE [master] 
GO
SELECT name FROM sys.databases WHERE is_encrypted = 1 
GO 
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys 
GO
  3. Disable TDE: 
    USE [DBNAME] 
ALTER DATABASE [DBNAME] SET ENCRYPTION OFF 
GO 

USE [DBNAME] 
DROP DATABASE ENCRYPTION KEY 
GO
  4. Verify TDE: 
    USE [master] 
GO 
SELECT name FROM sys.databases WHERE is_encrypted = 1 
GO 
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys 
GO
  5. Change the recovery to simple, which can help clear any encrypted values in the log file: 
    ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE 
GO
  6. Change the recovery to full: 
    ALTER DATABASE [DBNAME] SET RECOVERY FULL 
GO
  7. Modify the instance to switch with an option group that doesn’t have TDE enabled.

This results in the old TDE certificate being completely removed from the RDS for SQL Server instance. For more information about option groups, refer to Working with option groups and Adding an option to an option group.

  1. When the RDS instance is available, add the TDE option to the option group, which generates a new certificate.

When the DB recovery model changes from FULL-SIMPLE-FULL, it takes a new snapshot.

  1. When the snapshot is complete, convert the instance back to Multi-AZ via the Amazon RDS console.

There’s no downtime when attaching the options group, converting to Single-AZ, and then converting back to Multi-AZ. However, there is an impact to high availability and point-in-time recovery (PITR) during this process, because the database recovery modes and Multi-AZ are being altered.

Limitations with TDE on Amazon RDS for SQL Server

This solution has the following limitations:

  • The solution is only supported by SQL Server 2019 Standard Edition and Enterprise Edition, and SQL Server 2012–2017 Enterprise Edition.
  • Amazon RDS doesn’t support importing or exporting TDE certificates.
  • You can create native backups of TDE-enabled databases, but you can’t restore those backups to on-premises databases. You can’t restore native backups of TDE-enabled, on-premises databases.

Summary

In this post, you learned how to rotate TDE certificates on an RDS for SQL Server instance. You can use this strategy to rotate TDE certificates in a timely fashion before the certificates expire and to ensure proper compliance on the instance.

If you have any comments or questions, please share them in the comments section.

About the Authors

Lakshman Thatisetty is a Database Specialist Solutions Architect with Amazon Web Services. He works with AWS customers designing customer solutions on database projects, helping them migrate and modernize their existing databases to the AWS Cloud as well as orchestrate large-scale migrations in AWS.

Saroj Kumar Das is a Cloud Support Engineer with Amazon Web Services. He works with AWS customers providing deep technical expertise on the Amazon RDS database platforms with specialization in SQL Server.