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.