Cross Subscription Prod Refresh on SQL Managed Instance using TDE and Azure Key Vault

Published Jun 16 2022 08:05 AM 995 Views

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.

 

  1. We created a virtual machine and a database named TDE encrypted with a self signed certificate
  2. 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...
  3. Backup and Restore the TDE database from our VM to the SQL MI Instances
  4. 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.

 

BradleyBallMICROSOFT_0-1655390506808.png

 

 

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.

 

  1. Create a TDE Key in the Development Azure Key Vault
  2. Backup the Key
  3. Create a new “2-b-moved” Azure Key Vault
  4. Restore the Key to the new “2-b-moved” Azure Key Vault
  5. Migrate the new “2-b-moved” Azure Key Vault from the development subscription to the production subscription
  6. Backup the key from the new “2-b-moved” Azure Key Vault now in the production subscription
  7. Restore the Key to the production Azure Key Vault
  8. Associate both SQL Managed Instances, Production & Development, with the Key’s in their respective Azure Key Vaults
  9. Create a T-SQL Credential on each Azure SQL Managed Instances to an Azure Blob Storage account
  10. 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.

BradleyBallMICROSOFT_1-1655390506824.png

 

 

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.

BradleyBallMICROSOFT_2-1655390506826.png

 

 

So we start at step 1. Create the TDE Key

BradleyBallMICROSOFT_3-1655390506843.png

 

 

We start by opening our Azure Portal and opening our Dev Azure Key Vault.

BradleyBallMICROSOFT_4-1655390506845.png

 

Under Settings click Keys.

BradleyBallMICROSOFT_5-1655390506847.png

 

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.

BradleyBallMICROSOFT_6-1655390506851.png

 

 

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.

 

BradleyBallMICROSOFT_7-1655390506852.png

 

Now click on the Key we have created.  Now onto Step 2.

 

  1. Backup the Key

BradleyBallMICROSOFT_8-1655390506877.png

 

Click on the Download Backup button.

BradleyBallMICROSOFT_9-1655390506879.png

 

A copy of the key will automatically be downloaded.  Now onto Step 3.

  1. Create New Key Vault

BradleyBallMICROSOFT_10-1655390506905.png

 

 

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.

 

BradleyBallMICROSOFT_11-1655390506906.png

 

Type Key vaults in the text box and click enter.

BradleyBallMICROSOFT_12-1655390506907.png

 

Click Key Vault.

BradleyBallMICROSOFT_13-1655390506909.png

 

Click Create.

BradleyBallMICROSOFT_14-1655390506910.png

 

 

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.

 

BradleyBallMICROSOFT_15-1655390506917.png

 

 

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.

BradleyBallMICROSOFT_16-1655390506921.png

 

 

Select Key, Secret, & Certificate Management in the drop down menu. 

BradleyBallMICROSOFT_17-1655390506924.png

 

 

Click None selected and type in the name of the service, select the managed identity, and click add, then Add again.

BradleyBallMICROSOFT_18-1655390506925.png

 

 

BradleyBallMICROSOFT_19-1655390506928.png

 

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.

BradleyBallMICROSOFT_20-1655390506931.png

 

Then click Create.  After the Key vault is created click on Go to resource.

 

 

 

BradleyBallMICROSOFT_21-1655390506934.png

 

Click on Keys.   

BradleyBallMICROSOFT_22-1655390506935.png

 

We are ready for step 4.

  1. Restore Key Backup

BradleyBallMICROSOFT_23-1655390506951.png

 

 

Click Restore Backup.

BradleyBallMICROSOFT_24-1655390506954.png

 

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.

 

BradleyBallMICROSOFT_25-1655390506957.png

 

 

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.

 

  1. Move Key Vault bball-Keyvault-2-b-moved to Prod Subscription

 

BradleyBallMICROSOFT_26-1655390506971.png

 

 

Click the -> Move drop down and select Move to another subscription.

 

BradleyBallMICROSOFT_27-1655390506974.png

 

 

Select the subscription and the Resource group to which you would like to move the Key Vault, then click Next.

BradleyBallMICROSOFT_28-1655390506978.png

 

 

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.

BradleyBallMICROSOFT_29-1655390506981.png

 

Once this finishes you will get a green check and the Next button will become blue.  Click Next.

 

BradleyBallMICROSOFT_30-1655390506985.png

 

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.

 

  1. Backup Key

BradleyBallMICROSOFT_31-1655390507008.png

 

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.

 

BradleyBallMICROSOFT_32-1655390507010.png

 

 

Click Download Backup.

BradleyBallMICROSOFT_33-1655390507012.png

 

 

This takes us to step 7.

  1. Restore Key Backup

 

BradleyBallMICROSOFT_34-1655390507033.png

 

 

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.

BradleyBallMICROSOFT_35-1655390507036.png

 

Browse to the backup of the key and click Open.  You should see your key restored inside of our Production Key vault.

BradleyBallMICROSOFT_36-1655390507039.png

 

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.

  1. Associate Key with SQL MI Prod & SQL MI Dev

BradleyBallMICROSOFT_37-1655390507053.png

 

 

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.

BradleyBallMICROSOFT_38-1655390507058.png

 

BradleyBallMICROSOFT_39-1655390507062.png

 

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.

 

BradleyBallMICROSOFT_40-1655390507065.png

 

 

Click on the link that says Change key.

BradleyBallMICROSOFT_41-1655390507070.png

 

Select Key vault, the Key vault for the environment, the Key we created bball-demo-tde-key, and click Select.

BradleyBallMICROSOFT_42-1655390507072.png

 

 

BradleyBallMICROSOFT_43-1655390507075.png

 

 

Back on the Transparent Data Encryption screen click Save for both instances.

BradleyBallMICROSOFT_44-1655390507079.png

 

Once the operation completes we are ready to move on to Step 9.

 

BradleyBallMICROSOFT_45-1655390507093.png

 

 

  1. 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 ?';

 

 

 

BradleyBallMICROSOFT_46-1655390507096.png

 

 

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.

BradleyBallMICROSOFT_47-1655390507098.png

 

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.

BradleyBallMICROSOFT_48-1655390507106.png

 

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.

BradleyBallMICROSOFT_49-1655390507110.png

 

This takes us to our final step, Step 10!

  1. Backup and Restore TDE.bak to bball-tde-test

BradleyBallMICROSOFT_50-1655390507124.png

 

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.

BradleyBallMICROSOFT_51-1655390507128.png

 

Using Azure Storage Explorer I can validate the backup is in the storage account.

BradleyBallMICROSOFT_52-1655390507130.png

 

Now we perform the Restore to dev.

BradleyBallMICROSOFT_53-1655390507133.png

 

 

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

 

 

Version history
Last update:
‎Jun 16 2022 08:02 AM