Transparent Data Encryption (TDE) with a Customer Provided Key, Azure SQL Managed Instance (MI), and Failover Groups should all be easy to setup. Azure SQL MI with either of the other two is pretty straightforward to setup. All three of them at the same time require a little doing in order to get them all working correctly together. This is because you as the admin need to manually move the key which TDE is using to encrypt the databases to the secondary region manually, and you need to tell the Azure SQL MI in the secondary region where to find the key.
Having a customer-managed key for TDE requires some special setup to ensure that the key is available to both instances. This is done by placing the key within Azure Key Vault. In order to set up a failover group you need to have not just the Azure Key Vault in the primary site, but you also need to set up one in the secondary site which will be hosting your Azure SQL MI.
Once you have an Azure Key Vault in both sites, you can use the GUI to export the key from the primary site and restore it to the Azure Key Vault in the secondary site. After that is done you’ll need to give the Azure SQL MI in the secondary site access to the key, which is done through the Access Policies section of Azure Key Vault. The managed instance needs three permissions to access keys; which are Get, Unwrap Key, and Wrap Key. No other rights need to be granted to the MI.
To grant rights to the Managed Instance click the Add Access Policy link.
Then select the three rights needed in the Key Permissions drop down.
Then click on “None Selected” next to “Select principal”. This will bring up a list of all users and applications which are registered with Azure Active Directory. Find the Managed Instance for the secondary environment and click “Select” at the bottom of the screen, then click “Add” on the prior screen (where you selected the permissions).
Once this is done, go into the setting for the Managed Instance in the secondary site, and select Transparent data encryption from the menu. On this screen change the setting from “Service-managed Key” to “Customer-managed Key”. Then either select a key or enter a key identifier of the key which will be used for new databases from the key vault (if all your databases are protected with one key, just select this key).
Save these settings changes and edit the properties of your Azure SQL MI in the primary site. On this server select Failover Groups and click the “Add Group” button at the top of the blade that opens. Select all the correct information for the secondary instance and the group name and save the settings.
It’ll take a while, but when it is done the map should look something like this (depending on what regions you selected of course). Once you’ve gotten to this point you’ve set up your Azure SQL Managed Instance using TDE with a Customer-managed Key and a Failover group.
You should have a beer at this point, you’ve earned it.
Denny
