CODEWITHSUNDEEP
sqlsql-serverencryptionsql-server-2014
LearningMacro
LearningMacro

Reputation: 137

Cannot Decrypt the encrypted columns from the database backup on local machine

I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.

USE [master];
  GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO 

select File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(File_Name)) 
 from [test].[dbo].[Orders_Customer]

I tried doing above still no luck.

Can anybody point me in the right direction? Any help is greatly appreciated.

Thanks

Upvotes: 1

Views: 989

Answers (1)

Rachel Ambler
Rachel Ambler

Reputation: 1604

You've opened the Master key (in your example) in the Master DB

Change the first line to use 

Use Test;

The Open Master Key statement works in the context of the Current Database. You opened it whilst in Master, but then selected that data from the Test DB.

Upvotes: 1

Related Questions