How to migrate a database that has a database master key and certificate for encryption

Question

Morning y'all.

I'm a little stumped at this one. We have a database that we encrypted a column in a table. This is the master key that I created and the Certificate & Symmetric key. I need to migrate this database to another instance of SQL, but after I migrate the database, I cannot decrypt the information.

Help?

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'omitted';

CREATE CERTIFICATE SecureLoginPasswords  
   WITH SUBJECT = 'SecureLogin Customer Passwords for Website';  
GO  


CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE SecureLoginPasswords;  
GO  

Answer 1

I need to migrate this database to another instance of SQL, but after I migrate the database, I cannot decrypt the information.

You have a database master key (DMK), which is protected by both the service master key (SMK) and a password. Since you're migrating to a different instance of SQL Server you can still access the DMK through the password, however automatic key decryption will no longer work because the SMK has changed.

The fix is easy enough. When you restore the database to the new instance, run the following:

use databasename
go

open master key decryption by password = 'MyPassword'
go
alter master key drop encryption by service master key
go
alter master key add encryption by service master key
go
close master key
go

This will drop the encryption of the DMK by the SMK, since it can't decrypt it on the new instance. Then it will add the encryption through the SMK back to the DMK so that automatic key decryption will work.

The reason I don't say to backup and restore the SMK, as the other answer shows, is that it affects all of the databases on the instance whereas just fixing the broken one won't cause any other issues on any current databases.

Answer 2

First you need to backup the master key

backup service master key to file = 'c:\exportedmasterkey\MasterkeyLMI.SMK'
encryption by password = 'omitted'
go

then you need to restore the master key to the new instance. In my case, i restored the database to the other instance which brought along the Certificate and Symmetrical key.

RESTORE SERVICE MASTER KEY FROM FILE = 'c:\exportedmasterkey\MasterkeyLMI.SMK' 
DECRYPTION BY PASSWORD = 'omitted'

The last step I did was grant security for the new users to the certificate and the key.

GRANT CONTROL ON CERTIFICATE::[CertName] TO [User]
GRANT VIEW DEFINITION ON SYMMETRIC KEY::[KeyName] TO [User]