I am developing a database which will use SQL cell level encryption to encrypt confidential data. Upon setting it up, I noticed if I take the value MyValue, each time I encrypt it, it will result in a different VarBinary value. Is this typical of the SQL cell level encryption? Is there a way to insure MyValue will always return the same VarBinary value?
As an example, we will likely be storing Credit Card information. When a member signs up, I would like to confirm the credit card provided does not already exist in the database. It would be very expensive to have to decrypt all of the credit cards just to see if one matches the credit card supplied by the user. Therefore, I was hoping to be able to encrypt the supplied credit card (in the stored procedure) and then query where creditcard = @EncryptedCreditCard.
Is this possible using SQL Cell level encryption? If not, what would your best recommendation be?
asked Oct 30 '10 at 10:07 AM in Default
One option is to create another column that is a hash of the card number and index it. Then when searching check the hash of the number you have against the hash column AND also the number against the decrypted number column. The second comparison is needed since a hash is not guaranteed to be unique
answered Oct 30 '10 at 10:32 AM
When we looked at credit card encryption we looked at SQL encryption and discarded it because anyone who has valid access to the SQL will also have access to the encryption keys and for PCI compliance it is necessary to restrict the access to the cards to a select few people. Therefore we created a DLL which does the encryption and decryption.
answered Oct 31 '10 at 10:40 AM