Restore TDE Encrypted Database on an Alternate (Failover) VM
This topic explains how to restore a TDE encrypted database from an alternate/failover VM.
Note: To restore a TDE encrypted database on the same VM, see Backup and Restore TDE Encrypted Database .
To restore the backup database on an alternate SQL Server VM, you have to make the keys accessible on this VM in addition to the original VM.
The process to set up the alternate VM is very similar to configuring the original VM with minor exceptions. For example, you do need to create the VM Set and KeySet.
The following details the required steps and the differences regarding setting an alternate VM.
Install and configure the Policy Agent on the Alternate VM
-
Download the Policy Agent on the alternate VM.
For details, see Install and register the Entrust Policy Agent .
-
Install the Policy Agent on the alternate VM.
Note: Register the alternate VM to the same VMSet as the original VM.
-
Register the alternate SQL Server VM (for example, name win-backup) with KeyControl.
Configure EKM on the SQL Server
-
Connect with the SQL Server.
-
Configure Extensible Key Management.
-
Create Cryptographic Provider.
For details, see Configure EKM on the SQL Server .
Enable TDE on SQL Server
Enable TDE on the alternate SQL Server VM, so that it can be used for TDE.
For details, see Enable TDE on SQL Server .Configure EKM on the SQL Server .
Configure the KeyControl Database Connector
-
Navigate to the CLOUD KEYS > KeySet tab.
Note: Navigate to the same KeySet used to create keys for the original TDE setup.
For details, see Configuring KeyControl for TDE .
-
Create a Database Connector for this KeySet and the alternate SQL Server VM registered (here Install and configure the Policy Agent on the Alternate VM ). Click on Actions > Create Connector.
-
Enter the Name of the alternate SQL Server VM and Connector Name.
Note the new connector name in the example is tde_connect_backup.
-
Check the database connector is created correctly.
Cryptographic Provider Setup on SQL Server
-
Select the required database connector and select Actions > Generate Access Token.
-
Copy the access token to the SQL server and use SQL commands to create “Cryptographic Provider”
-
Create a credential using the copied Access Token (Identity and Secret) in your SQL Studio Server that will be used by the system administrators.
CopyCREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'tde_connect_backup',
SECRET = '<Secret Access Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO -
Add the credential to privileged user with domain login in the format [DOMAIN\login].
CopyALTER LOGIN [DOMAIN\login]
ADD CREDENTIAL "sa_ekm_tde_cred";
GO
Configure the TDE Key in Microsoft SQL Server
-
Create master key (KEK) using the name of the key generated during database encryption setup and the provider created (here, Configure EKM on the SQL Server ).
Note: The PROVIDER_KEY_NAME used for restore should be the same as the key used for encryption on the original SQL Server VM.
CopyUSE master;
CREATE ASYMMETRIC KEY TDE_KEY -- Give the KEK a name in SQL Server
FROM PROVIDER EKM_Prov
WITH PROVIDER_KEY_NAME = '<Cloud Key>', -- master_key1 created in last Step
CREATION_DISPOSITION = OPEN_EXISTING;
GO -
Check the key is created and has correct key thumbprint.
CopyUSE master;
select name,thumbprint,algorithm_desc from sys.asymmetric_keys
GO
Restore Database from Backup File
-
Copy the backup file on the new SQL Server where you want to restore the database.
-
Use the following set of SQL statements to restore the database.
CopyUSE [master]
RESTORE DATABASE testdb FROM DISK = N'D:\testdb.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 -
Create another credential and a login for TDE. Add the credential to the login.
CopyCREATE CREDENTIAL tde_ekm_cred
WITH IDENTITY = 'tde_connect_backup',
SECRET = 'xdZ5TCNRLcldfeCr8SDZyu+TJF1kH5MGMZgaj2vZeUgP3Nf2xBh/lWicDzhvaWZCN3w3A5gVlkgG05AnfdlIYafP6HE+z SqsGXAy4Fx7deZYqTbOcz3HOYmu99IE81fATfG58C1qukmzY9UW3z04p3TaAM2xho30SIhm2hs1iupsXADGBuSVYSxi5C8 aH26jrXZFl6hIiInwK8F3GF3MQSWksRmgruAOSBLfnh+iasM1vqkZ/BHZ/3vRcsZZTrC3DycHXGrZqwcVxZaPsQlok2vC0 cRw6m7afBCJI76y854kIGEflOCvQugGxxyEBpPOOuLrCEQo0y5jr9x2dYgWIr4X24qTnW9+BVMlMqGh2oOmqBqqNFBVUBE UjSIjHZuE/4xo/lH6aknRu0PHP2JDhvDGpouRfCaJ7/5q1dygiEl1OcSoANemqu44GUaVyAS1esIM182T6rNbbmRgRJsyq znrdTLx02oUPbtbUXRUdh7hgYGPBB+uvid2YYT+NOsXM2nR0XdkxJ59x2RbFErjTGuf6mj756qE2RBzV9eeIEjMQgiajfT UNWz/NTk+Ca5z6Rdznig8VTCLR0Q3iHxF6zb5jSYkLeo0pbeUuuSLtlPX7kkrzFt07j2dj8q3V2lkcvYLOMdgtzzW/ILGj S0jEyZBbyROxK+48WSQ244EVhs='
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
CREATE LOGIN TDE_Login
FROM ASYMMETRIC KEY TDE_KEY ;
GO
ALTER LOGIN TDE_Login
ADD CREDENTIAL tde_ekm_cred
GO