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

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 "

Answer 2

The master key was decrypted by the service master key on the source server and we were decrypting the master key with password on the destination. I altered the master key to be decrypted by the service master key and it's working now.