Restoring Encrypted TDE Database Backups
Here is a list of a few use cases to consider while restoring the encrypted TDE database backups on another machine (secondary node):
Restoring an Encrypted TDE Database Backup
Restoring an Encrypted TDE Database Backup after rotating the DEK and MEK key
Restoring an Encrypted TDE Database Backup after rotating only MEK
Restoring an Encrypted TDE Database Backup after rotating only DEK
Restoring an Encrypted TDE Database Backup with the key version enabled
Restoring an Encrypted TDE Database Backup on increasing the key version
Perform the following steps for all the above use cases:
Execute the below query on the Microsoft SQL Server.
sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'EKM provider enabled', 1; RECONFIGURE;
Note
- Only the users with the sysadmin or serveradmin privileges can execute the
sp_configure
query to change the server configuration. Whereas, the other users can run this command only to view the server configuration. - Before loading the DLL, ensure that
NAE_IP
andLog_File
parameters are configured. You can also configure the other parameters as per your requirement. For more details, refer Configuring Properties File - Only the users with the sysadmin privileges can load the DLL.
- Only the users with the sysadmin or serveradmin privileges can execute the
To load the DLL, execute the following query. This will create a new cryptographic provider named
<provider_name>
. Provider name is visible under Security\Cryptographic Providers in Microsoft SQL Server Management Studio.CREATE CRYPTOGRAPHIC PROVIDER <provider_name> FROM FILE = '<Installation_Directory>\CipherTrust\CAKM For SQLServerEKM\cakm_mssql_ekm.dll'
Note
Alter the file location if you did not accept the default installation directory.
To create a SQL credential, execute the following query. This will create a new credential named
<credential_name>
. The credential is visible under Security\Credentials in Microsoft SQL Server Management Studio.CREATE CREDENTIAL <credential_name> WITH IDENTITY='<cipherTrust_manager_user>', SECRET='<cipherTrust_manager_user_password>' FOR CRYPTOGRAPHIC PROVIDER <provider_name>
You must use a user/password combination that exists on the CipherTrust Manager and that user must have at least Key Admins privileges.
To map this new credential to an existing login on the Microsoft SQL Server, execute the following query:
ALTER LOGIN <Sql_server_login> ADD CREDENTIAL <credential_name>
Fetch the keys from the CipherTrust Manager using the following commands:
USE Master; CREATE ASYMMETRIC KEY `<new_asym_key_name>` FROM PROVIDER `<cakm_ekm_provider_name>` WITH PROVIDER_KEY_NAME = '<key_name_on_cm>#1', CREATION_DISPOSITION=OPEN_EXISTING
Create TDE Login and add credentials.
CREATE LOGIN <tde_login_name> FROM ASYMMETRIC KEY <new_asym_key_name>; CREATE CREDENTIAL <tde_cred_name> WITH IDENTITY = '<domain_name||cm_username>', SECRET = '<cm_password>' FOR CRYPTOGRAPHIC PROVIDER <cakm_ekm_provider_name>; ALTER LOGIN <tde_login_name> ADD CREDENTIAL <tde_cred_name>;
Before restoring the database, ensure that the database backup is copied to the desired Microsoft SQL Server node. Now, restore the database
using the below command. RESTORE DATABASE <DB Name> FROM DISK = '<backup_file_path>\<backup_file_name>.bak' WITH REPLACE;
Note
While restoring the encrypted TDE Database, if key version is enabled, ensure that the latest versioned key must be fetched on the another machine (secondary node).