How-to restore across different SQL Managed instances when using TDE with Customer Managed Keys

Microsoft

Mar 29, 2022

This how-to post focuses on how-to restore across different SQL Managed instances when using TDE with Customer Managed Keys.

You can find and reference the whole series with the following link http://aka.ms/sqlmi-howto.

Transparent Data Encryption (TDE) is an encryption at rest technology that has been in use with SQL Server for many releases. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.

TDE encrypts the storage of an entire database by using a symmetric key called the Database Encryption Key (DEK). DEK is protected by the TDE protector. This DEK is protected by the TDE protector which is either a service managed key or the customer managed key in Azure Key Vault

For Azure SQL Managed Instance, TDE is on by default for the instance, which means all databases created for the instance are enabled by TDE.

By default, Azure SQL Managed Instance uses a service managed key, which means Azure SQL manages a certificate for the key (rotates the key and protects it with a root key within Azure).

Another option is to use TDE with Customer managed Keys. TDE with Customer managed Keys uses Azure Key Vault, which provides highly available and scalable secure storage for RSA cryptographic keys backed by FIPS 140-2 Level 2 validated hardware security modules (HSMs), while Managed HSM offering provides the Level 3. Azure Key Vault streamlines the key management process and enables customers to maintain full control of encryption keys, including managing and auditing key access.

Customer-managed TDE provides the following benefits to the customer:

Full and granular control over usage and management of the TDE protector. (TDE Protector is the key used for encryption of the Database Encryption Key (DEK) and stored in the Key vault.)
Transparency of the TDE protector usage.
Ability to implement separation of duties in the management of keys and data within the organization.
Key Vault administrator can revoke key access permissions to make encrypted database inaccessible.
Central management of keys in AKV.
Greater trust from your end customers, since AKV is designed such that Microsoft can't see nor extract encryption keys’
Ability to backup and restore databases using COPY_ONLY backups for Managed Instance.

Despite the management overhead, because of the above advantages, most customers prefer to use Customer Managed Keys.

Now that we understand the advantages of using TDE with customer managed keys, let us look at the process of doing a manual backup and restore across managed instances when using TDE with customer managed keys. There are a couple of scenarios where customer might need to do this:

When needing a backup that needs to be held > 35 days
When needing a database refresh from one environment to another (Example: Production to Development or vice versa)

How-to setup restore across different SQL Managed instances when using TDE with Customer Managed Keys

In the example below, we are restoring a database from the source SQL Managed Instance [vyelsqlmi1] to the target SQL Managed Instance [vyelsqlmi2].

We have used our Azure subscription to create a new Azure Key Vault and added a key to it. Then we have navigated to the SQL Managed Instance [vyelsqlmi1], selected Transparent Data Encryption blade from the Resource Menu, and selected Customer-managed key as shown below:

We then selected Change key option and picked our Azure Key Vault (vyelazurekeyvault) and the respective key (vyelsqlmikey) as shown on the image below:

After choosing Select and then Save options on the next screen, within a few seconds our key is now enabled as the TDE protector for our SQL Managed Instance.

Since we have enabled the option “Make this key the default TDE protector”, all the databases on the Managed Instance [vyelsqlmi1] have been encrypted with the TDE protector - vyelsqlmikey. So, all the backups taken on this instance will be encrypted with this TDE protector.

Though enabling this feature is very easy, there are considerations and recommendations that one needs to follow when using customer managed Keys with Azure SQL Managed Instance. The detailed documentation on this topic is to be found at Customer-managed transparent data encryption (TDE) - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics.

To restore a backup encrypted with a TDE Protector from Key Vault, we need to make sure that the key material is still in the original vault under the original key name. Also, the target instance needs to have access to the TDE protector from the source instance.

In this example, we intend to restore a database protected with a particular key, but we do not wish to encrypt the databases on the server with that key. So, you need to add that key on the target instance, but don't need to check the checkbox at the bottom of the blade – ‘Make this key the default TDE protector’. That way, key can be used for restoring on the target instance, without being used as a default protector.

Navigate to the SQL Managed Instance [vyelsqlmi2] in the Azure Portal, select Transparent data encryption blade from the Resource Menu, and selected Customer-managed key as shown below.

Make sure that the setting - 'Make this key the default TDE protector' is disabled. That way the key can be used only for restore on the target instance, without being used as a TDE protector after the restore.

After selecting Change key option & our Azure key Vault (vyelazurekeyvault) and respective key (vyelsqlmikey) used by the source SQL Managed Instance [vyelsqlmi1]:

We chose Select and then Save options on the next screen.

With this action we have configured the necessary processes for decrypting the backups for restoring on the secondary SQL Managed Instance [vyelsqlmi2].

Key Vault Access Policies

Checking the access policies for the Azure Key Vault, you can see that both the SQL Managed Instances have permissions to access the key vault. They have the necessary 3 key permissions – Get, Wrap Key and Unwrap Key, which serve for the key access, encryption and decryption respectively.

List of available keys on the Managed Instance

Executing Powershell comandlet Get-AzSqlInstanceKeyVaultKey  on the SQL Managed Instance will list of available keys associated with that managed instance and identify the missing ones.  If one needs to restore a database to a SQL Managed Instance, then the key which was used as part of the backup must be added to the managed instance.

Running the command on [vyelsqlmi1] listed three keys associated with it.

Note: Azure Portal only shows only the last key assigned. To get the complete list, PowerShell is the way to go.

Backup and Restore

Now, we are ready to do a backup and restore as we have ensured that both the source and target SQL Manages Instances have access to the key in our Azure Key Vault. SQL Managed instance supports only full database COPY_ONLY Backups and backing up to an Azure Blob Storage account.

Considerations and Limitations of Manual backups on a managed instance can be found in our official documentation - T-SQL differences between SQL Server & Azure SQL Managed Instance - Azure SQL Managed Instance

At a high level, the steps to do a backup and restore are the following

Create a container on Azure Blob Storage to store the backups.
On the source Managed Instance, create a SAS credential to access the container and then back up a database to it
On the target Managed Instance (MI), create a SAS credential to access the container, and then restore the backup from it

The following article explains the process to create manual backups on Azure SQL Managed instance - Database user-initiated backups. Azure SQL Managed Instance

Following the above described process we have created a backup of a database on [vyelsqlmi1] to the Azure Blob Storage container- mibackups as shown below:

For the restore on to the target managed instance -[vyelsqlmi2], please ensure that you follow the process stated in our quickstart in our documentation Restore a backup (SSMS) - Azure SQL Managed Instance.