Pat Hartman said:
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
