Manage SQL Server Encryption Keys Across AWS Regions for Disaster Recovery

This blog post provides a method for managing SQL Server encryption keys across AWS Regions to enable successful encrypted database recovery during cross-region disaster recovery (DR) events on Amazon EC2. It highlights the critical importance of the entire encryption key hierarchy for DR.

The post explains the layered encryption hierarchy used by SQL Server, where each key protects the one below it. It details how Transparent Data Encryption (TDE), encrypted backups with certificates, and column-level encryption keys traverse this hierarchy and the implications for DR. Missing any key in the chain can lead to data inaccessibility.

For cross-region DR to a different SQL Server instance, the DR instance's master database has its own SMK. TDE certificates are protected by the DR instance's DMK. User databases restored to the DR instance retain their original DMK, which must be re-encrypted by the new instance's SMK if column-level encryption keys are protected by it.

To ensure TDE-encrypted databases can be restored on a DR instance, TDE certificates and their private keys must be exported from the primary instance and then imported onto the DR instance's master database before restoring the database.

Column-level encryption keys stored within a database have different DR behaviors based on what protects them. If protected by the DMK, the keys travel with the database, but the DMK must be re-encrypted with the DR instance's SMK. If protected by a certificate, that certificate needs export/import. Passwords for password-protected keys should be stored in AWS Secrets Manager.