Encrypt SSN Example (TDE)

Question

Post reply

Encrypt SSN Example (TDE)

Welsh Corgi

SSC Guru

Points: 116520
More actions
July 15, 2014 at 12:02 pm

#309077

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/

Viewing 15 posts - 1 through 15 (of 95 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

http://www.sqlservercentral.com/articles/Transparent+Data+Encryption+(TDE)/66334/

The transparent part always gets me.:-)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

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/

djj SSCoach Points: 18831 More actions · Answer 3

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

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/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

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/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

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/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

GilaMonster (7/15/2014)
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.

Thank you very much! 🙂

So I can do it in T-SQL?

Do you have an example where you had to encrypt and decrypt the SSN or TAX ID Number please.

The following article threw me off.

http://msdn.microsoft.com/en-us/library/ms174361.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/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

That's the exact article I would have given you as an example. See the other examples that it links to.

Bear in mind this isn't something you implement without a fair amount of thought, consideration and design. Otherwise you can end up compromising performance without actually gaining anything security-wise. You also need to have analysed threats and identified exactly what you're trying to protect against.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

First entry when I used BING on the phrase encryptbykey sql server 2008.

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

EDIT: Sorry, same link you posted except you link is broken. It went nowhere when I clicked on it. I had to copy it from an editable page to see where it went.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

GilaMonster (7/15/2014)
That's the exact article I would have given you as an example. See the other examples that it links to.
Bear in mind this isn't something you implement without a fair amount of thought, consideration and design. Otherwise you can end up compromising performance without actually gaining anything security-wise. You also need to have analysed threats and identified exactly what you're trying to protect against.

ok, I need to find a link for dummies. One that has an example of encrypting and decrypting the same column.

Sorry.

Has anyone done this? Does anyone have any code that they can share?

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/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 14

Welsh Corgi (7/15/2014)
One that has an example of encrypting and decrypting the same column.

You don't encrypt columns. Get that idea out of your head, it's probably causing your confusion. There is nothing special about the column. It is not flagged as encrypted, it is not a special data type or setting. It is a stock-standard varbinary column.

You encrypt data when you insert it. So as part of your insert statement.

You decrypt data when you retrieve it. So as part of your select statement.

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