trimming SSNs

Question

trimming SSNs

Viewing 7 posts - 31 through 36 (of 36 total)

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

ScottPletcher SSC Guru Points: 101103 More actions · Answer 1

RonKyle - Tuesday, March 19, 2019 1:30 PM
For all of you advocating encryption, how do you stored SSNs? Fortunately I don't deal with data requiring this level of protection, but I'm curious how you do it.

Column encryption: ENCRYPTBYASYMKEY. With, of course, insanely convoluted passwords.

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".

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 2

ScottPletcher - Tuesday, March 19, 2019 2:23 PM
RonKyle - Tuesday, March 19, 2019 1:30 PM
For all of you advocating encryption, how do you stored SSNs? Fortunately I don't deal with data requiring this level of protection, but I'm curious how you do it.
Column encryption: ENCRYPTBYASYMKEY. With, of course, insanely convoluted passwords.

Thank you. I will have to experiment with this on my dev version at home.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

ScottPletcher - Tuesday, March 19, 2019 2:23 PM
RonKyle - Tuesday, March 19, 2019 1:30 PM
For all of you advocating encryption, how do you stored SSNs? Fortunately I don't deal with data requiring this level of protection, but I'm curious how you do it.
Column encryption: ENCRYPTBYASYMKEY. With, of course, insanely convoluted passwords.

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

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 4

I read something that asymmetric encryption was resource intensive and that the recommended solution is symmetric encryption with the key encrypted using asymmetric encryption. Any comment?

Regarding "salt", if the field is char(9) or char(11), how do you add salt? There's no room left.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

RonKyle - Wednesday, March 20, 2019 6:45 AM
I read something that asymmetric encryption was resource intensive and that the recommended solution is symmetric encryption with the key encrypted using asymmetric encryption. Any comment?
Regarding "salt", if the field is char(9) or char(11), how do you add salt? There's no room left.

I wasn't the one that did the encryption for SSNs at work but I do know that it didn't end up being just a char(9) or char(11) and I do know that a "Salt" was added. The reason why you can't go without the "Salt" is because there are only 10 billion possibilities for SSNs. If you use something like a HASHBYTES solution, a "rainbow table" is a quick and easy way to reverse the supposedly unreversable.

Also remember that slow is a whole lot better than hacked.

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

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 6

Jeff Moden - Wednesday, March 20, 2019 7:01 AM
RonKyle - Wednesday, March 20, 2019 6:45 AM
I read something that asymmetric encryption was resource intensive and that the recommended solution is symmetric encryption with the key encrypted using asymmetric encryption. Any comment?
Regarding "salt", if the field is char(9) or char(11), how do you add salt? There's no room left.
I wasn't the one that did the encryption for SSNs at work but I do know that it didn't end up being just a char(9) or char(11) and I do know that a "Salt" was added. The reason why you can't go without the "Salt" is because there are only 10 billion possibilities for SSNs. If you use something like a HASHBYTES solution, a "rainbow table" is a quick and easy way to reverse the supposedly unreversable.
Also remember that slow is a whole lot better than hacked.

It would be helpful if someone has more information on adding salt to fixed length columns. Salt in cryptography is random characters/words before and/or after the message to further scramble them. The failure of many German enigma operators to add salt, as they were supposed to, helped the British to break the codes. But that doesn't seem to apply to fixed length fields. //edited for grammer

ScottPletcher SSC Guru Points: 101103 More actions · Answer 7

RonKyle - Wednesday, March 20, 2019 7:26 AM
Jeff Moden - Wednesday, March 20, 2019 7:01 AM
RonKyle - Wednesday, March 20, 2019 6:45 AM
I read something that asymmetric encryption was resource intensive and that the recommended solution is symmetric encryption with the key encrypted using asymmetric encryption. Any comment?
Regarding "salt", if the field is char(9) or char(11), how do you add salt? There's no room left.
I wasn't the one that did the encryption for SSNs at work but I do know that it didn't end up being just a char(9) or char(11) and I do know that a "Salt" was added. The reason why you can't go without the "Salt" is because there are only 10 billion possibilities for SSNs. If you use something like a HASHBYTES solution, a "rainbow table" is a quick and easy way to reverse the supposedly unreversable.
Also remember that slow is a whole lot better than hacked.
It would be helpful if someone has more information on adding salt to fixed length columns. Salt is cryptography is random characters/words before and/or after the message to further scramble them. The failure of many German enigma operators to add salt, as they were supposed to, helped to British to break the codes. But that doesn't seem to apply to fixed length fields.

I would think encrypted data would always be varbinary. After decrypting it, you can cast it back to char (or whatever non-binary data type).

I think asymmetric encryption removes the need for a "salt" value (but I am not any kind of expert on encryption). And, yes, it does take more resources. That's ok here because very, very few people should ever see an unencrypted SSN or even part of it. I can't imagine when/why you'd ever need to display the last-four chars in any output (again, except in extremely limited cases to an extremely limited set of people).

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".