Storing encypted SSN's

Question

Storing encypted SSN's

Viewing 15 posts - 16 through 30 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

A SSN hashed using MD5 can be un-hashed by simply joining it on a dictionary table prepopulated with a billion sequential SSN codes that were hashed using the same MD5 algorithm. Using this method, you could probably un-hash a million hashed SSN within an hour.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

ram302 SSCommitted Points: 1618 More actions · Answer 2

Jeff,

Thanks for the feel-good praise :-D. Regarding your point on adding a salt and rainbow, where can I begin to read up on this?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Google "salt hash" and "rainbow table" and you'll probably have more to read than you have time for.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

First of all, I am against the use of SSN's as UID;

Aside from the debate of how to secure the SSN, it's also important to understand that SSN is often a user supplied ID just like name or phone number. It's not that SSN is unreliable, broadly speaking the SS administration does a great job of assigning a unique SSN to each citizen, it's that a significant percentage of the US population are either not citizens or for various reasons are not in the system. Also, people routinely supply a fake SSN when asked to supply one, especially if your line of business is something like a hotel or even a clinic (the type where patients walk in off the street and pay in cash). Understandably many people don't feel obliged to hand over their real SSN. You'll see a lot of this: 111-11-1111.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

GilaMonster (4/26/2016)
ram302 (4/26/2016)
I will never need to decrypt the SSN, but I will need to add/remove ones as personnel are hired/terminated.
In that case, don't look at encryption. Just hash the SSN with a strong hashing algorithm and only ever work with the hashed values. Far easier than messing with Always Encrypted.

Agreed. Use SHA2 if you don't need to decrypt.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

Steve Jones - SSC Editor (4/26/2016)
GilaMonster (4/26/2016)
ram302 (4/26/2016)
I will never need to decrypt the SSN, but I will need to add/remove ones as personnel are hired/terminated.
In that case, don't look at encryption. Just hash the SSN with a strong hashing algorithm and only ever work with the hashed values. Far easier than messing with Always Encrypted.
Agreed. Use SHA2 if you don't need to decrypt.

And use a salt, preferably one that varies a bit. Be careful of using one of the values in the SSN, however, as this likely will be easy to build a rainbow table for.

ram302 SSCommitted Points: 1618 More actions · Answer 7

Ok,

- I'll need to do a lookup using the UID (SSN), but I plan to substitute this for a hash value using some hash algorithm (SHA-1?).

- The table will NOT have plaintext SSN; it will have the HASH values in place of SSN and compare that to the hash value entered in the lookup.

I'm never decrypting this, so I don't really care to know the SSN plaintext value.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

SHA2_512 is the strongest hashing algorithm on SQL 2012. Use that unless you have a good reason to use a weaker algorithm. Edit: and salt it.

SHA1 has been out of favour since 2010.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125561 More actions · Answer 9

Post the SHA2_256 hash of any validly formatted plaintext SSN (ie: any code conforming to 999-99-9999), and I'll reply back with the un-hashed plaintext SSN number.

For example use this:

PRINT HASHBYTES('SHA2_512', '421-76-1234');

Reply back with this:

0x6960D3AC78539B038B4185368DF579A7D022D34DE7ED06F1CE378B8DB8C74FB0F78980482B1BAE12FD80A1B0AED8EC4F86DEC969A1E819B6823C63DE0F40B6D2

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Eric M Russell (4/26/2016)
A SSN hashed using MD5 can be un-hashed by simply joining it on a dictionary table prepopulated with a billion sequential SSN codes that were hashed using the same MD5 algorithm. Using this method, you could probably un-hash a million hashed SSN within an hour.

Not just MD5, either. Like you said, there are only a billion unique SSN possibilities.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 11

Eric M Russell (4/26/2016)
Post the SHA2_256 hash of any validly formatted plaintext SSN (ie: any code conforming to 999-99-9999), and I'll reply back with the un-hashed plaintext SSN number.
For example use this:
PRINT HASHBYTES('SHA2_512', '421-76-1234');
Reply back with this:
0x6960D3AC78539B038B4185368DF579A7D022D34DE7ED06F1CE378B8DB8C74FB0F78980482B1BAE12FD80A1B0AED8EC4F86DEC969A1E819B6823C63DE0F40B6D2

That's why you use a Salt. Break this:

PRINT HASHBYTES('SHA2_512', @a);

------

0xE280051E10A9665BE965D08DE2C2E9B63E6CCC8C9C7B8AAFD67A3F05499B60030C465CA90DBD93D180D2FFAF8909215616834B8B320257F11877BB31472314CA

ram302 SSCommitted Points: 1618 More actions · Answer 12

To be clear, and sorry if I'm not getting it right, I'll be hashing (with salt) these ssn values for my ssn column and these will be my lookup values that will be used in queries.

The salt will not be anything using DATETIME or RAND() since I would need this to remain constant.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 13

Steve Jones - SSC Editor (4/27/2016)
Eric M Russell (4/26/2016)
Post the SHA2_256 hash of any validly formatted plaintext SSN (ie: any code conforming to 999-99-9999), and I'll reply back with the un-hashed plaintext SSN number.
For example use this:
PRINT HASHBYTES('SHA2_512', '421-76-1234');
Reply back with this:
0x6960D3AC78539B038B4185368DF579A7D022D34DE7ED06F1CE378B8DB8C74FB0F78980482B1BAE12FD80A1B0AED8EC4F86DEC969A1E819B6823C63DE0F40B6D2
That's why you use a Salt. Break this:
PRINT HASHBYTES('SHA2_512', @a);
------
0xE280051E10A9665BE965D08DE2C2E9B63E6CCC8C9C7B8AAFD67A3F05499B60030C465CA90DBD93D180D2FFAF8909215616834B8B320257F11877BB31472314CA

For the application to view or lookup salted hash values, it would have to salt the hash using the same function / key, so at this point you might as well use symmetric key encryption instead of hashing.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Eric M Russell (4/27/2016)
at this point you might as well use symmetric key encryption instead of hashing.

I disagree. If you're using symmetric encryption and the key is compromised, all data can be decrypted. With a salted hash it can't be reversed. It can be brute forced, but that takes time with the stronger hash algorithms. Salting the hash means you can't use a simple rainbow table. It's about making things harder, not making things impossible.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125561 More actions · Answer 15

GilaMonster (4/27/2016)
Eric M Russell (4/27/2016)
at this point you might as well use symmetric key encryption instead of hashing.
I disagree. If you're using symmetric encryption and the key is compromised, all data can be decrypted. With a salted hash it can't be reversed. It can be brute forced, but that takes time with the stronger hash algorithms. Salting the hash means you can't use a simple rainbow table. It's about making things harder, not making things impossible.

Please show an example of what you and Steve mean by "salting the hash".

Wouldn't the application need to possess the same salting key and function, which is essentially the same vulnerability as a symmetric key?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho