Encrypting data within the DB

[kimi]

Hello,
I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I have
been working with SQL Server, Building ASp WebSites for many years now.
I am by no means an expert - nor have I had ANY formal training. So ebar
with me if my questions seem elementary...

I have some questions regarding sensitive data and encryption.

There is a project that is headed my way were the social security number
is being used as the unique identifier for an account. I have always
used as identity column as a unique identifier. What would be the pros
and cons of using the SSN as a unique identifier?

#1 How do I go about encrypting the number to store in the DB. Is this
done within SQL Server? Or before the data is inserted?

#2 Is it possible to use an encrypted field as a unique identifier?

My gut tells me to use the identity column , encrypt the SSN and not use
it as any part of an identifier.

Thank You for your Help.. Happy New Year!

Please Reply to the Newsgroup.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[J00 Moo]

It is not common but it is possible for a Social Security number to change
given time. It should certainly be a way for people to quickly find
individuals however I dont think it it should be a primary key --

As far as encryption goes check out the source code for the forums that are
available at www.webwizguide.com -- there is a very nice encryption system
built into the software that I have used many a time and I cant knock it --

"kimi" <ki*@kimmyXSPAMX.com> wrote in message
news:3f***********************@news.frii.net...

Hello,
I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I have
been working with SQL Server, Building ASp WebSites for many years now.
I am by no means an expert - nor have I had ANY formal training. So ebar
with me if my questions seem elementary...

I have some questions regarding sensitive data and encryption.

There is a project that is headed my way were the social security number
is being used as the unique identifier for an account. I have always
used as identity column as a unique identifier. What would be the pros
and cons of using the SSN as a unique identifier?

#1 How do I go about encrypting the number to store in the DB. Is this
done within SQL Server? Or before the data is inserted?

#2 Is it possible to use an encrypted field as a unique identifier?

My gut tells me to use the identity column , encrypt the SSN and not use
it as any part of an identifier.

Thank You for your Help.. Happy New Year!

Please Reply to the Newsgroup.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"kimi" <ki*@kimmyXSPAMX.com> wrote in message
news:3f***********************@news.frii.net...

Hello,
I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I have
been working with SQL Server, Building ASp WebSites for many years now.
I am by no means an expert - nor have I had ANY formal training. So ebar
with me if my questions seem elementary...

I have some questions regarding sensitive data and encryption.

There is a project that is headed my way were the social security number
is being used as the unique identifier for an account. I have always
used as identity column as a unique identifier. What would be the pros
and cons of using the SSN as a unique identifier?

#1 How do I go about encrypting the number to store in the DB. Is this
done within SQL Server? Or before the data is inserted?

#2 Is it possible to use an encrypted field as a unique identifier?

My gut tells me to use the identity column , encrypt the SSN and not use
it as any part of an identifier.

Thank You for your Help.. Happy New Year!

Please Reply to the Newsgroup.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

1. There is no built-in encryption mechanism in MSSQL, but there are a
number of third-party products available:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Alternatively, use the Windows CrpytoAPI from your front end application to
encrpyt/decrypt as you access the data, so the database only ever stores the
encrypted value.

2. Any column which has guaranteed unique values could be a key, but your
encryption solution might produce something unsuitable, such as a very long
value which would be awkward to work with.

Personally, I would go with your proposed solution, and avoid the SSN as a
key. For example, if your solution ever goes international, then basing
everything on an SSN will suddenly become an issue. An artificial key of
some sort is a useful solution. But ultimately you have to decide based on
your requirements.

Simon

[kimi]

>As far as encryption goes check out the source code for >the forums
that are

available at www.webwizguide.com -- there is a very nice >encryption systembuilt into the software that I have used many a time and I >cant knock

it --

Thank you for the link above. I will check this out first chance I get.
I am somewhat familiar with this forum. Thanks.
Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

>but there are a number of third-party products available:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
Have you used any of the third party software to encrypt?

Alternatively, use the Windows CrpytoAPI from your front >end application toencrpyt/decrypt as you access the data, so the database >only ever stores theencrypted value.
Well this is what I want - but this brings up antoher concern. If the
value stored is encrypted - Would this mean I could never search by SSN?

Personally, I would go with your proposed solution, and >avoid the SSN as akey. For example, if your solution ever goes >international, then basingeverything on an SSN will suddenly become an issue. An >artificial key ofsome sort is a useful solution. But ultimately you have to >decide

based on your requirements.

I agree - I think I am going to push for this - and your point about
international possibilities will be my defense.

Thank you for your help.
Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[J00 Moo]

Nah it just means you would have to decript the SSN field as part of the
where clause -- probably not that fast as this would lead to indexes being
thrown off but hey --

What I am not quite sure of is why the SSN has to be encrypted in the first
place? Unless somebody is going to have access to the server that should
not I just dont get it -- maybe I am not as cautious as some however

"kimi" <ki*@kimmyXSPAMX.com> wrote in message
news:3f***********************@news.frii.net...

but there are a number of third-party products available:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Have you used any of the third party software to encrypt?

Alternatively, use the Windows CrpytoAPI from your front >end

application to

encrpyt/decrypt as you access the data, so the database >only ever

stores the

encrypted value.

Well this is what I want - but this brings up antoher concern. If the
value stored is encrypted - Would this mean I could never search by SSN?

Personally, I would go with your proposed solution, and >avoid the SSN

as a

key. For example, if your solution ever goes >international, then

basing

everything on an SSN will suddenly become an issue. An >artificial key

of

some sort is a useful solution. But ultimately you have to >decide

based on your requirements.

I agree - I think I am going to push for this - and your point about
international possibilities will be my defense.

Thank you for your help.
Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Madison Pruet]

because customers are more willing to give you their SSN if they know that
it is secure.
"J00 Moo" <no************************@hotmail.com> wrote in message
news:bt************@ID-68406.news.uni-berlin.de...

Nah it just means you would have to decript the SSN field as part of the
where clause -- probably not that fast as this would lead to indexes being
thrown off but hey --

What I am not quite sure of is why the SSN has to be encrypted in the first place? Unless somebody is going to have access to the server that should
not I just dont get it -- maybe I am not as cautious as some however

"kimi" <ki*@kimmyXSPAMX.com> wrote in message
news:3f***********************@news.frii.net...

but there are a number of third-party products available:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Have you used any of the third party software to encrypt?

Alternatively, use the Windows CrpytoAPI from your front >end

application to

encrpyt/decrypt as you access the data, so the database >only ever

stores the

encrypted value.

Well this is what I want - but this brings up antoher concern. If the
value stored is encrypted - Would this mean I could never search by SSN?

Personally, I would go with your proposed solution, and >avoid the SSN

as a

key. For example, if your solution ever goes >international, then

basing

everything on an SSN will suddenly become an issue. An >artificial key

of

some sort is a useful solution. But ultimately you have to >decide

based on your requirements.

I agree - I think I am going to push for this - and your point about
international possibilities will be my defense.

Thank you for your help.
Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

kimi (ki*@kimmyXSPAMX.com) writes:

Well this is what I want - but this brings up antoher concern. If the
value stored is encrypted - Would this mean I could never search by SSN?
Searching for a single value should not be much of a problem. You encrypt
the input and then look up the encrypted value. Of course, you must use
an encryption method which always gives the same encrypted value for the
same input.

Search for a range, like all SSN that start on 500 would be more
problematic.

Going back to your original post:What would be the pros and cons of using the SSN as a unique identifier?

The pro would be that you would get a tap on your shoulder by Joe Celko.

But as noted by others, SSN is a typical example of something that looks
like a natural key, but when you look closer to it, does not live up to
the strict requirements for a primary key in a relational database. A
fact which is true many natural look-a-like keys.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[kimi]

>But as noted by others, SSN is a typical example of >something that
looks like a natural key, but when you look >closer to it, does not live
up to the strict requirements >for a primary key in a relational
database. A

fact which is true many natural look-a-like keys.

Would you consider a phone number to be a good choice for a primary key?

As I understand the data that is to be stored right now.
The only options I have are ssn, phone number, or identity column.

I am so leaning toward the identity.
Thank you for your replies
Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

Upon further ingestion of coffee I realize that a phone number as a
primary key would be a BAD Move...

I am sticking with the identity column - i see it as my only option
right now.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"kimi" <ki*@kimmyXSPAMX.com> wrote in message
news:3f***********************@news.frii.net...

Upon further ingestion of coffee I realize that a phone number as a
primary key would be a BAD Move...

I am sticking with the identity column - i see it as my only option
right now.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The identity column is probably the best approach. The fundamental issue as
I see it is that there is no such thing as a natural key for human beings,
or at least no key that is practical in relational database terms. In some
specific contexts, it might be possible to use something like an email
address, but even then a surrogate key might be required purely for
performance.

You could try to make a composite key out of numerous fields (names, phone
numbers, addresses etc), but it would be completely impractical, even if you
could be sure that everyone in your database will always have an email
address and a phone. I suppose you might theoretically be able to produce a
unique hash value from someone's DNA, but even if it could be made small
enough to work in a practical implementation, you'd still have a problem
when you get your first set of identical twin customers/employees/whatever.

SSNs, passport numbers, employee IDs etc. are essentially artificial keys at
the end of the day, so there's no reason to be reluctant to use one
yourself, at least in my view.

Simon

[--CELKO--]

>> The only options I have are ssn, phone number, or identity column.
<<

Go down the list and ask yourself which of the three can be verified
in the reality you are modeling. I can call someone and ask for his
phone number. I can call the government and verify his SSN. IDENTITY
by definition is never a key -- just put the same data into the table
1000 times on a table that uses such a pseudo-key and trash your data
integrity.

What is the nature of the application? Do you require the phone
number or can someone qualify for membership in the set without one?
Probably. Do you require the SSN or can someone qualify for membership
without one? Probably not. If I hire a foreign national, I have to
get a tax number for him in a business environment. So you allow
sloppy SSNs only if you want to go to jail under INS, IRS and Homeland
Security laws. You not only have to have it on file as a matter of
law, you have to verify it as a matter of law.

The bad news is that simple verification is a pain with SSN. Buy a
package or find some freeware to save yourself the pain.

[lindawie]

Joe,

I can call someone and ask for his phone number.

And don't forget to ask "Is this the party to whom I am speaking?"
Linda