Friday, March 9, 2012

Encrypt By Key gives different results every call time

Every time I call Encrypt by key I get a different result, although I am encrypting the same clear text,using the same authenticator and using the same key.
I tried calling it twice inside one stored procedure and I got two different results.

Declare @.Temp varbinary(8000);
Declare @.Temp2 varbinary(8000);
set @.Temp = EncryptByKey(Key_GUID('MyKey'),@.cleartext,1,@.Authenticator);
set @.Temp2= EncryptByKey(Key_GUID('MyKey'),@.cleartext,1,@.Authenticator);
Select @.Temp,@.Temp2;

when executing this stored procedure, the value of @.Temp is differnent from the result of @.Temp2, and the same thing happens without using an authenticator.

Is there something going behind in the encryption mechanism of SQL server?

yes it's deliberate and important. You might have 2 rows with the same value, for example a salary. If these had the same resulting encrypted value it would make discovery of the non-encrypted value much easier. To address this SQL 2005's encryption methods use a salt with a random initialisation vector so you get a different encrypted value for the same input text.

thx,

Simon.

No comments:

Post a Comment