Encrypted values are different although the source is the same

From: CB (craig.bryden_at_derivco.com)
Date: 11/29/05 

Date: Tue, 29 Nov 2005 11:47:16 +0200

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

Relevant Pages

  • Encryptio key hardware solution... help :(
    ... that provides a Secure and Safe environment where these Credit Card ... Now it was proposed we do the 'hardware ... methods to protect and unprotect passed data. ... using a 2-step process the first step will need to read the encryption key ...
    (microsoft.public.sqlserver.security)
  • [PHP] Re: keeping credit card info in session
    ... the strength of the encryption means nothing. ... Anyways, if you're storing the credit card in the database, then ... credit card based on the session id (so you should also store the ... PHP General Mailing List ...
    (php.general)
  • Re: [PHP] Re: keeping credit card info in session
    ... Encryption is a mandatory part of PCI compliance... ... to store the keys somewhere to decrypt the data to use it. ... On Apr 8, 2007, at 4:56 PM, itoctopus wrote: ... Anyways, if you're storing the credit card in the database, then ...
    (php.general)
  • Is In-Browser Encryption Safe?
    ... One of our clients has asked us to add an ordering facility to a web ... protect the credit card number. ... having orders reach the client as email makes sense. ... I have noticed implementations of public-key encryption ...
    (Security-Basics)
  • [PHP] Re: keeping credit card info in session
    ... You have to store the keys somewhere to decrypt the data to use it. ... As we have seen with blu-ray and HD DVD movies, the keys are the weak point that are easily compromised. ... Once you have the decryption key, the strength of the encryption means nothing. ... Anyways, if you're storing the credit card in the database, then why are you ...
    (php.general)