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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s