Restore a DB encrypted with a TDE key into a server/instance having a different encryption key

Published Oct 23 2020 09:55 AM 2,136 Views
Microsoft

If you need to restore a database that was encrypted with a TDE (transparent data encryption) customer-managed key into a server that is encrypted with a different key, you can follow the steps below:

 

  1. If necessary, restore the TDE customer-managed key that was used to encrypt the database you will restore into the Azure Key Vault (AKV) you intend to use.

 

  1. On the “Transparent data encryption” blade of the target server/instance, click on “change key” and select the key with which the source backup was encrypted:

 

Thamires_Lemes_0-1603471538658.jpeg

 

  1. Uncheck the “Make the selected key the default TDE protector” and save. By unchecking the key as TDE Protector, you will add the key to the server/instance without changing the encryption key of Its databases:

 

Thamires_Lemes_1-1603471538664.jpeg

Note: They key was changed from thlemes-sqldb-k to thlemes-key2

 

  1. After the operation completes, you will see the key you selected in ‘Key’, but with the ‘Make the selected key the default TDE protector’ unchecked:

 

Thamires_Lemes_2-1603471538676.jpeg

 

  1. However, if you refresh the page, It will show again the TDE Protector key:

 

Thamires_Lemes_3-1603471538679.jpeg

 

This is because the TDE Protector key wasn’t changed and It’s shown by default in the Portal. However, after adding the source key as non-TDE Protector, you will be able to successfully perform the restore from the source backup into the target server/instance.

 

Although you can’t see the keys that are not the TDE Protector in the Portal, you can list them using REST API:

 

SQL Managed Instance: https://docs.microsoft.com/en-us/rest/api/sql/managedinstancekeys

SQL Server: https://docs.microsoft.com/en-us/rest/api/sql/serverkeys

 

There is also a REST API command to delete the key if you need to, as you can see in the links above.

 

1 Comment
Regular Visitor

Ok, this is for Azure SQL. Using an on-premise SQL server on a VM in Azure I have to recreate the chain, Mapped AsyKey -> DEK, using a credential/login and set the thumbprint of the mapped key to the same value as the source SQL Server that performed the backup with the Key (I obviously must have this key in vault).

My question...

In the vault the function "New Version" of the key exists. It sets a new version number and marks it as "current version" and the older as "older version". You can decide to set them enabled or disabled. If I encrypt a DB using a version and do some bakups and then I create a new version of the key I can recrypt the DEK using new version and this works but I cannot restore any backup made with older version of the key also if it is enabled. I cannot restore them even if I set enable only the "version" used to make the backups. SQL returns me an error in the encryption. I'd like to know if this "New version" function is supported by SQL IaaS in order to rotate the keys or if I must necessarily create a new key every time I need to rotate and preserve the older until I could have need it for my backups.    

%3CLINGO-SUB%20id%3D%22lingo-sub-1813286%22%20slang%3D%22en-US%22%3ERestore%20a%20DB%20encrypted%20with%20a%20TDE%20key%20into%20a%20server%2Finstance%20having%20a%20different%20encryption%20key%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1813286%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20need%20to%20restore%20a%20database%20that%20was%20encrypted%20with%20a%20TDE%20(transparent%20data%20encryption)%20customer-managed%20key%20into%20a%20server%20that%20is%20encrypted%20with%20a%20different%20key%2C%20you%20can%20follow%20the%20steps%20below%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EIf%20necessary%2C%20restore%20the%20TDE%20customer-managed%20key%20that%20was%20used%20to%20encrypt%20the%20database%20you%20will%20restore%20into%20the%20Azure%20Key%20Vault%20(AKV)%20you%20intend%20to%20use.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%222%22%3E%0A%3CLI%3EOn%20the%20%E2%80%9CTransparent%20data%20encryption%E2%80%9D%20blade%20of%20the%20target%20server%2Finstance%2C%20click%20on%20%E2%80%9Cchange%20key%E2%80%9D%20and%20select%20the%20key%20with%20which%20the%20source%20backup%20was%20encrypted%3A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Thamires_Lemes_0-1603471538658.jpeg%22%20style%3D%22width%3A%20651px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228771i742A43456DE60912%2Fimage-dimensions%2F651x285%3Fv%3D1.0%22%20width%3D%22651%22%20height%3D%22285%22%20role%3D%22button%22%20title%3D%22Thamires_Lemes_0-1603471538658.jpeg%22%20alt%3D%22Thamires_Lemes_0-1603471538658.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%223%22%3E%0A%3CLI%3EUncheck%20the%20%E2%80%9CMake%20the%20selected%20key%20the%20default%20TDE%20protector%E2%80%9D%20and%20save.%20By%20unchecking%20the%20key%20as%20TDE%20Protector%2C%20you%20will%20add%20the%20key%20to%20the%20server%2Finstance%20without%20changing%20the%20encryption%20key%20of%20Its%20databases%3A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Thamires_Lemes_1-1603471538664.jpeg%22%20style%3D%22width%3A%20650px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228770i84E823C44B075DB3%2Fimage-dimensions%2F650x284%3Fv%3D1.0%22%20width%3D%22650%22%20height%3D%22284%22%20role%3D%22button%22%20title%3D%22Thamires_Lemes_1-1603471538664.jpeg%22%20alt%3D%22Thamires_Lemes_1-1603471538664.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3ENote%3A%20They%20key%20was%20changed%20from%20thlemes-sqldb-k%20to%20thlemes-key2%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%224%22%3E%0A%3CLI%3EAfter%20the%20operation%20completes%2C%20you%20will%20see%20the%20key%20you%20selected%20in%20%E2%80%98Key%E2%80%99%2C%20but%20with%20the%20%E2%80%98Make%20the%20selected%20key%20the%20default%20TDE%20protector%E2%80%99%20unchecked%3A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Thamires_Lemes_2-1603471538676.jpeg%22%20style%3D%22width%3A%20651px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228772i2B202F562015CC83%2Fimage-dimensions%2F651x206%3Fv%3D1.0%22%20width%3D%22651%22%20height%3D%22206%22%20role%3D%22button%22%20title%3D%22Thamires_Lemes_2-1603471538676.jpeg%22%20alt%3D%22Thamires_Lemes_2-1603471538676.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%225%22%3E%0A%3CLI%3EHowever%2C%20if%20you%20refresh%20the%20page%2C%20It%20will%20show%20again%20the%20TDE%20Protector%20key%3A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Thamires_Lemes_3-1603471538679.jpeg%22%20style%3D%22width%3A%20650px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228773i40E846A70F662EE1%2Fimage-dimensions%2F650x176%3Fv%3D1.0%22%20width%3D%22650%22%20height%3D%22176%22%20role%3D%22button%22%20title%3D%22Thamires_Lemes_3-1603471538679.jpeg%22%20alt%3D%22Thamires_Lemes_3-1603471538679.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20because%20the%20TDE%20Protector%20key%20wasn%E2%80%99t%20changed%20and%20It%E2%80%99s%20shown%20by%20default%20in%20the%20Portal.%20However%2C%20after%20adding%20the%20source%20key%20as%20non-TDE%20Protector%2C%20you%20will%20be%20able%20to%20successfully%20perform%20the%20restore%20from%20the%20source%20backup%20into%20the%20target%20server%2Finstance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlthough%20you%20can%E2%80%99t%20see%20the%20keys%20that%20are%20not%20the%20TDE%20Protector%20in%20the%20Portal%2C%20you%20can%20list%20them%20using%20REST%20API%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Managed%20Instance%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Frest%2Fapi%2Fsql%2Fmanagedinstancekeys%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Frest%2Fapi%2Fsql%2Fmanagedinstancekeys%3C%2FA%3E%3C%2FP%3E%0A%3CP%3ESQL%20Server%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Frest%2Fapi%2Fsql%2Fserverkeys%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Frest%2Fapi%2Fsql%2Fserverkeys%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20is%20also%20a%20REST%20API%20command%20to%20delete%20the%20key%20if%20you%20need%20to%2C%20as%20you%20can%20see%20in%20the%20links%20above.%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1813286%22%20slang%3D%22en-US%22%3E%3CP%3ESteps%20on%20how%20to%20restore%20of%20a%20database%20encrypted%20with%20a%20Transparent%20Data%20Encryption%20(TDE)%20customer-managed%20key%20into%20a%20server%2Finstance%20encrypted%20with%20a%20different%20key.%20This%20works%20for%20both%20Azure%20SQL%20DB%20and%20Azure%20SQL%20Managed%20Instance%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Oct 23 2020 09:55 AM
Updated by: