Encrypted values are different although the source is the same

Hi
I am in the process of evaluating the SQL 2005 data encryption. I have
noticed something very strange and am hoping that someone would be able to
clear it up for me.
I have a table that holds credit card numbers. It is possible that there
will be two or more rows with the same credit card number.
I have created my master key:
create master key encryption by password =
'***************************************
********'
GO
I have created my certificate:
create certificate cert_sk_admin with subject = 'Certificate for accessing
symmetric keys';
GO
I have created my symmetric key:
create symmetric key sk_CreditCard with algorithm = aes_128 encryption by
certificate cert_sk_admin;
GO
I have added a new column to the credit card table (lets call it CCNO_Enc.
The existing column is CCNO).
I then update the new column:
UPDATE CREDITCARD
SET CCNO_Enc = EncryptByKey(Key_GUID('sk_CreditCard'), CCNO)
GO
The Problem:
If I now query this table (select CCNO, CCNO_Enc FROM CREDITCARD WHERE CCNO
= '123456789'), I get two records back (there are two rows with this CCNO).
The problem is that the encrypted value of the two rows is different? How
can this be if the source value is the same. Our problem with this is that
searching on an encrypted column is very slow(when performing the
decryption). We would like to encrypt the search criteria and use that to do
a direct comparison on the encrypted field (without decrypting it in the
where clause)
The Questions:
1. Why are the encrypted values different?
2. Can this be changed?
Thanks
CraigCB wrote:
> Hi
> I am in the process of evaluating the SQL 2005 data encryption. I have
> noticed something very strange and am hoping that someone would be
> able to clear it up for me.
> I have a table that holds credit card numbers. It is possible that
> there will be two or more rows with the same credit card number.
> I have created my master key:
> create master key encryption by password =
> '***************************************
********'
> GO
> I have created my certificate:
> create certificate cert_sk_admin with subject = 'Certificate for
> accessing symmetric keys';
> GO
> I have created my symmetric key:
> create symmetric key sk_CreditCard with algorithm = aes_128
> encryption by certificate cert_sk_admin;
> GO
>
> I have added a new column to the credit card table (lets call it
> CCNO_Enc. The existing column is CCNO).
> I then update the new column:
> UPDATE CREDITCARD
> SET CCNO_Enc = EncryptByKey(Key_GUID('sk_CreditCard'), CCNO)
> GO
> The Problem:
> If I now query this table (select CCNO, CCNO_Enc FROM CREDITCARD
> WHERE CCNO = '123456789'), I get two records back (there are two rows
> with this CCNO). The problem is that the encrypted value of the two
> rows is different? How can this be if the source value is the same.
> Our problem with this is that searching on an encrypted column is
> very slow(when performing the decryption). We would like to encrypt
> the search criteria and use that to do a direct comparison on the
> encrypted field (without decrypting it in the where clause)
> The Questions:
> 1. Why are the encrypted values different?
> 2. Can this be changed?
> Thanks
> Craig
This has to do with AES (Rijndael) encryption and its use of something
called cipher-block chaining. That is, the same plain text can be
encrypted with the same key, producing different cipher text. This
thread might explain it in more detail than I can provide:
http://www.eggheadcafe.com/ng/micro...]www.imceda.com
www.quest.com|||The encryption is salted. There are several severe problems with encryption
if that is not done.
Therefore you cannot use encrypted values as index. You could use hash of
the clear text value as index, but that would also weaken you security
(again, if one knows a clear text value, it can find the corresponding
encrypted value in the database).
For a description of salting (or initialization vector) see
http://www.rsasecurity.com/rsalabs/node.asp?id=2171
HTH,
~ Remus
"CB" <craig.bryden@.derivco.com> wrote in message
news:%231gDknM9FHA.4076@.tk2msftngp13.phx.gbl...
> Hi
> I am in the process of evaluating the SQL 2005 data encryption. I have
> noticed something very strange and am hoping that someone would be able to
> clear it up for me.
> I have a table that holds credit card numbers. It is possible that there
> will be two or more rows with the same credit card number.
> I have created my master key:
> create master key encryption by password =
> '***************************************
********'
> GO
> I have created my certificate:
> create certificate cert_sk_admin with subject = 'Certificate for accessing
> symmetric keys';
> GO
> I have created my symmetric key:
> create symmetric key sk_CreditCard with algorithm = aes_128 encryption by
> certificate cert_sk_admin;
> GO
>
> I have added a new column to the credit card table (lets call it CCNO_Enc.
> The existing column is CCNO).
> I then update the new column:
> UPDATE CREDITCARD
> SET CCNO_Enc = EncryptByKey(Key_GUID('sk_CreditCard'), CCNO)
> GO
> The Problem:
> If I now query this table (select CCNO, CCNO_Enc FROM CREDITCARD WHERE
> CCNO = '123456789'), I get two records back (there are two rows with this
> CCNO). The problem is that the encrypted value of the two rows is
> different? How can this be if the source value is the same. Our problem
> with this is that searching on an encrypted column is very slow(when
> performing the decryption). We would like to encrypt the search criteria
> and use that to do a direct comparison on the encrypted field (without
> decrypting it in the where clause)
> The Questions:
> 1. Why are the encrypted values different?
> 2. Can this be changed?
> Thanks
> Craig
>