Encryption Question - Urgent!

Hi,

I encrypt a column in a table. I am able to decrypt/encrypt the same successfully. However, when I copy the encrypted data to a new database and try to decrypt using the same certificate, it doesn't work. I have created the same "Master Key" and certificates on the new DB .... So, is it possible to decrypt the encrypted data that is transferred from one DB to another? If not, are there any alternatives ?

I have tried opening the master key on the new DB using the following:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

Thanks.!

How do you encrypt/decrypt and what error messages are you seeing, if any? Please describe the steps that you followed in more detail.

Thanks
Laurentiu

|||

Here are the details:

In DB1

1. I created a master key (CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pwd')

2. Cerated a certificate (CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'xxx')

3. Created Symmetric key (CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME).

Then in DB1, I encrypted a column in a table using ENCRYPTBYKEY. I am also able to decrypt using DECRYPTBYKEY.

Then I inserted the encrypted column into another db with same table structure.

INSERT INTO DB2.dbo.table1(enc_column)

SELECT x.enc_column

FROM DB1.dbo.table1 x

now in DB2, I am not able to decrypt the "enc_column". when I use the same syntax I get a "null".

|||

Thanks for the details. The reason why you cannot decrypt in the new table is because you have no decryption key in the new database.

If you plan to move encrypted data from one database to another, you should create the symmetric key used to encrypt the data in both databases. The way you can do that is by using the same IDENTITY_VALUE and KEY_SOURCE parameters for the CREATE SYMMETRIC KEY statement. Of course, you should use the same key algorithm, as well.

Thanks
Laurentiu

|||

Also, see this recent post for a demo of how to do this:

http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx

Thanks
Laurentiu