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 Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
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: Today @ 8:56 PM
Points: 13,078, Visits: 12,529
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
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 4,202, Visits: 3,639
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