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

  1. Download the Policy Agent on the alternate VM.

    For details, see Installation Requirements for Microsoft SQL Server TDE .

  2. Install the Policy Agent on the alternate VM.

    Note: Register the alternate VM to the same VMSet as the original VM.

  3. Register the alternate SQL Server VM (for example, name win-backup) with KeyControl.

Configure EKM on the SQL Server

  1. Connect with the SQL Server.

  2. Configure Extensible Key Management.

  3. Create Cryptographic Provider.

For details, see Configuring Microsoft SQL Server for EKM .

Enable TDE on SQL Server

Enable TDE on the alternate SQL Server VM, so that it can be used for TDE.

For details, see Installation Requirements for Microsoft SQL Server TDE .Configuring Microsoft SQL Server for EKM .

Configure the KeyControl Database Connector

  1. 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 a KeyControl Database Connector .

  2. 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 ).

  3. Click on Actions > Create Connector.

  4. Enter the VM Name of the alternate SQL Server VM and Connector Name.

    The new connector name should be called something similar to tde_connect_backup.

  5. Navigate to the Key Sets page and check that the database connector is created correctly. The state of the database connector should be ENABLED.

Cryptographic Provider Setup on SQL Server

  1. Select the database connector that you just created and select Actions > Generate Access Token.

  2. Copy the access token to the SQL server and use SQL commands to create “Cryptographic Provider”

  3. Create a credential using the copied Access Token (Identity and Secret) in your SQL Studio Server that will be used by the system administrators.

    Copy 
    CREATE CREDENTIAL sa_ekm_tde_cred
    WITH IDENTITY = 'tde_connect_backup',
    SECRET = '<Secret Access Key>'
    FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
    GO

  4. Add the credential to privileged user with domain login in the format [DOMAIN\login].

    Copy 
    ALTER LOGIN [DOMAIN\login]
    ADD CREDENTIAL "sa_ekm_tde_cred";
    GO

Configure the TDE Key in Microsoft SQL Server

  1. 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.

    Copy 
    USE 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

  2. Check the key is created and has correct key thumbprint.

    Copy 
    USE master;
    select name,thumbprint,algorithm_desc from sys.asymmetric_keys
    GO

Alternatively, you can open old versions of the TDE key by using its thumbprint. You will need to specify the key name starting with @ followed by the thumbprint of the key. For example: 

Copy 
USE master;
CREATE ASYMMETRIC KEY TDE_KEY_v1 -- Give the master key version a name in SQL Server
FROM PROVIDER EKM_Prov WITH
PROVIDER_KEY_NAME = '@E5196A7752214754934DF7BD479B6F7E',   -- master key thumbprint
CREATION_DISPOSITION = OPEN_EXISTING;
GO

Restore Database from Backup File

  1. Copy the backup file on the new SQL Server where you want to restore the database.

  2. Use the following set of SQL statements to restore the database.

    Copy 
    USE [master] 
    RESTORE DATABASE testdb FROM DISK = N'D:\testdb.bak' WITH FILE = 1, NOUNLOAD, STATS = 5

  3. Create another credential and a login for TDE. Add the credential to the login.

    Copy 
    CREATE 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