Hello Dear Reader! I was working with some friends lately and we needed to set up a process to refresh their Development Environment databases from Production. Additionally, the databases are encrypted using Transparent Data Encryption, The SQL MI instances are in different regions, and the SQL MI Instances are in different subscriptions. To duplicate the environment, in order to match our friends, we did the following setup.
- We created a virtual machine and a database named TDE encrypted with a self signed certificate
- We exported the Certificate & Private Key and used pvk2pfx to create a pfx file and uploaded this to our SQL MI Instances, see Microsoft Doc’s article Migrate a certificate of a TDE-protected database to Azure SQL Managed Insta...
- Backup and Restore the TDE database from our VM to the SQL MI Instances
- Create an Azure Key Vault in our development subscription and our production subscription
This gives us the following environment. We have our SQL Managed Instances in two different regions with our TDE databases restored using the self-signed certificate.
Remember our goal is to be able to perform cross subscription restores and still keep our databases encrypted. To do that we will perform the next 10 steps.
- Create a TDE Key in the Development Azure Key Vault
- Backup the Key
- Create a new “2-b-moved” Azure Key Vault
- Restore the Key to the new “2-b-moved” Azure Key Vault
- Migrate the new “2-b-moved” Azure Key Vault from the development subscription to the production subscription
- Backup the key from the new “2-b-moved” Azure Key Vault now in the production subscription
- Restore the Key to the production Azure Key Vault
- Associate both SQL Managed Instances, Production & Development, with the Key’s in their respective Azure Key Vaults
- Create a T-SQL Credential on each Azure SQL Managed Instances to an Azure Blob Storage account
- Backup the TDE database from the production SQL Managed Instance and restore it to the development SQL Managed Instance
Here is a flow chart of these activities.
Azure Key Vault Steps 1 – 7
One of the things we have to do is make sure that Production & Development can access the same Keys for TDE. This is because TDE encrypts the database and places a thumbprint of the Key in the header file of the database. Without access to the key the database will not come online. If we are looking to do a “prod refresh”, refreshing the development environment with a current copy of the production database, then we will need the keys to be accessible to both instances.
“But Brad”, you say, “Why can’t I just backup the keys from the Development Key Vault and restore them to the Production Key Vault?”.
Alas Dear Reader, there is a dependency on the subscription for backing up and restoring a Key Vault Key. If you attempt to restore a Key from one subscription to another you will get this error.
So we start at step 1. Create the TDE Key
We start by opening our Azure Portal and opening our Dev Azure Key Vault.
Under Settings click Keys.
Click + Generate Import. We are not going to go in-depth on all the options for creating a Key. There is one essential piece of advice I will give you. Each Key should be unique, and if you plan on switching out the Keys every year, BUT you have a regulatory compliance that you must keep X number of years of backups on hand then you need to ensure the life of the Key is X number of years.
For Example: Company A wants to switch Keys every year. Company A has a requirement to restore backups that are 10 years old if needed. Therefore every Key that is created should have a minimum of 10 years before they expire.
Because in 5 years if Company A must restore a backup, they will need the active non-expired Key from 5 years ago to be associated with the Azure SQL Managed Instance so they can restore the backup.
*Remember what I said above, thumbprint of the Key is in the header of the database file and the backup, you cannot restore or bring online a database if you do not have the Key with the thumbprint associated with the SQL Managed Instance. ….also Company A needs Long Term Backup Retention.
By default Keys do not have expiration dates or activation dates. You need to select them.
Enter a name for the Key, go with the default of RSA, RSA Key 2048, no active or expiration date for this demo, Enabled Yes, no rotation policy set, click Create.
Now click on the Key we have created. Now onto Step 2.
- Backup the Key
Click on the Download Backup button.
A copy of the key will automatically be downloaded. Now onto Step 3.
- Create New Key Vault
There are multiple ways to create a new Key vault, we will do this by clicking on the Azure Portal Menu and selecting + Create a resource.
Type Key vaults in the text box and click enter.
Click Key Vault.
Click Create.
For the Resource Group name we’ll make a new one named bball-keyvault-test-move. The name will be bball-Keyvault-2-b-moved, the region South Central US, and click Next.
We are going to add an Access Policy for the managed identity of our Azure SQL Managed instance, bball-tde-test. To do this we will click on +Add Access Policy.
Select Key, Secret, & Certificate Management in the drop down menu.
Click None selected and type in the name of the service, select the managed identity, and click add, then Add again.
There may be some additional Networking or Tag addition that you need to make. For the purposes of this walk through we can move straight to review + create.
Then click Create. After the Key vault is created click on Go to resource.
Click on Keys.
We are ready for step 4.
- Restore Key Backup
Click Restore Backup.
Browse to where you saved your Key backup from step 2 and select the Key backup. Click Open. Now you should see the Key restored. If you click on the key and look at the CURRENT VERSION they should match.
The Name should match the backed up Key name, and the status should be enabled. Now click on Overview because it is time for step 5.
- Move Key Vault bball-Keyvault-2-b-moved to Prod Subscription
Click the -> Move drop down and select Move to another subscription.
Select the subscription and the Resource group to which you would like to move the Key Vault, then click Next.
At this point you will see a screen that says, “checking whether these resources can be moved. This might take a few minutes.”. And it will take a few minutes, validating that it can move the Key vault.
Once this finishes you will get a green check and the Next button will become blue. Click Next.
Check the check box next to the statement, “I understand that tools and scripts associated with this moved resources will not work until I update them to use new resource IDs”. Click Move.
This will take you back to the overview page. Wait until the notifications alerts you that the Key vault has successfully moved subscriptions. This takes us to Step 6.
- Backup Key
Now that we have moved our Key vault to our production subscription we will backup the Key so we can restore it to our production Key vault. Go to your production subscription, and to the Key vault we’ve just moved. Just like step 2 go to Keys and click on bball-demo-tde-key.
Click Download Backup.
This takes us to step 7.
- Restore Key Backup
Like step 4 we are restoring the Key Backup, but this time we are doing it to our Production Key vault bball-keyvault-useast. Navigate to our Production Key vault. Click on Keys and Click Restore Backup.
Browse to the backup of the key and click Open. You should see your key restored inside of our Production Key vault.
Azure SQL Managed Instance Steps 8-10
Now we are ready for Step 8, where we will associate both keys that we have created with their respective Azure SQL Managed Instances in Dev and Prod. Remember the goal is to have the same key in the backup, so we can perform our prod refresh into our development environment.
- Associate Key with SQL MI Prod & SQL MI Dev
We will perform these actions in both our Production and Development SQL Managed Instances at the same time. Open your portal blades to the SQL Managed Instances. On the SQL Managed Instance blade click on Transparent Data Encryption under Security.
We need to change the setting from Service-managed key to Customer-managed key. A quick note on these settings. If you use Service-managed keys and your database is encrypted, you cannot create a Copy Only Backup. It will fail. So we need to change this so we can do our prod refresh of our dev environment.
Click on the link that says Change key.
Select Key vault, the Key vault for the environment, the Key we created bball-demo-tde-key, and click Select.
Back on the Transparent Data Encryption screen click Save for both instances.
Once the operation completes we are ready to move on to Step 9.
- Create Credential with SAS token to Azure blob storage
In Each SQL Managed Instance we will execute the following script.
CREATE CREDENTIAL [https://bballstorage.blob.core.windows.net/sqlmitcpbackups]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
,SECRET='SAS TOKEN without the ?';
In order to get the SAS Token you will go to the storage account that you would like to use, user Security + networking select Shared access signature.
I’m not going into setting the expiration date of the SAS token. You will want to discuss this with your team, follow any polices your organization has, in order to determine what expiration date should be. Mine will literally be for a few minutes, because that’s all I need for this blog.
Copy the SAS token. Remember to remove the question mark after pasting it into the script from above. Now run this script on both SQL Managed Instances in order to create a connection to the storage account.
This takes us to our final step, Step 10!
- Backup and Restore TDE.bak to bball-tde-test
This is the moment we’ve been waiting for, time to complete our prod refresh of dev. We will start by running a backup script on bball-sqlmi-useast to our Azure blob storage account.
Using Azure Storage Explorer I can validate the backup is in the storage account.
Now we perform the Restore to dev.
And we have completed our prod refresh!
Clean Up
Earlier we spoke about the need to rotate Keys. At this point we can delete the Azure Key Vault 'bball-keyvault-2-b-moved'. In a year, quarter, or whatever your timeframe is to rotate keys you can repeate all the steps to switch over to new keys and make sure you can still perform prod refreshes to your dev environment.
Alright Dear Reader, I hope this is helpful. As always Thank You for stopping by!
Thanks,
Brad