Hash Social Security Number

Question

Hash Social Security Number

texpic

SSCertifiable

Points: 5887
More actions
April 18, 2019 at 4:41 am

#3441901
I have to hash some social security numbers. There are some customers with more than one account, the social security can be used to link these customers. For purposes of what I'm doing, I do not need to recover the number so hashing it is fine. I'd also like to be able to search using the hashed value; that is the reason for fn_varbintohexstr.
QUESTION: Is this a prudent/safe way to hash a number like a social security number or other confidential data like a credit card number, etc? If not, any suggestions on something better (would also like to be able to search on the hashed result). Thanks.
```
SELECT CAST(SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA2_512', '123456789')), 3, 32) as varchar(max))
```

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

First of all, you don't want it to be a VARCHAR(MAX). You can't index LOBs.

Second of all, there aren't many SSNs and it wouldn't take much more than a day for my 7 year old laptop to generate a SHA2-512 "Rainbow" table for every SSN there is so hashing SSNs by themselves simply isn't enough. You really should do proper encryption along with some form of "salt".

--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)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

No disagreement with what Jeff says, but I only wanted to point out that there is no reason to use fn_varbintohexstr (unless you are still on SQL 2005), since you can use a convert style to get a hexstring:

SELECT SUBSTRING(convert(varchar(32), HashBytes('SHA2_512', '123456789'), 1), 3, 32)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Good point Erland, fn_varbintohexstr is to be avoided!

😎

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Heh... I wasn't going to tell the OP about that... I didn't want to make it any easier for him to do it the wrong way. The SSNs have to be properly encrypted with "salt" and not simply hashed even if it is only one way.

--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)

ScottPletcher SSC Guru Points: 101120 More actions · Answer 5

Under no circumstances should you use the actual SSN to link accounts. You might have to store the SSN in one place, but you sure don't have to store it in more than one.

Instead, create a separate table for the SSN and a corresponding dummy key value for the SSN, such as a guid.

You link tables using the corresponding guid, not the actual SSN or even a a hash of it.

In the one table that has the actual SSN, you protect the bejeebers out of it. Encrypt it big-time -- as above -- and perhaps explicitly DENY access to it to all standard accounts. Have one account that can get to it, and a process that specific user(s) can call that will switch context to run in the context of that account.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".