Re: Encrypted values are different although the source is the same
From: David Gugick (david.gugick-nospam_at_quest.com)
Date: 11/29/05
- Next message: Remus Rusanu [MSFT]: "Re: Encrypted values are different although the source is the same"
- Previous message: CB: "Encrypted values are different although the source is the same"
- In reply to: CB: "Encrypted values are different although the source is the same"
- Next in thread: Remus Rusanu [MSFT]: "Re: Encrypted values are different although the source is the same"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 29 Nov 2005 14:30:46 -0500
CB 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/microsoft.public.dotnet.security/post470654.asp
-- David Gugick Quest Software www.imceda.com www.quest.com
- Next message: Remus Rusanu [MSFT]: "Re: Encrypted values are different although the source is the same"
- Previous message: CB: "Encrypted values are different although the source is the same"
- In reply to: CB: "Encrypted values are different although the source is the same"
- Next in thread: Remus Rusanu [MSFT]: "Re: Encrypted values are different although the source is the same"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
