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!

[Simon Hayes]

"kimi" <ki*@kimmyXSPAM X.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