Social Security Number Encryption

[HoganConsulting]

One of our clients is a consortium of credit brokerage companies. The group of companies shares a single hosted web application where they all store a basic set of datum on their credit clients including social security numbers. They routinely search this large database for matches in an attempt to minimize credit fraud. The database currently contains over 350,000 social security numbers. These numbers must be searchable and exportable on demand as plain text. The records are currently stored in a SQL Server 2000 database. As part of their security upgrades, we would like to start encrypting the SSN's in the database, but they still must be 100% searchable and exportable. I've seen several posts detailing encryption/decryption and databases, but none contain hard details on the implementation or appear to be scalable to several hundred thousand records.

1. Have any of you had experience with a similar encryption situation?
2. How did you implement your encryption scheme?
3. Was it scalable?
4. I personally fear a scenario where one day, the data fails to decrypt and the companies' data becomes unusable. Do you have any recommendations for preventing this kind of catastrophic failure?

[Pekka]

1. Yes
2. Stored 2 versions: 1 hashed (so its searchable and scalable), 1 aes_encrypted, so its exportable.
3. Yes, the hash is searchable, that means that you can du a search like "is this SSN in the database" without any en/decyption other than a quick hash of the criteria.
4. Yes, store SSN numbers secure + offsite on given intervals. Nightly dumps?

Just ask, I'm happy to help.

[HoganConsulting]

Pekka,

Why would I want to store both the hash and encrypted version? Why wouldn't I just encrypt the SQL search parameter and then attempt to match the encrypted input to a stored encrypted value? Wouldn't two encrypted versions of the same string be the same if I used the same encryption algorithm?

[mvierow]

You hash a value you know, then compare it to existing hashed values. But if you don't know the value to begin with, then you need a key. Thats where the aes/3des/blowfish encrypted version comes in.

http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=e546373d

[Pekka]

Hogan

I would store the hash, because IMHO it will be faster to do the lookup against the hash, the AES encrypted string should be stored base64 encoded as well, so it will be somewhat complex / expensive to do the lookup.

I think the AES version should only be used for archival purposes only.

[HoganConsulting]

Why not store the AES encrypted version as a UUencoded VARCHAR? If both the hash and AES strings are stored the same way, why would one string search be faster than another? The AES encrypted versions of the SSN's are 26 characters long. The HASH is 32 characters. It seems like searching the UUencoded AES string would be faster than the same Hashed version. Rather than opinion, do we have any actual "experience" to justify one way or the other? I don't only want to use encryption for backup purposes only, it’s also a second line of defense in case the database security is compromised. The AES encryption scheme should be an integral part of the standard data security model. Are there any readers with real-world experience who can interject?

[Guest]

On the hash issue...
If you are only searching for exact matches then hash buys you nothing.
Search would be something like "where #MyEncryptFunc(SSN)# = EncryptedDB_Column".
Just have an index on EncryptedDB_Column and performance is fine.

In fact, a hash can be risk because all hashes are now crackable and having both an encrypted and a hashed column decreases the time needed to crack either scheme.

If you need to do a range search for some reason, then you would build up an index of "partial-string hashes". You probably don't need this for SSN though.

Overall, the key things are:

1. 
   
2. Never store keys in the payload database.
   
3. Never have the keys in any code: SQL, CFM, etc.
   
4. You need at least 3 systems, ideally on 3 separate machines: (a) The payload database, (b) The web app code, (c) The secure key repository/source.
   
5. The Web app accepts/sends all data to/from browsers via SSL.
   
6. Changing the password will require a full pass through the SSN column to reencrypt but you must do this frequently anyway.
   
7. You need a way to associate the correct password with a given DB backup but do not store these together.
   
8. Put audit mechanisms in place.
   
9. Have at least two enemies overseeing each other for admin duties. I like the old air force tactic of giving each a pistol with orders to shoot at the first sign of a security breach.
   

[HoganConsulting]

Thanks MikerRoo, I came to the same conclusions about HASH security and searching.