Encripting SSN

[Pat Hartman]

I am using an SQL 2005 be and I need to encrypt the SSN field. Does anyone
have VBA code examples? How do you protect the key so that it doesn't die
with the developer?

 

[Arvin Meyer [MVP]]

I am using an SQL 2005 be and I need to encrypt the SSN field. Does anyone
have VBA code examples? How do you protect the key so that it doesn't die
with the developer?

I'm not very keen on storing SSN's in any database not required by law, so
do say you weren't warned. You'll need to protect this code with an MDE and
use the same routine to decrypt. The password, must be supplied each time,
if you want it to be secure. If it's not, merely copying and pasting back
will cough up the correct answer. As far as protecting it, make a copy and
put it in the safe. Run an query to dycrypt and re-encrypt with each change
of personnel. Obviously, the longer and more complex the password key is,
the better it will protect:

Public Function Encrypt(ByVal strIn As String, ByVal strKey As String) As
String
On Error GoTo Error_Handler

Dim i As Integer
Dim bytData As Byte
Dim bytKey As Byte
Dim strEncrypted As String

Encrypt = vbNullString

For i = 1 To Len(strIn)
bytData = Asc(Mid(strIn, i, 1))
bytKey = Asc(Mid(strKey, (i Mod Len(strKey)) + 1))
strEncrypted = strEncrypted & Chr(bytData Xor bytKey)
Next i

If strEncrypted <> vbNullString Then
Encrypt = strEncrypted
End If

Exit_Here:
Exit Function

Error_Handler:
Resume Exit_Here
End Function

 

[Jeff Boyce]

Pat

Here's one of several suggestions I found via Google...

(e-mail address removed)
Guest
n/a Posts March 31st, 2006

11:25 PM
#7

Re: Need a So-Called SSN Encryption > --------------------------------------------------------------------------------


Actually no, it doesn't do the job. The point of the policy is that
your company will be in line for a major lawsuit if (when) your
database server is hacked and the SSNs are stolen and your customers
start falling victim to identity theft. Knowing this, someone wrote a
policy that you are not allowed to store SSNs. You have NOT complied
with that policy.

Someone else already offered you one very simple solution, you could
hash the SSNs and use the hash as an ID rather than the SSN. The only
thing I would add is that you should hold on to the last four digits of
the SSN and use them to resolve collisions. That you refuse to
implement this simple and effective solution actually makes me just a
little angry. I'm angry knowing that people like you, who don't care
to protect *my* personal information, are often in positions where you
have charge of my personal information.

Incidentally, the "perfect" solution to this problem can be found in
Bruce Schneier's book Applied Cryptography. I don't have it in front
of me right now but the gist of it is that you not only hash the SSNs
but you use the SSN as a key to encrypt the rest of the data. With
this system, when someone steals your user database, they wont get
anything - not even names and addresses.

Please look into this. It's the right thing to do.

Regards

Jeff Boyce
Microsoft Office/Access MPV

 

[Pat Hartman]

Thanks Arvin, you've been very helpful today. I hate keeping SSNs also but
the client is using a third party service to obtain information on broker
license information and they need SSN's for individuals and FEINs for
agencies in order to obtain the necessary license information. So we're
stuck since the company is growing so fast it has become a burden keeping up
with licensing for hundreds of brokers and doing business with an unlicensed
broker or agency runs the risk of fines and other sanctions so it is pretty
important to them.
Happy New Year!

 

[Pat Hartman]

Thanks Jeff, I've learned a lot of things about encryption today and one of
them is that hashing seems to be a one-way street. You can hash a number
but you can't get it back. That means that I need to encrypt it which
requires a key and an algorithm to code and decode the sensitive
information. I'll try the code Arvin posted and see what happens.
Happy New Year!

 

[Pat Hartman]

OOPS, Your encryption routine doesn't include the algorithm for decrypting
the string. Do you have one?

 

[Tony Toews [MVP]]

I am using an SQL 2005 be and I need to encrypt the SSN field. Does anyone
have VBA code examples? How do you protect the key so that it doesn't die
with the developer?

I've been using the Microsoft Crypto API. However I can't recall the
exact URL now.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Arvin Meyer [MVP]]

It's the same code to decrypt, which is why I said the password needs to be
entered each time. If not, someone will figure out that all they need to do
is copy it and paste it back in. When you run the AfterUpdate, It would
expose the SSN.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com