November 19, 2008 at 8:10 pm
Hello everyone,
I have recently started with a new company. One of the things that I have noticed is the very low level of security that the database has (i.e. all db members are using SA access, etc.) Probably the largest I current see right now is that they are NOT encrypting the SSN of the user. Mind you we are running roughly 50 client databases with close to almost 1 Million users. All of this is not encypted and is being passed to the front end all nice and visable like.
Obviously, this needs to change. The question I am interested in are what are the different ways each of you secure you SSN if the have to be viewed on the front end.
Do you encrypt while in the database, and then decript before sending it to the front end? Do you let the front end decrypt? Do you only encrypt the first 5 numbers of the SSN while leaving the last 4 for viewing? Or do you encrypt the entire thing?
I am trying to figure out what options are out there, if there is a norm, and what options that I haven't though of yet.
Thank you very much for your assistance.
Fraggle
November 19, 2008 at 8:26 pm
On a database that originated when the app was built on SQL Server 2000, we encrypt at the app level. On a more recent retrofit of an older app, we build the encryption into the database and let SQL Server handle key escrow. But our private network between the app and the database is considered trusted.
K. Brian Kelley
@kbriankelley
November 20, 2008 at 7:27 am
I prefer to encrypt in the front end so that the data is not traveling over the wire unencrypted. I also like the way .NET does encryption better then SQL Server encryption. I believe you also need to encrypt the whole thing or you need to store the last 4 in clear text in a separate column as the encrypted value is binary.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 20, 2008 at 9:58 pm
Both are great idea. I personally like Jack's option right now, but that would be going forward. How would we go about encrypting everything in the database already, or can this be done via .net as well (forgive me, my .net experience is limited). Also, we deal with international users, which don't follow the same logic as our SSN. Any idea on how to compensate for those?
Thanks,
Fraggle
November 20, 2008 at 10:29 pm
Depends what you're trying to achieve with encryption. Remember that SQL's encryption cannot protect the data against the DBA.
As for what to encrypt, you need to identify what's sensitive data. That's a business decision and should be made at that level. It's not a technical decision. Don't encrypt everything. You will kill performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2008 at 6:50 am
What we have done is create an Encryption DLL in .NET that is used in the UI (actually businaess layer) that we signed so I could use it in SSIS. Granted I was taking data from one system and migrating it to SQL Server, but the same idea could be used to migrate within a DB. You could create teh DLL for the UI, include in an SSIS package that takes the plain text data, ecrypts it and then updates the table setting a value ina new column for the encrypted value. Or, migrates the entire table to a new table that you would then rename to replace the existing table (dropped).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 21, 2008 at 7:38 pm
Gila,
I would never encrypt everything as that would just be silly unless I worked for the NSA or Area 51 or something. 🙂
I am just trying to see how others in our wonderful group encypt SSN/TaxID's/foriegn SSN/etc.
Jack,
The bad news is that I reallly only understood about 1/2 of what you said. The good news, is one of the .NET guys understood perfectly and is taking a look at it.
Anyone else have ideas?
Thanks
Fraggle
November 22, 2008 at 7:03 am
At least someone understood it. I wouldn;t have understood until about 6 months ago, when I had to deal with it. I think I even posted a similar question on SSC.;)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply