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;

• Razvan Zoitanu: Inherited system, no docs on the password used to protect the DBMK for db2, so I had to add one to make this work: ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = <password>, then remove the encryption by SMK B. Backed up db2, restored to Server1, opened session with the pass, enabled the SMK A encryption as by your answer. Good thing I've noticed this as part of a planned migration, or the encrypted data might have been lost if Server2 failed, even if the dbs are backed up daily. Thank you so much for pointing me on the right track. I'll add another answer with the process that worked for me.
• Remus Rusanu: You were lucky indeed that SMK still worked, allowed you to add a password of choice.

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