10

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.

Improve this question

1 Answer 1

Reset to default
8

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.

Improve this answer
2
  • That blog entry is useful. I've followed it except that instead of "master" I use the particular database I want to copy. I get the error "An error occurred while decrypting asymmetric key 'asym_something' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable." when I restore the master key from the file. Should I FORCE it? (This is after I've restored the database, whereas your instructions have the restoring later on.)
    – paulmorriss
    Commented Jun 20, 2011 at 14:31
  • 1
    Thanks, it took me far to long to figure it out. eek, I am not sure that you should force it. The way I understand it, the "keys" are tied to the MASTER databases on each server. The only thing that is tied to the database is the cert that is signed by the master key. So you need to create a master key on server1, back it up, then restore the key on server 2. Then move over the cert for the database. I hope someone else can shed some light on the subject.
    – RateControl
    Commented Jun 20, 2011 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.