I need to be able restore Azure SQL Server databases from one subscription to another (example: Prod to Dev environments that exist in different subscriptions). The databases will have TDE enabled. What is the implication with respect to TDE for the following two scenarios:
- Azure SQL databases (PaaS)
- SQL databases hosted on Azure VMS (IaaS)
TDE is enabled by default for Azure SQL databases. We plan to enable TDE on the SQL databases hosted on Azure VMs.
Will there be any issues? If any issues, how can we mitigate such that the data in the restored data is accessible in the form that it was in on the source servers (and is not in some encrypted state that is not useable).
Similarly, what is the implication with respect to PaaS and IaaS databases that have TDE enabled with DR scenarios where data needs to be accessed in a secondary region.
Thank you.
CodePudding user response：
To restore a TDE-encrypted database to another SQL Server, you need to first restore the certificate to the destination server.
When moving a TDE protected database, you must also move the certificate or asymmetric key that is used to open the DEK. The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files. For more information, see Transparent Data Encryption (TDE).
You must retain copies of both the certificate file and the private key file in order to recover the certificate. The password for the private key does not have to be the same as the database master key password.
To restore, you need the following permissions:
- Backup Operator permissions in the vault where you're doing the restore.
- Contributor (write) access to the source VM that's backed up.
- Contributor (write) access to the target VM:
- If you're restoring to the same VM, this is the source VM.
- If you're restoring to an alternate location, this is the new target VM.
For more details and step-by-step implementation, refer Restore SQL Server databases on Azure VMs