Binary Data on Multiple Servers

I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.

When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).

--To create my cert and key:

USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO

To encrypt:

OPEN SYMMETRIC KEY MyKey

DECRYPTION BY CERTIFICATE MyCert

WITH PASSWORD = 'same password as above';

Insert my record, use scope_identity to return primary key into @.CustomerID.

INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @.DataToEncrypt, 1, CONVERT( varbinary, @.CustomerID)))

CLOSE SYMMETRIC KEY MyKey

To decrypt:

SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @.CustomerID))) as PlainTextData

FROM Customers

WHERE (CustomerID= @.CustomerID)

Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.

Thanks in advance for your help!

Did you up the database master key on the server and restore it to your home machine? If not, then the data will not decrypt on your home machine. The database master key is used to encrypt the symmetric key which would then be used to decrypt your data. If you create the "same" symmetric key, but use a different database master key, then the identity will be different which will prevent decryption. This is by design, because it ensures that someone can fake security credentials and gain access to your data.|||

That's one thing I didn't do! Thanks Michael. That's got to be it.

Thank you,

Richard

|||

You generated different keys. Have a look at http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx for how you can regenerate the same key on different machines.

Thanks
Laurentiu

|||

Laurentiu, great article! I didn't realize the Key_Source and Identity_Value were essential to make the key work in multiple databases. I thought as long as the algorithm and name were identical everything would decrypt properly. Once I created a new key like you mentioned in your article, the decryption worked great across both databases. Thanks again!

Richard

sql