SQL 2005 Encryption

[gillrowley]

I have a table that contains encrypted social security numbers and other data. I'm trying to extract these SSNs onto a webpage. I pass an unencrypted SSN to a stored procedure, and the sproc encrypts the parameter, and searches the table for any matches. I come up empty in my search. Upon inspection I see that multiple table records with the same SSN have different values in the encrypted field (SSN is not a primary key). If I query the table unencrypting the SSNs during the search, and matching the unencrypted records with the unencrypted parameter, then I get successful results. This takes longer, since the table has over 3 million records.

Is it possible to accomplish this task somehow using my first method? Different encyrption technique, perhaps? I had created the symmetric key with a certificate using the DES algorithm.

Thanks
Gill

 

[mrdenny]

When you are encrypting the data using the symmetric key you are probably padding the encrypted value with some other value, probably the id value of the row, or the numerical value of the SSN, or some random numeric value. Because of this the SSN value that you encrypt will always be different than the values stored in the database, unless you know what value was used to encrypt the SSN.

One option would be to store the MD5 hash of the SSN in another column of the database. MD5 is a one way hash of the value so you can index that column and search it as needed, then use the value which was encrypted by the symmetric key to display to the website.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog