Encrypted DB -- Restore Question

Hi,

I have a DB in which I encrypt a few columns in a table. I am using a Symmetric key to encrypt and decrypt the data. When I take a back up of this DB and restore on another server ... my decryption doesn't work. I have dropped the master key and recreated it with same password and that didn't help either.

What are the rules to follow when we restore a db on a different server that has encrypted data ? Thanks.

You will need to open the master key in the new database once ,so that a copy of the same is saved in the master database. After that the decryption should work. Let me know if this helped?|||How do I open Master Key? Thanks!|||

Hi,

You shouldn't have to do anything special to restore a db with encrypted values. The one thing you may need to do is to alter the master key of the database and re-encrypt with the service master key. You can do this with:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your_password_here';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

(for more info, take a look http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx).

EDIT: whoops, wrong link, sorry, this should now have the correct link.

|||

I tried and that didn't help ...

I used OPEN MASTER KEY DECRYPTION BY PASSWORD = 'my pwd'

|||

OPEN MASTER KEY DECRYPTION BY PASSWORD = ['password']

|||What's the specific error you are seeing? Are you getting "NULL" back as the decrypted value or are you receiving a server error?|||Yes ... I get "NULL" and when I look at the data in the column, it does have encrypted data. Thanks for your help!!!|||Does the login that you are using having appropriate rights... I mean on the symmetric keys, certificates.|||Yes .. I have logged in as SysAdmin ...|||

One other tying you can try, before you decrypt, run "SELECT * FROM sys.openkeys" to verify that the symmetric key is ready for decryption.

|||Are the operating system on both the machines the same, because certain encryption types like AES are supported on certain type of OS.|||I looked at the owner info again ... and the original DB was created by "sa" account itself ... where as the attached DB is owned by another user that is of SysAdmin group. Does that matter for decryption? I really appreciate your help!!|||

The owner of the db shouldn't matter for decryption (especially since you confirmed you can select from the table without problems) as long as the user has not been denied view rights for the symmetric key. You also need rights on whatever is encrypting the symmetric key.

If the symmetric key shows up in sys.openkeys though, you should be fine as it means the key has been opened and ready for decryption.

Can you share the SQL you are using to decrypt the data?

|||Just a suggestion, try restore and backup instead of attachdb.