Restoring VKM/CAKM Database
This page intends to provide solution to specific problem occurred while restoring VKM/CAKM database.
Problem
The CAKM library cannot handle both thumbprints and IDs (UUID/FP) simultaneously. It can only manage one ID/thumbprint at a time, causing other databases to enter into a recovery state. This issue affects CAKM environments, where databases enter recovery_pending mode after restoring the encrypted VKM/CAKM database backup simultaneously.
Solution
You can restore VKM/CAKM TDE either manually or by using the script. The use of the script is optional.
Restore VKM/CAKM TDE Manually
Create and save the backup of the property file.
Set
VKM_mode=yes
.Fetch the restored Asymmetric Key from the CipherTrust Manager.
NOTE: Use a key name other than that was used with VKM/CAKM.
USE Master; CREATE ASYMMETRIC KEY `<new_asym_key_name>` FROM PROVIDER `<cakm_ekm_provider_name>` WITH PROVIDER_KEY_NAME = '<key_name_on_cm>', 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>;
Ensure that the database backup is accessible.
Restore the database
<db_name>
.RESTORE DATABASE <DB Name> FROM DISK = '<backup_file_path>\<backup_file_name>.bak' WITH REPLACE;
Verify whether the restore is successful and set
VKM_mode=no
.
If the restore fails
Drop the asymmetric key.
DROP ASYMMETRIC KEY <key_name>
Set
VKM_mode=no
.Fetch the restored Asymmetric Key from the CipherTrust Manager.
NOTE: Use a key name other than that was used with VKM/CAKM.
USE Master; CREATE ASYMMETRIC KEY `<new_asym_key_name>` FROM PROVIDER `<cakm_ekm_provider_name>` WITH PROVIDER_KEY_NAME = '<key_name_on_cm>', CREATION_DISPOSITION=OPEN_EXISTING
Restore the database.
RESTORE DATABASE <DB Name> FROM DISK = '<backup_file_path>\<backup_file_name>.bak' WITH REPLACE;
Verify whether the restore is successful.
Restore VKM/CAKM TDE using Script
To help restore VKM/CAKM TDE, a SQL script (cakm_ekm_db_restoration.sql
) and a C executable (cakm_ekm_config_change.exe
) are created.
These tools can handle both UUID and FP thumbprints/IDs, facilitating the restoration of VKM/CAKM encrypted databases to CAKM without downtime.
You can find these tools at the following path on GitHub:
https://github.com/ThalesGroup/CipherTrust_Application_Protection/tree/master/cakm/ekm
Before proceeding with restoring VKM/CAKM TDE using the script, complete the prerequisites described below.
Prerequisites
Ensure that the SQL Server has permissions to access the following:
CAKM property file
C executable (cakm_ekm_config_change.exe)
Database backup file
SQL script (cakm_ekm_db_restoration.sql)
Tip
This is a sample script and you can update the same as per your requirement.
If a database backup is created at a lower SQL version(for example, SQL server 2016) and needs to be restored at a higher SQL version(for example, SQL server 2019), create the following directories and provide read-write permissions to these directories. You can now copy the database backup in the backup directory.
<Path to backup file>\Microsoft SQL Server\MSSQL<n>.MSSQLSERVER\MSSQL\Backup\<DB>.bak
Where,
<n>
can be any version number of SQL Server database.Note
While restoring the backup of encrypted database, if the keyname already exists in the database, it is recommended to remove the key from the database before running the SQL script.
Steps
To restore VKM/CAKM TDE using the SQL script (cakm_ekm_db_restoration.sql
):
Copy
cakm_ekm_db_restoration.sql
andcakm_ekm_config_change.exe
from GitHub.Place these files on the location where CAKM is installed.
For example,C:\Program Files\CipherTrust\CAKM For SQLServerEKM
Open the command prompt and navigate to the path where CAKM is installed.
Below is the sample command to restore the encrypted backup file:
Syntax
sqlcmd -S <Hostname> -d <master> -U <sa> -P <password> -i <cakm_ekm_db_restoration.sql> -v keyname=<"cm_key_name"> provider=<"cakm_ekm_provider_name"> dbname=<"name_of_db_to_restore"> backup=<"path_to_backup_file>\Microsoft SQL Server\MSSQL<n>.MSSQLSERVER\MSSQL\Backup\<backup_file_name>.bak"> cm_user=<"cm_username"> cm_passwd=<"cm_password"> prop_path=""
Where,
<n>
can be any version number of SQL Server database.Sample
sqlcmd -S WIN-SPK9OQLL89C -d master -U sa -P password -i cakm_ekm_db_restoration.sql -v keyname="DSM_VKM_Migration_Key_2_2" provider="thales_provider" dbname="VKM_DB" backup="D:\MSSQL_SERVER\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\VKM_Migration_Testing.bak" cm_user="cmdomain||cmuser" cm_passwd="cmpassword" prop_path=""
Where,
-S: SQL Server name
-d: Database name
-U: SQL Login ID
-P: SQL Login ID password
-i: SQL script
-v: provide custom arguments as key="value" pair as specified in above command example:
keyname: Keyname present on CipherTrust Manager
provider: Crytographic provider
backup: DB backup file with complete path
dbname: database name with which backup will be restored
cm_user: CipherTrust login user name
cm_passwd: CipherTrust login password
prop_path=""
If you want to provide a path to this parameter, the path should be the location where the CAKM for Microsoft SQL Server EKM is installed. For example, prop_path=
"C:\Program Files\CipherTrust\CAKM For SQLServerEKM\"
.If an empty value is provided in
prop_path
, then script will use the default path to find the property file and executable file(cakm_ekm_config_change.exe
).
Default path:C:\Program Files\CipherTrust\CAKM For SQLServerEKM\
.
Note
In the above sample command, the SQL script (cakm_ekm_db_restoration.sql) is provided with
-i
option as an input to runsqlcmd
utility.
At the end of script execution,
If the encrypted database backup is successfully restored, a "Restore successful" message is displayed.
If the encrypted database backup restore fails, "Restore failed. Error:
<Detailed Error Message>
" is displayed.For example: