Re: Encrypted values are different although the source is the same
From: Remus Rusanu [MSFT] (Remus.Rusanu.NoSpam_at_microsoft.com.nowhere.moon)
Date: 11/29/05
- Previous message: David Gugick: "Re: Encrypted values are different although the source is the same"
- In reply to: CB: "Encrypted values are different although the source is the same"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 29 Nov 2005 13:41:30 -0800
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
>
- Previous message: David Gugick: "Re: Encrypted values are different although the source is the same"
- In reply to: CB: "Encrypted values are different although the source is the same"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
