Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best way to store SSN SQL Server 2008 Expand / Collapse
Author
Message
Posted Tuesday, August 13, 2013 7:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 13, 2015 2:34 PM
Points: 100, Visits: 243
The system I am helping maintain must store SSNs - no choice there. What's the best way/practice to store this? I imagine I probably want to encrypt it somehow in the field properties?

If it helps with suggestions, this is an internal app - the data will never be exposed to the outside world.

Thanks!
Post #1483707
Posted Tuesday, August 13, 2013 7:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 13,893, Visits: 13,467
RedBirdOBX (8/13/2013)

If it helps with suggestions, this is an internal app - the data will never be exposed to the outside world.
Thanks!


You mean never intentionally. You need to store this type of data encrypted. http://technet.microsoft.com/en-us/library/bb934049.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1483711
Posted Tuesday, August 13, 2013 9:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 5,331, Visits: 4,810
I believe TDE really only protects against the smash-and-grab risk. People with SELECT permission on the table can still access the data - it's transparent to them. If you want to store it encrypted, start here: http://technet.microsoft.com/en-us/library/bb964742%28v=sql.100%29.aspx TDE also requires Enterprise Edition, which not everyone has the luxury of working with.

In a nutshell, you'll need to create keys and then encrypt the data using the keys. A simple word of advice if you decide to go this route is to backup your keys. There are commands to do this referenced in the above page, but I prefer to also keep the SQL statements I use to originally create the key. This way, I know I can recreate the key when needed. I was burned by this once and I vowed it wouldn't happen to me again.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1483816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse