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 12345»»»

Encrypt SSN Example (TDE) Expand / Collapse
Author
Message
Posted Tuesday, July 15, 2014 12:02 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 4,243, Visits: 4,291
Does anyone have an example on encrypting a SSN Field using TDE?

Any help would be greatly appreciated.

Thank you,


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1592727
Posted Tuesday, July 15, 2014 12:12 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:44 AM
Points: 322, Visits: 1,011
http://www.sqlservercentral.com/articles/Transparent+Data+Encryption+(TDE)/66334/

The transparent part always gets me.
Post #1592738
Posted Tuesday, July 15, 2014 12:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 4,243, Visits: 4,291
Thanks for the link.

I was hoping to get a simple example to encrypt an SSN.

Thank you.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1592745
Posted Tuesday, July 15, 2014 12:28 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:44 AM
Points: 322, Visits: 1,011
The TDE is a bit misleading. You might take a look at http://msdn.microsoft.com/en-us/library/ms179331(v=SQL.100).aspx

The way we do it is:

1) create certificates/keys (only needs done once)
2) open key
3) EncryptByKey
Post #1592752
Posted Tuesday, July 15, 2014 12:43 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 4,243, Visits: 4,291
I have this example.


http://msdn.microsoft.com/en-us/library/ms179331.aspx


I can't have the SSN in the table.

I just want to encrypt an SSN in a simple manner.

Thank you for your responses.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1592755
Posted Tuesday, July 15, 2014 12:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 40,437, Visits: 36,890
TDE is entire database encryption. Transparent encryption, data encrypted on disk, decrypted in memory. It protects against the theft of the data files or the backups. It doesn't protect columns.

To encrypt a column, you want to use the encryption functions, like EncryptByKey and DecryptByKey when you're inserting/updating/deleting data.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592761
Posted Tuesday, July 15, 2014 1:00 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 4,243, Visits: 4,291
GilaMonster (7/15/2014)
TDE is entire database encryption. Transparent encryption, data encrypted on disk, decrypted in memory. It protects against the theft of the data files or the backups. It doesn't protect columns.

To encrypt a column, you want to use the encryption functions, like EncryptByKey and DecryptByKey when you're inserting/updating/deleting data.


Oh Yes thank you.

How can I do the following without creating an additional column and have one column to store the SSN?
Do you have a simple example please?

Just say for example that I have a Table Named Mytable and a Column Named SSN. How do I encrypt it?

Thank you.


http://msdn.microsoft.com/en-us/library/ms179331.aspx


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1592765
Posted Tuesday, July 15, 2014 1:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 40,437, Visits: 36,890
There's nothing about the column which makes it encrypted. You would need to encrypt the values when you insert (eg with EncryptByKey) and decrypt the values when you select then (eg with DecryptByKey)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592768
Posted Tuesday, July 15, 2014 1:23 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 4,243, Visits: 4,291
GilaMonster (7/15/2014)
There's nothing about the column which makes it encrypted. You would need to encrypt the values when you insert (eg with EncryptByKey) and decrypt the values when you select then (eg with DecryptByKey)


So this needs to be done via .NET, etc or what?



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1592773
Posted Tuesday, July 15, 2014 1:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 40,437, Visits: 36,890
No. T-SQL, with the T-SQL encryption functions like EncrypByKey and DecryptByKey, or whichever of the similar functions you identify as meeting your requirements for protection, key management and all the rest of the admin around encryption.

You certainly can do it in .Net if you want with whatever the .Net encryption functions are.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592782
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse