-> I performed a database restore with a backup on a development server as advised by my application team.

-> The database restore went fine. Application team performed testing and reported below error when trying to decrypt an encrypted column.

Msg 15581, Level 16, State 7, Line 2
Please create a master key in the database or open the master key in the session before performing this operation.

-> To solve this problem, I executed below query on SQL Server Instance where the database was restored,

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Passw0rd' -- Password should be same as what was used with query "CREATE MASTER KEY ENCRYPTION BY PASSWORD"
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

-> Execute below query to fetch the decrypted data,

OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
 FROM Table1;
CLOSE SYMMETRIC KEY Table1_Sym_Key;
GO

-> It worked fine. Below action should be taken if no manula steps is required.

-> Before restoring a database backup that contains column encrypted objects on a different SQL Server instance. Below actions should be performed.

-> Execute below query to backup the Service Master Key where the database backup was performed.

BACKUP SERVICE MASTER KEY
 TO FILE = 'C:\temp\JBSAG1_Service_master_key'
ENCRYPTION BY PASSWORD = 'Passw0rd';

-> Copy the backup of Service Master Key to SQL Server Instance where restore should be performed.

-> Execute below query on destination SQL Server Instance where restore will be performed,

RESTORE SERVICE MASTER KEY
FROM FILE = 'C:\temp\JBSAG1_Service_master_key'
DECRYPTION BY PASSWORD = 'Passw0rd';
GO

-> Once above tasks are completed. Any further restores will not have any issues querying the encrypted data.

-> Check this article if you are encountering this issue on an Always ON availability group environment.

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.