x

SQL Encryption

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?

Thanks,

Zach
more ▼

asked Oct 30, 2010 at 10:07 AM in Default

WebTech gravatar image

WebTech
51 2 2 4

Is anyone else able to offer their input on this? Can anyone point me in the right direction on how to write my own using the Managed Rijndael class and using registry to store the keys?
Nov 01, 2010 at 11:41 PM WebTech
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
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
more ▼

answered Oct 30, 2010 at 10:32 AM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

So, there is no way that you know of using SQL Encryption, to insure the value returned is always the same Varbinary string? Your idea is great, but I would prefer not to store the same information twice if I do not need to.
Oct 30, 2010 at 10:40 AM WebTech
I'm sure that would be ideal, someone else here could speak to that better than myself. Personally, I prefer to encipher/decipher in the client code(meaning website code, not on enduser's machine) and store that into the db
Oct 30, 2010 at 10:51 AM Scot Hauder
Are you using a component similar to AspEncrypt for that? I have concerns regarding SQL encryption but it is the method the client wishes to deploy. It concerns me that all an attacker needs to know, is the KeyNames and they would simply be able to decrypt all the data in the database. Thoughts?
Oct 30, 2010 at 11:08 AM WebTech
No, I rolled my own using 256bit Rijndael Managed class http://msdn.microsoft.com/en-us/library/system.security.cryptography.rijndaelmanaged.aspx You can store the keys in the registry, or use DPAPI. Even if you only use the web.config to store the keys and IVs the attacker would have to compromise both, the web server and your db server to decipher the data. Perhaps it is an irrational fear or laziness on my part but I would rather do this than manage all of the keys and master key in SQL and if something went awry with these keys not be able to decipher anything in my db. I'm sure you can accomplish what the client wants. This board isn't as active on the weekend but by Monday, others that have done this will probably chime in.
Oct 30, 2010 at 11:47 AM Scot Hauder

Scot,

Thanks for the excellent information.

I would love more information on that, especially how to create and store the encryption key in registry. I am fairly new to .Net and a working example would be phenomenal. Of course, I would understand if you did not provide that!
Oct 30, 2010 at 08:43 PM WebTech
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Oct 31, 2010 at 10:40 AM

dvroman gravatar image

dvroman
1.1k 2 2

Yeah, that is my primary concern regarding SQL encryption.
Oct 31, 2010 at 09:24 PM WebTech
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x737
x38

asked: Oct 30, 2010 at 10:07 AM

Seen: 1340 times

Last Updated: Oct 30, 2010 at 10:07 AM