Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 20, 2016 3:49 AM
Points: 5,002, Visits: 4,797
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


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 12:59 PM
Points: 1,493, Visits: 2,827
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 20, 2016 3:49 AM
Points: 5,002, Visits: 4,797
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


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 12:59 PM
Points: 1,493, Visits: 2,827
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 20, 2016 3:49 AM
Points: 5,002, Visits: 4,797
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: Yesterday @ 5:28 PM
Points: 45,116, Visits: 43,217
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, MVP, M.Sc (Comp Sci)
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 20, 2016 3:49 AM
Points: 5,002, Visits: 4,797
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: Yesterday @ 5:28 PM
Points: 45,116, Visits: 43,217
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, MVP, M.Sc (Comp Sci)
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 20, 2016 3:49 AM
Points: 5,002, Visits: 4,797
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: Yesterday @ 5:28 PM
Points: 45,116, Visits: 43,217
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, MVP, M.Sc (Comp Sci)
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