5

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  
4
  • Thanks, but I need to keep the existing database intact. Any idea on how to do this?
    – Teddy Higgins
    Sep 28, 2018 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, 2018 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, 2018 at 13:42
  • Actually, i figured it out. i'll post the answer below. Sep 28, 2018 at 13:42

2 Answers 2

5

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.

2
  • Suppose I restore system dbs onto new hardware after a sql OS crash then will the linked server credentials work without me restoring the SMK? I'm asking because I think that any sensitive data is protected by SMK.
    – variable
    May 11, 2022 at 18:53
  • Please can you clarify what you mean by affects in your statement - 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
    – variable
    May 11, 2022 at 18:57
1

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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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