Backup and restore managed instance from .bak

Question

Hi,

We are attempting to setup a backup of our sql managed instance database to a non-azure storage location as a mitigation for possible ransomware attack on our tenant.

This process appears to work in testing:

1. Produce a .bak file using the 'BACKUP DATABASE' command with 'COPY_ONLY' and store it outside Azure.
2. Restore the .bak to a different azure managed instance in our tenant using the 'RESTORE DATABASE' command.

The documentation indicates that the built-in TDE certificate is unique to each server: https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview?view=azuresql&tabs=azure-portal#service-managed-transparent-data-encryption

Does this mean the restore process would not work if the destination server has a different certificate, or the certificate has been rotated in the interim time between the backup and restore operations?

Would switching to customer managed TDE be necessary to ensure our backups can be reliably decrypted?

Though our testing of this backup/restore process has been successful, we just need to verify that this process is officially supported by Azure.

Answer 1

Hi Tyson L •,

Welcome to Microsoft Q&A forum.

As I understand, you have a Backup and restore managed instance from .bak for Azure SQL Managed Instance.

Taking manual COPY-ONLY backup of a database encrypted by service-managed TDE is not supported in Azure SQL Managed Instance, since the certificate used for encryption is not accessible. Use point-in-time-restore feature to move this type of database to another SQL Managed Instance, or switch to customer-managed key.

You can consider below:

Customer-managed TDE is also referred to as Bring Your Own Key (BYOK) support for TDE. In this scenario, the TDE Protector that encrypts the DEK is a customer-managed asymmetric key, which is stored in a customer-owned and managed Azure Key Vault (Azure's cloud-based external key management system) and never leaves the key vault. The TDE Protector can be generated by the key vault or transferred to the key vault from an on-premises hardware security module (HSM) device. SQL Database, SQL Managed Instance, and Azure Synapse need to be granted permissions to the customer-owned key vault to decrypt and encrypt the DEK. If permissions of the server to the key vault are revoked, a database will be inaccessible, and all data is encrypted.

With TDE with Azure Key Vault integration, users can control key management tasks including key rotations, key vault permissions, key backups, and enable auditing/reporting on all TDE protectors using Azure Key Vault functionality. Key Vault provides central key management, leverages tightly monitored HSMs, and enables separation of duties between management of keys and data to help meet compliance with security policies. To learn more about BYOK for Azure SQL Database and Azure Synapse, see Transparent data encryption with Azure Key Vault integration.

To start using TDE with Azure Key Vault integration, see the how-to guide Turn on transparent data encryption by using your own key from Key Vault.

For more deeper understanding of your case and assistance, I would recommend you to raise support case.

Let me know if you face any blocker in doing the same.

Thanks.