Restore encrypted database to another server I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. I'd like to run another copy of the product for testing on another server. I've restored the database to another server and installed the product on that other server. When I installed it I supplied the same password, and then restored a backup from the main server. However I'm getting the error message:

System.Data.SqlClient.SqlException: An error occurred during decryption.


from the product. I can access the tables using SQLServer Management studio.

I have tried this:

on first server:

CREATE CERTIFICATE cert1 WITH SUBJECT = 'Certificate for my stuff'

BACKUP CERTIFICATE  cert1 TO FILE = 'd:\backup\cert1.dat' 
WITH PRIVATE KEY 
(
ENCRYPTION BY PASSWORD = 'mypassword',
FILE = 'd:\backup\cert1_privatekey.dat'
)


on second server:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'

CREATE CERTIFICATE cert1 FROM FILE = 'd:\cert1.dat'
WITH PRIVATE KEY
(
FILE = 'd:\cert1_privatekey.dat',
DECRYPTION BY PASSWORD = 'mypassword'
)


I also tried this on the second server:

alter MASTER KEY regenerate with enCRYPTION BY PASSWORD='password'


but that gave an error message about an asymmetric key.

How can I restore the backup from the first server onto the second server?

Update:

There is an asymmetric and symmetric key in the database as well. If I open the symmetric key using the asymmetric key I get the same error, so I think that's why it's not working - somehow the keys aren't transferring in such a way that they can be used.
I wrote a blog entry about mirroring and TDE. 

It took me a while to figure out that to get the db on the secondary server, I needed the code line:

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
 GO


The password is the same one that I used to encrypt the master key file. After issuing the command on the 2nd SQL server, everything played nicely. Here is the db.stackexchange post I made also.