Restoring the Database which has encrypted columns

Scenario:

I tried restoring a database from server1 to server 2.

But when I tried accessing the encrypted data on restored database of server2, I got the following error while opening the encryption key to read the encrypted data, something like this:

“Unable to open the key for encryption. First open the Master Key.”

3 methods to resolve this type of error:

Decrypt and Encrypt the Data

1.) Decrypt the Encrypted data on original database of server1.

2.) Transfer the data from server1 to server2.

3.) Drop the Encryption keys, Certificates and Master key on restored database of Server2:

Select * from sys.certificates

select * from sys.symmetric_keys

Drop SYMMETRIC KEY CCC

Drop Certificate CERTAdventureWorks

Drop Master Key

4.) Recreate the Master_Key, Certificate and Encryption keys with the same name as it was befor so that no code of the restored database needs to be done on server2:

CREATE MASTER KEY ENCRYPTION

BY PASSWORD = 'zxcvbnm’

create certificate CERTAdventureWorks with Start_date = '07/20/2011',

Expiry_date = '07/20/2012',

subject = 'Generic encryption certificate for AdventureWorks database'

CREATE SYMMETRIC KEY CCC

WITH ALGORITHM = TRIPLE_DES ENCRYPTION

BY CERTIFICATE CERTAdventureWorks

5.)Encrypt the data and store using the above keys on server2 restored database.

Add Master Key encryption with Service key (If you know the password of Master Key):

On Server2 Restored Database:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'abcde'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY

Backup the Service key on Server1 and Restore it to Server2:

On Server1:

BACKUP SERVICE MASTER KEY TO FILE = 'E:\Certificate\S_Master_Key' ENCRYPTION BY PASSWORD = 'fghijkl'

On Server2:

RESTORE SERVICE MASTER KEY FROM FILE = 'E:\Certificate\S_Master_Key' DECRYPTION BY PASSWORD = 'fghijkl'

In few cases you might have to do the steps of approach 2 after performing the approach 3 steps.