Restore db with encrypted column in SQL Server, without overwriting existing Service Master Key

Question

Scenario:

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available. Password used to protect the DBMK 1 is unknown, encryption is enabled by SMK A.

Server2 running SQL Server 2012 with SMK B, db2 with DBMK 2, symmetric key and certificate available. Password used to protect DBMK 2 is unknown, SMK B used for encryption.

A restore of db2 to Server1 is required. There's one encrypted column in a table that stores passwords for an internal ASP.NET application. There are other applications accessing encrypted data in db1 on Server1 .

Question:

Best option to restore db2 to Server1 and access the encrypted data without causing issues to db1 ?

Tested:

A simple restore will bring the db2 data, symmetric key and certificate to Server1, but SMK A can't be used to decrypt the data: "Please create a master key in the database or open the master key in the session before performing this operation." Restored SMK B on a test server and all was good. Can't use this solution on Server1, as I believe that restoring SMK B to Server1 (overwriting SMK A) will disable my access to the encrypted data in db1. Don't want that to happen.

Any advice ? Thank you.

Answer 1

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.

I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.

What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption to the DBMK:

RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Answer 2

Used an empty SQL Server 2012 test instance, restored SMK from Server2, and restored db2 (my symmetric key, admin_key, and certificate admin_cert were included by default in the backup/restore).

BACKUP SERVICE MASTER KEY 
    TO FILE = 'path\prod.SMK' 
    ENCRYPTION BY PASSWORD = pass

RESTORE SERVICE MASTER KEY 
    FROM FILE = 'path\prod.SMK' 
    DECRYPTION BY PASSWORD = pass

This setup now allows access to the data in the encrypted column, just like on the production system. Run query in db2 on this test instance to demonstrate:

OPEN SYMMETRIC KEY admin_key 
    DECRYPTION BY CERTIFICATE admin_cert

SELECT ID, CONVERT(VARCHAR(1000),DecryptByKey(Password)) [decrypted_Password] 
FROM table_with_enc_column

CLOSE SYMMETRIC KEY admin_key

Start with this script in db2 on the test instance to remove the SMK encryption and switch to password encryption:

OPEN SYMMETRIC KEY admin_key 
    DECRYPTION BY CERTIFICATE admin_cert

ALTER MASTER KEY 
    ADD ENCRYPTION BY PASSWORD = pass

ALTER MASTER KEY 
    DROP ENCRYPTION BY SERVICE MASTER KEY

CLOSE SYMMETRIC KEY admin_key

Now backup this database from the test instance and restore to the destination Server1. No need to restore the SMK on this one.

Run this script in db2 on Server1 to set the SMK encryption:

OPEN MASTER KEY 
    DECRYPTION BY PASSWORD = pass

ALTER MASTER KEY 
    ADD ENCRYPTION BY SERVICE MASTER KEY

ALTER MASTER KEY 
    DROP ENCRYPTION BY PASSWORD = pass

All good, data in encrypted columns is accessible on Server1 both in db1 and db2, with SMK A as intended.

If you have any encrypted data on your instances, make sure you know the passwords for your DBMK, and you backup your SMK. Otherwise, the db backup might be useless.

There's a very detailed post on handling issues with the SMK: How to recover when the service master key (SMK) is not accessible