Standard

Posted by

Posted on

July 10, 2019

Posted under

Encryption

Comments

Leave a comment

Restoring a Transparent Data Encryption (TDE) Enabled User Database on a SQL Server Instance with a different master key

-> I had to restore a database on a development server using a backup from a Production Server.

-> When I tried restoring the backup I got the below error,

1.Restore_Error.PNG
TITLE: Microsoft SQL Server Management Studio
——————————
Restore of database ‘JBS_App_DB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot find server certificate with thumbprint ‘0xAA2EBEFF26F6DC56959BF42BFAFF09ABC6F12D39’. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&LinkId=20476
——————————
BUTTONS:
OK
——————————

-> It seems like the user database from where the backup was performed is TDE enabled .

-> We should backup the Certificate and Private key from SQL Server Instance where this backup was performed using below,

USE master;
GO
BACKUP CERTIFICATE JBS_PROD_Certificate
TO FILE = 'C:\temp\JBS_PROD_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JBS_PROD_Certificate.pvk',
ENCRYPTION BY PASSWORD='Pa$$w0rd');

-> The created Certificate should be added to the SQL Server Instance where we are performing the restore.

-> Executing below TSQL to create the master key on Instance where the backup should be restored,

USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Pa$$w0rd';
GO

-> Executing above Tsql produces below error,

2.Master_Key_Error.PNG
Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.

-> It seems like the SQL Server instance already has a master key created.

-> Lets just create the certificate using below,

CREATE CERTIFICATE JBS_PROD_Certificate
FROM FILE='C:\temp\JBS_PROD_Certificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\temp\JBS_PROD_Certificate.pvk',
DECRYPTION BY PASSWORD='Pa$$w0rd')

-> Try restoring the database and it works without any issue.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply