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
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPassword'


CREATE CERTIFICATE BackupCert
WITH SUBJECT = 'Backup Certificate';


-- Create a symmetric key
CREATE SYMMETRIC KEY FileName
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE BackupCert
GO

OPEN SYMMETRIC KEY FileName
DECRYPTION BY CERTIFICATE BackupCert
GO

UPDATE [TestDB].[dbo].[Order_Customer_tab]
SET [Process_File_Name] = ENCRYPTBYKEY(key_guid('FileName'), [Process_File_Name])
GO

OPEN Master Key decryption by PASSWORD = 'StrongPassword'

select Process_File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(Process_File_Name)) 
 from [TestDB].[dbo].[Order_Customer_tab]


I tried doing above still no luck.

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

Thanks
Looks like you did not encrypt the encryption key with the database master key. There has to be a complete chain from the service master key to the encryption key, usually is like this:

service master key -> database master key -> [optional certificate private key] -> encryption symmetric key

You are going to have to open the encryption key using the original password and then add the missing master key encryption.