SQL Server 2005 - Restoring an encrypted DB on a different server

Question

I have backed up an encrypted DB (symmetric key/certificate) and restored it on a different server.

Unfortuantely we're having problems with the decryption... hoping someone can help.

In the restored db, I can see the Symmetric Key and the Certificate in SSMS, but when I try to Open the key using the cert ( open symmetric key KeyA decryption by certificate CertB )I get the following very descriptive error:

Msg 15466, Level 16, State 1, Line 1 An error occurred during decryption.

Any ideas?

Thanks in advance.

Answer 1

The problem you are probably experiencing is that the Database Master Key for the servers is different. To my understanding the other keys are based off of this and it could cause problems when trying to decrypt the data. Check out the encryption hierarchy for a description of the steps that go into data encryption.

I hope this answer helps and isn't too off-track. :)

Answer 2

http://blogs.msdn.com/lcris/archive/2007/11/16/sql-server-2005-restoring-the-backup-of-a-database-that-uses-encryption.aspx answers this:

"When you restore a database that uses encryption features, there is only one thing you need to take care off - if the database master key (DbMK) needs a service master key (SMK) encryption, you need to regenerate this encryption. Note that this encryption is made by default when you create the DbMK, but it may be intentionally dropped, if you want tighter control of access to the encrypted data. Anyway, if you did have such SMK encryption for the DbMK, the steps to regenerate it are the following:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password' ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY

That's it - the database encryption features should now work as when the backup was taken. Also note that it doesn't matter if you restore the database on the server where the backup was taken or elsewhere. The only thing that matters for this procedure is that you know one of the passwords protecting the DbMK "