Need a So-Called SSN Encryption

[ILCSP]

Hello, perhaps you guys have heard this before in the past, but here is
what I'm looking for.

I have a SQL 2000 table with Social security numbers. We need to
create a Member ID using the Member's real SSN but since we are not
allowed to use the exact SSN, we need to add 1 to each number in the
SSN. That way, the new SSN would be the new Member ID.

For example:

if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.

Sounds simply enough, but I can't seem to get it straight.

I need this number to be created using a query, as this query is a
report's record source.

Again, any help would be appreciated it.

[David Portas]

IL***@NETZERO.NET wrote:

Hello, perhaps you guys have heard this before in the past, but here is
what I'm looking for.

I have a SQL 2000 table with Social security numbers. We need to
create a Member ID using the Member's real SSN but since we are not
allowed to use the exact SSN, we need to add 1 to each number in the
SSN. That way, the new SSN would be the new Member ID.

That's almost totally ineffective if the idea is to protect the SSN
from disclosure. Why don't you use a hash of the number instead? A
secure one like SHA1 for example.

If you must, then something like this should do it:

REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(@ssn,'0','#')
,'9','0'),'8','9'),'7','8'),'6','7'),'5','6')
,'4','5'),'3','4'),'2','3'),'1','2'),'#','1')

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[figital]

I agree - it's pretty lame that this method allows you to "comply" with
the policy.

Then again, when my school switched from using SSNs to "Student IDs",
it was a nightmare--but only because no one could remember them.

[ILCSP]

Hi David, I totally agree with you. However, this does the job. I
tested and the numbers do get increased by 1.

And to think I was trying to do Case..WHEN.. END statements. Gosh!

[--CELKO--]

One method is to create a table of single, pairs, triplets etc. like
this

CREATE TABLE TripletEncode
(in_triplet CHAR(3) NOT NULL PRIMARY KEY
CHECK(in_triplet LIKE '[0-9][0-9][0-9]',
out_triplet CHAR(3) NOT NULL);

Break the input into substrings, and replace the characters with the
out_triplet. It is not a great system. Given some sample data, you can
figure out the areas and other repeated parts of SSN, phone numbers,
etc.

There are some modifications, like putting the last digit in front, or
using the last digit to pick which encoding to use from a table with a
extra column that goes from 0 to 9. The real trick is to be sure that
the encodings are reversible.

[DickChristoph]

Hi

Having nothing better to do this afternoon I wrote a function called
CodeDecodeSSN which has the advantage that it can be used to go from SSN to
Encrypted SSN and then from Encrypted SSN back to the originalSSN.

It has the disadvantage that the Encrypted SSN may contain Hex characters A
through F).

It probably is also slow.

The mask is arbitrary, you can select any number of the form ###-##-####'
(these must be numeric digits 0 to 9) but it must remain the same through
the life of the application.

Modify this line to set your own mask.

set @mask = '123-45-6789'
Basically the function works by doing a bitwise XOR of each digit in the
Source SSN against the corresponding digit in the @mask.

Using a hash code such as David suggested is probably a better idea, but
like i said, I had nothing better to do this afternoon. ( I wish someone
would offer me a job :) )

select dbo.CodeDecodeSSN('123-46-7890')
Produces 000-03-1F19

select dbo.CodeDecodeSSN('000-03-1F19')
Produces 123-46-7890

create Function CodeDecodeSSN(@src varchar(11))
returns varchar(11)
begin
declare @mask varchar(11)
declare @rv varchar(11)
set @mask = '123-45-6789'
declare @i int
declare @j int
declare @c int
declare @c1 char(1)
declare @c3 char(3)
declare @m int
set @i = 1
set @rv = ''
while @i <= 11
begin
if @i = 4 or @i = 7
set @rv = @rv + '-'
else
begin
Set @c3 = '%' + substring(@src,@i,1) + '%'
set @c = PatIndex(@c3,'0123456789ABCDEF') -1
Set @m = substring(@mask,@i,1)
set @c = @c ^ @m
if @c > 9
begin
set @c1 = char(ascii('A') + @c - 10)
end
else
begin
set @C1 = cast(@c as char(1))
end
set @rv = @rv + @c1
end
set @i = @i + 1
end
return @RV
end
--
-Dick Christoph
<IL***@NETZERO.NET> wrote in message
news:11********************@z34g2000cwc.googlegrou ps.com... Hello, perhaps you guys have heard this before in the past, but here is
what I'm looking for.

I have a SQL 2000 table with Social security numbers. We need to
create a Member ID using the Member's real SSN but since we are not
allowed to use the exact SSN, we need to add 1 to each number in the
SSN. That way, the new SSN would be the new Member ID.

For example:

if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.

Sounds simply enough, but I can't seem to get it straight.

I need this number to be created using a query, as this query is a
report's record source.

Again, any help would be appreciated it.

[christopher.secord]

IL***@NETZERO.NET wrote:

Hi David, I totally agree with you. However, this does the job.

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.

[DickChristoph]

Hi

Oh one more thing, this method is not particularily secure. If a hacker knew
one SSN and the associated encrypted SSN he/she could XOR them together to
determine the Mask used and then use this value to decrypt all the encrypted
SSNs in the table.

-Dick Christoph

"DickChristoph" <dc********@yahoo.com> wrote in message
news:dm******************@tornado.rdc-kc.rr.com...

Hi

Having nothing better to do this afternoon I wrote a function called
CodeDecodeSSN which has the advantage that it can be used to go from SSN
to Encrypted SSN and then from Encrypted SSN back to the originalSSN.

It has the disadvantage that the Encrypted SSN may contain Hex characters
A through F).

It probably is also slow.

The mask is arbitrary, you can select any number of the form ###-##-####'
(these must be numeric digits 0 to 9) but it must remain the same through
the life of the application.

Modify this line to set your own mask.

set @mask = '123-45-6789'

Basically the function works by doing a bitwise XOR of each digit in the
Source SSN against the corresponding digit in the @mask.

Using a hash code such as David suggested is probably a better idea, but
like i said, I had nothing better to do this afternoon. ( I wish someone
would offer me a job :) )

select dbo.CodeDecodeSSN('123-46-7890')
Produces 000-03-1F19

select dbo.CodeDecodeSSN('000-03-1F19')
Produces 123-46-7890

create Function CodeDecodeSSN(@src varchar(11))
returns varchar(11)
begin
declare @mask varchar(11)
declare @rv varchar(11)
set @mask = '123-45-6789'
declare @i int
declare @j int
declare @c int
declare @c1 char(1)
declare @c3 char(3)
declare @m int
set @i = 1
set @rv = ''
while @i <= 11
begin
if @i = 4 or @i = 7
set @rv = @rv + '-'
else
begin
Set @c3 = '%' + substring(@src,@i,1) + '%'
set @c = PatIndex(@c3,'0123456789ABCDEF') -1
Set @m = substring(@mask,@i,1)
set @c = @c ^ @m
if @c > 9
begin
set @c1 = char(ascii('A') + @c - 10)
end
else
begin
set @C1 = cast(@c as char(1))
end
set @rv = @rv + @c1
end
set @i = @i + 1
end
return @RV
end
--
-Dick Christoph
<IL***@NETZERO.NET> wrote in message
news:11********************@z34g2000cwc.googlegrou ps.com...

Hello, perhaps you guys have heard this before in the past, but here is
what I'm looking for.

I have a SQL 2000 table with Social security numbers. We need to
create a Member ID using the Member's real SSN but since we are not
allowed to use the exact SSN, we need to add 1 to each number in the
SSN. That way, the new SSN would be the new Member ID.

For example:

if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.

Sounds simply enough, but I can't seem to get it straight.

I need this number to be created using a query, as this query is a
report's record source.

Again, any help would be appreciated it.

[Mike C#]

SHA hash function:
http://www.sqlservercentral.com/colu...olkitpart4.asp
Encryption functions:
http://www.sqlservercentral.com/colu...olkitpart1.asp

<IL***@NETZERO.NET> wrote in message
news:11********************@z34g2000cwc.googlegrou ps.com...

Hello, perhaps you guys have heard this before in the past, but here is
what I'm looking for.

I have a SQL 2000 table with Social security numbers. We need to
create a Member ID using the Member's real SSN but since we are not
allowed to use the exact SSN, we need to add 1 to each number in the
SSN. That way, the new SSN would be the new Member ID.

For example:

if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.

Sounds simply enough, but I can't seem to get it straight.

I need this number to be created using a query, as this query is a
report's record source.

Again, any help would be appreciated it.

[rcamarda]

I'm tacking this very issue as well.
1. SQL Sever 2005 has symmetric and asymmetric encyption. Even tho its
slower, I testing asymmetric encryption. This way only the people that
should have access to the sSN will be able to decrypt it. The DBA's
wont even know the key.
2. On SQL Server 2000 see http://www.activecrypt.com/
This seemed to do the trick as well, but I have upgraded to 2005.
HTH
Rob