Transparent data encryption (TDE) in Azure SQL with customer-managed key (CMK) supports Bring Your Own Key (BYOK) for data protection at rest and facilitates separation of duties in key and data management. With customer-managed TDE, the user manages the lifecycle of keys (creation, upload, rotation, deletion), usage permissions, and auditing of key operations. The key used for encrypting the Database Encryption Key (DEK), known as the TDE protector, is an asymmetric key managed by the customer and stored in Azure Key Vault (AKV).

Once a database is encrypted with TDE using a Key Vault key, new backups are encrypted with the same TDE protector. Changing the TDE protector does not update old backups to use the new protector. To restore a backup encrypted with a Key Vault TDE protector, ensure that the key material is accessible to the target server. The TDE feature was designed with the requirement that both the current and previous TDE protectors are necessary for successful restores. It is recommended to retain all previous versions of the TDE protector in the key vault to enable the restore of database backups.

This blog post will provide detailed information on which keys should be available for a database restore and the reasons why they are necessary.

Encryption of the transaction log file

To understand which keys are required for a point-in-time restore, it is necessary to first explain how the transaction log encryption operates. The SQL Server Database Engine divides each physical log file into several virtual log files (VLFs). Each VLF has its own header. Encrypting the entire log file in one single sweep is not possible, so each VLF is encrypted individually and the encryptor information is stored in the VLF header. When the log manager needs to read a particular VLF for recovery, it uses the encryptor information in the VLF header to locate the encryptor and decrypt the VLF.

Unencrypted transaction log

Consider the following sequence of blocks as the logical log file, where each block represents a Virtual Log File (VLF). Initially, we are in VLF1, and the current Log Sequence Number (LSN) is within VLF1.

Transparent data encryption enabled

When TDE is enabled on the database, the current VLF is filled with non-operational log records, and a new VLF (VLF2) is created. Each VLF has one header containing the encryptor information, so whenever the encryptor information changes, the log rolls over to the next VLF boundary. The subsequent VLF will contain the new DEK (DEK_1) and the thumbprint of the encryptor of the DEK in the header. Any additions to the log file will be added to VLF2 and will be encrypted.

When VLF2 reaches capacity, a new VLF (VLF3) will be generated. Since encryption is enabled, the new VLF will contain the DEK and its information in its header, and it will also be encrypted.

Key rotation

When a new DEK is generated or its encryptor changes, the log rolls over to the next VLF boundary. The new VLF (VLF4) will contain the updated DEK and encryptor information. For example, if a new DEK (DEK_2) is generated via key rotation in the Azure Portal, VLF3 will fill with non-operational commands before VLF4 is created and encrypted by the new DEK.

A database can use multiple keys at a single time

Currently, for server and database level CMK, after a key rotation, some of the active VLFs may still be encrypted with an old key. As key rotations are allowed before these VLFs are flushed, the database can end up using multiple keys simultaneously. To ensure that at a certain point in time, the database is using only the current encryption protector (primary generation p) and the old encryption protector (generation p-1) we used the following approach:

1. Block a protector change operation when there is any active VLF using an old thumbprint different from the current encryption protector. When a customer attempts a protector change or the key is being auto rotated, we will verify if there are any VLFs using the old thumbprint that are "active". If so, the protector change will be blocked.
2. If there are no "active VLFs" using the old thumbprint, we take a log backup to flush the inactive VLFs, then rotate the protector and wait for it to fully rotate.

This approach ensures that the database will use 2 keys at any given time.

Example

1. Time t0 = DB is created without encryption
2. Time t1 = DB is protected by Thumbprint A
3. Time t2 = DB protector is rotated to Thumbprint B
4. Time t3 = Customer requests a protector change to Thumbprint C
   • We check the active VLFs, they are using Thumbprint A and we block the change
   • This ensures that currently the DB is only using Thumbprint A and Thumbprint B
5. Time t4 = Customer requests a protector change to Thumbprint C
   • We check the active VLFs, and none of them are using thumbprint A.
   • We solicit a log backup, that should flush the inactive VLFs using thumbprint A
   • Then we rotate the encryption protector and succeed the operation only when both (b) and (c) are fully complete
6. This approach ensures that after time t4, the DB is only using Thumbprint B and Thumbprint C

Point-in-time restore

Based on the provided information, it is evident that multiple keys are necessary for a restore if key rotations have taken place. To restore a backup encrypted with a TDE protector from Azure Key Vault, ensure that the key material is accessible to the target server. Therefore, we recommend that you keep the old versions of the TDE protector in the Azure Key Vault, so database backups can be restored.

To mitigate it, run the Get-AzSqlServerKeyVaultKey cmdlet for the target server or Get-AzSqlInstanceKeyVaultKey for the target managed instance to return the list of available keys and identify the missing ones. To ensure all backups can be restored, make sure the target server for the restore has access to all of keys needed. These keys don't need to be marked as TDE protector.

Backed up log files remain encrypted with the original TDE protector, even if it was rotated and the database is now using a new TDE protector. At restore time, both keys are needed to restore the database. If the log file is using a TDE protector stored in Azure Key Vault, this key is needed at restore time, even if the database has been changed to use service-managed TDE in the meantime.

Point-in-time restore example

When a customer wants to restore data to a specific point in time (tx), they will need the current encryption protector (p) and the old encryption protector (p-1) from the period [tx-8 days] to [tx]. The reason for using tx-8 is that there is a full backup every 7 days, so we expect to have a complete backup within the last 8 days. Because VLFs may remain active with the earlier key, the system is designed to use the two latest thumbprints (p-2 and p-3) from outside the buffer period. Consider the following timeline:

The PITR request is made for 8/20/2025 (tx), at which point Thumbprint D (p) is active. To ensure we have a full backup, we subtract 8 days, bringing us to 8/12/2025 (tx-8), when Thumbprint C (p-1) is active. Since VLFs might still be active with the previous key, we also need Thumbprint B (p-2) and Thumbprint A (p-3).

The required thumbprints for this point-in-time restore are A, B, C and D.

Conclusion

To restore a backup encrypted with a Key Vault TDE protector, it is essential to ensure that the key material is accessible to the target server. It is recommended to retain all old versions of the TDE protector in the key vault to facilitate the restore of database backups.