2

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  

migrated from stackoverflow.com Sep 28 '18 at 13:17

This question came from our site for professional and enthusiast programmers.

  • Thanks, but I need to keep the existing database intact. Any idea on how to do this? – Teddy Higgins Sep 28 '18 at 12:34
  • master key and symmetry keys can not be copied across. As they maintain database security at column level and serving actual purpose. Either you have to rename column and create a new column, decrypt data at source level and copy data across to new column , finally encrypt column at target – Ven Sep 28 '18 at 12:37
  • you should be able to backup certificate and symmetric key and restore them to the other instance stackoverflow.com/questions/41032092/… – Bob Klimes Sep 28 '18 at 13:42
  • Actually, i figured it out. i'll post the answer below. – Teddy Higgins Sep 28 '18 at 13:42
3

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.

0

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.