Sql-server – Decryption not working when SQL Server database copied to new server

encryptionsql serversql-server-2012

I am copying a database from an old server to a new server. The database has an encrypted column. From my searches I see that I need to backup the master key from the old server and restore it in order to be able to retrieve previously encrypted data, however, I must be doing something wrong.

Please see the process I am following. Can anyone please explain why it isn't working? When the encrypted column is decrypted, the values shows NULL, which is not right.

-- OLD SERVER ---------------------------------------------------------------
BACKUP MASTER KEY TO FILE = 'D:\MasterKey' ENCRYPTION BY PASSWORD = 'MyPassword1'
GO

-- Copied file to new server

-- NEW SERVER ---------------------------------------------------------------
RESTORE MASTER KEY FROM FILE = 'D:\MasterKey' 
    DECRYPTION BY PASSWORD = 'MyPassword1'
    ENCRYPTION BY PASSWORD = 'MyDBNameEncryption$3C|_|R3!'
GO 
-- Command(s) completed successfully.

select * from sys.symmetric_keys
-- (1 row(s) affected)
-- name principal_id    symmetric_key_id    key_length  key_algorithm   algorithm_desc  create_date modify_date
-- ##MS_DatabaseMasterKey## 1   101 128 D3  TRIPLE_DES  2013-01-17 09:37:05.873 2013-01-17 09:37:05.873

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyDBNameEncryption$3C|_|R3!'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
-- Command(s) completed successfully.

CREATE CERTIFICATE MyDBNameCertificate01 WITH SUBJECT = 'MyDBName Certificate'
GO
-- Command(s) completed successfully.

CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyDBNameCertificate01
GO
-- Command(s) completed successfully.

select * from sys.certificates
-- (1 row(s) affected)
-- name certificate_id  principal_id    pvt_key_encryption_type pvt_key_encryption_type_desc    is_active_for_begin_dialog  issuer_name
-- MyDBNameCertificate01    268 1   MK  ENCRYPTED_BY_MASTER_KEY 1   MyDBName Certificate

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyDBNameCertificate01
select 
    name, 
    Password, 
    decryptbykey(Password) as [DecryptedPass]
FROM
    [tbl_Users]
CLOSE SYMMETRIC KEY SSN_Key_01
--name  Password    DecryptedPass
--David 0x00CF72EF69A517......34EBC1EDB4F1D0    NULL

The NULL value above should be the original value from the old database, so I assume the encryption objects have not been installed correctly…

Best Answer

The sequence of steps is entirely depended on you current (old db) encryption hierarchy. There is no cookie-cutter recipe that can be applied w/o knowing your encryption hierarchy, which you did not present. It cannot be deduce from the code you posted, steps like creating a new certificate after the database copy is attached make not much sense. Normally after you copy the file and attach it to the new server all you have to do is open the master key and add the server master key encryption:

USE [MyDatabase];
GO

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

Assuming a typical data->symmetric key->certificate->database master key->service master key encryption hierarchy the steps above are sufficient.

Related Question