Re: SQL 2005 - Searching Encrypted SSN

Thanks guys!
The bottom line is that I think I will do it with a third party database
encryption solution. We have several hundreds of applications that use this
data, and it does seem like a lot of work, if it is at all possible.
I'd rather do it with a solution and vendor that has "real-life" experience.

Cheers,

Ron


"Remus Rusanu [MSFT]" wrote:

Usualy this is done by storing a cryptographic hash (e.g. SHA256) of the
clear text, in a separate column in adition to the encrypted column. The
search is performed on the hash column, not on the encrypted column. The
hash can be used only to find exact matches. The performance penalty is the
cost of computing one hash to be searched for (e.g hash the clear text SSN,
then search the hash value)

You should realize that this schema allows an attacker to validate whether a
known SSN number is in the database or not, since he can compute the hash
and search for it.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx


"Ron Brent" <RonBrent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E9D1C567-4499-4076-A67C-E72E38ABB104@xxxxxxxxxxxxxxxx
Hi,

SQL 2005.
I would like to encrypt the SSN column (the PK).
Currently, I have an application that searches according to SSN (i.e. -
the
user types her SSN for verification, and then her details are retrieved
from
the SQL Server table).
So, the column that is encrypted is the only column that I can use to
search
according to, plus it should be the PK.

Is it possible to do it with the SQL 2005 encryption?
How do I solve the performance issue of searching (select *...) on an
encrypted column?

Many thanks,

Ron



.

Relevant Pages

  • RE: Signing before Encryption and Signing after Encryption
    ... The property that a hash match is supposed to verify (is this ... Signing before Encryption and Signing after Encryption ... Signing with symmetric keys is a lot more ...
    (Security-Basics)
  • Re: Newbie - Is this Reasonable?
    ... because this hash is stored in the database. ... So you use PKCS5v2 to generate a key hash from a salt and the user's passphrase, then store the salt and the hash in a database. ... are even more critical in database applications because the payoff from tampering with selected fields may be much higher, fields tend to be fixed-length so it's easier to tamper with them in a meaningful way, and databases lend themselves to off-line analysis, so the attacker can marshall more resources and take more time to attack your system. ... You're using a stream cipher for encryption. ...
    (sci.crypt)
  • Re: Signing before Encryption and Signing after Encryption
    ... Hash: SHA1 ... Encryption and Signing after Encryption ... are signature schemes that only require symmetric keys. ...
    (Security-Basics)
  • Re: OTP and message integrity.
    ... The keys are OTP encrypted as well. ... Without the hash, ... Without the keys, ... keys are different for each encryption, the random number files for the OTP ...
    (sci.crypt)
  • Re: Encryption keys
    ... simple hash like SHA1or something, then it is pretty easy to brute ... > SSL uses asymmetric encryption, ... > Embed the public key of your server in your code. ... This is the symmetrical key. ...
    (microsoft.public.dotnet.general)