May 26, 2009 at 3:27 pm
I have created a table to hold Social Security numbers and have used encryptbykey on the field SSN varbinary(256) and added a unique constraint to prevent duplicate entries, however, since it is encrypted duplicates are being allowed. How can I prevent duplicate SSN #s from being entered? Should I decrypt the field for comparison within the sp upon new data entry?
May 26, 2009 at 4:55 pm
See the responses posted to this same question on SQLTeam:
May 27, 2009 at 10:56 am
You could use a hash.
Otherwise, there isn't a great way to do this. Typically you do not create a unique index on an encrypted field.
May 28, 2009 at 10:27 am
If I use hash can I not then recover or unhash the SSN like I can using encrypt/decrypt? The SSN will need to be viewed by certain users. My goal is to design this database for users to enter the confidential data and it be encrypted or hashed then others will need to be able to query the data without the encryption to view the SSNs. Is this possible in SQL 2005 without using external software?
May 28, 2009 at 10:40 am
Here's the thing. You can use a hash to try and determine if you have a duplicate. Not for encrypting.
What I'd suggest is that you use a separate column as a primary key, identity or GUID if you must. Then encrypt the SSN, compute a hash for another column that can be a quick check for dups. If you collide on a hash, decrypt, check, etc.
If you need to search, you might to the "last 4" or first 3 of the SSN.
Otherwise, there isn't a way to index an encrypted column.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply