Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


When to use Transparent Data Encryption (TDE)?


When to use Transparent Data Encryption (TDE)?

Author
Message
Ryan Kilkenny
Ryan Kilkenny
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 88
My company has been getting a lot of requests from our clients to put into place more stringent security practices. I just received the word from our CIO that all Personal Information (PI) must be encrypted at rest across all our servers. Which means we have to encrypt this data while its in the database.

My first inclination was to turn on TDE, but after reading several articles, I'm worried about losing performance. If we choose to encrypt at the column-level, it will require us to redesign some of the applications using these databases, which will probably be cost-prohibitive.

Is there any advice out there from previous experiences regarding TDE that would help me decide the best path forward? I should say also that we are currently using SQL Server Standard Edition, and I realize that means we'll have to upgrade to Enterprise.

Much appreciated!
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
If you're serious about protecting "at rest" data in all scenarios including protecting the mdf and ldf should someone get access to the server's file system then TDE is the way to go. If at rest only means protecting backups to your manager then you could go with a backup solution that can encrypt backups as they are taken, maybe something like Red Gate's SQL Backup Pro.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
Regarding the use of TDE in general, there will be a performance hit for CPU so plan on it...and if you;re leveraging native backup compression to keep your backup sizes smaller you'll lose that benefit once you move to TDE.

As for measuring the hit...you can test your existing workload on similar hardware using a replay trace where the database you replay into is encrypted using TDE. That should give you a good idea of the additional overhead you'll be signing up for. Just be careful taking a replay trace from a production system. If you're near a tipping point on any major resource taking a replay trace could cause you some issues.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Ryan Kilkenny
Ryan Kilkenny
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 88
Thanks. I'm going to try to push back on the requirements and go with encrypted backups and/or column-level encryption where it's absolutely necessary. It would be nice to hear of some success stories where using TDE has actually done some good. I'm not convinced TDE is very useful at this point.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8782 Visits: 16555
kilkenny (2/27/2012)
My company has been getting a lot of requests from our clients to put into place more stringent security practices. I just received the word from our CIO that all Personal Information (PI) must be encrypted at rest across all our servers. Which means we have to encrypt this data while its in the database.

My first inclination was to turn on TDE, but after reading several articles, I'm worried about losing performance. If we choose to encrypt at the column-level, it will require us to redesign some of the applications using these databases, which will probably be cost-prohibitive.

Is there any advice out there from previous experiences regarding TDE that would help me decide the best path forward? I should say also that we are currently using SQL Server Standard Edition, and I realize that means we'll have to upgrade to Enterprise.

Much appreciated!

TDE does not encrypt objects in the database and prevent them from being viewed. Anybody with access to the server will be able to query the database and read data.

TDE encrypts the files at rest and subsequently any backups

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
kilkenny (2/27/2012)
My first inclination was to turn on TDE, but after reading several articles, I'm worried about losing performance. If we choose to encrypt at the column-level, it will require us to redesign some of the applications using these databases, which will probably be cost-prohibitive.


TDE and column-level encryption are two completely different things for completely different purposes.

TDE protects the data at rest. It encrypts the database file on disk and the backups. It means that if a backup is stolen or someone manages to copy the data files, they cannot restore or attach the database without having the certificates, however anyone connecting to the database sees only unencrypted data.

Column encryption protects data (specific columns) from unauthorised queries. If someone queries the table without first having opened the key (which required specific database-level permissions), they see encrypted data only. Any sysadmin has permission automatically on the keys and hence can always decrypt data. For that reason this is no protection against someone stealing the backup or data file as they would have sysadmin permission on their own SQL instances and hence could decrypt the data.

Consider what you are protecting against and pick the appropriate method. Using the wrong one to protect against a thread is worse than useless.


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


Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4591 Visits: 9529
Perry Whittle (3/4/2012)
kilkenny (2/27/2012)
My company has been getting a lot of requests from our clients to put into place more stringent security practices. I just received the word from our CIO that all Personal Information (PI) must be encrypted at rest across all our servers. Which means we have to encrypt this data while its in the database.

My first inclination was to turn on TDE, but after reading several articles, I'm worried about losing performance. If we choose to encrypt at the column-level, it will require us to redesign some of the applications using these databases, which will probably be cost-prohibitive.

Is there any advice out there from previous experiences regarding TDE that would help me decide the best path forward? I should say also that we are currently using SQL Server Standard Edition, and I realize that means we'll have to upgrade to Enterprise.

Much appreciated!

TDE does not encrypt objects in the database and prevent them from being viewed. Anybody with access to the server will be able to query the database and read data.

TDE encrypts the files at rest and subsequently any backups

Starting with SQL Server 2005, users what are not members of the SYSADMIN role don't by default have access to view object definitions. So, SELECT permission on a table or EXEC permission on a stored procedures doesn't confer permission to view the object definition. They must first be explicitly graned VIEW DEFINITION permission.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
There are several other issues that might play into the decision.

Column-level encryption (CLE) does not allow the indexing of the encrypted column. If that column is in a WHERE clause, that could be a performance issue. Certainly would be an I/O issue, since all rows would have to be loaded and decrypted to check the information for the WHERE. If you use a Certificate (even self-created), then use that to protect a Symmetric key, which in turn is used to protect the data in an encrypted field, then simply restoring a backup to another server wouldn't allow someone to see the data, since the certificate is protected by the Database Master Key (goes with the db backup), which in turn is protected by the System Master Key (does not go with the db backup). Unless this SMK is backed up and restored to the new server before the encrypted database is restored, the data is not viewable by even an SA, since it has no way to decrypt the data correctly. CLE even works in Express Edition! (2005 and up). Much more programming issues, since the encryption and decryption has to be performed (can be mitigated with the use of Stored Procedures to hide the complexity). Varbinary data type is the only data type allowed for encrypted columns.



TDE encrypts whole databases (live data files, backups, tlogs, etc.). It allows normal index usage, since normal datatypes are used for the columns. TDE does require the encryption of TempDB, even if your target database is the only one to get TDE, the TempDB must still be encrypted. That could affect other databases on the same instance, even if not encrypted themselves. TDE is at the I/O level (between the disk and the memory), so the memory is unencrypted, and as such, it does not encrypt data that bypasses the buffer pool (FILESTREAM). No special programming has to be done. All objects are used normally.


A great book on the subject is "Expert SQL Server 2008 Encryption", 2009, Apress, Michael Coles and Rodney Landrum
(I have no affiliation or financial interest in the sale of this book).

Hope this helps.
vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
Here is a link to a previous post, where I gave more info, and gave some scripts to play with to learn CLE.

http://www.sqlservercentral.com/Forums/Topic1152176-391-1.aspx
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8782 Visits: 16555
Eric M Russell (3/5/2012)
Perry Whittle (3/4/2012)
kilkenny (2/27/2012)
My company has been getting a lot of requests from our clients to put into place more stringent security practices. I just received the word from our CIO that all Personal Information (PI) must be encrypted at rest across all our servers. Which means we have to encrypt this data while its in the database.

My first inclination was to turn on TDE, but after reading several articles, I'm worried about losing performance. If we choose to encrypt at the column-level, it will require us to redesign some of the applications using these databases, which will probably be cost-prohibitive.

Is there any advice out there from previous experiences regarding TDE that would help me decide the best path forward? I should say also that we are currently using SQL Server Standard Edition, and I realize that means we'll have to upgrade to Enterprise.

Much appreciated!

TDE does not encrypt objects in the database and prevent them from being viewed. Anybody with access to the server will be able to query the database and read data.

TDE encrypts the files at rest and subsequently any backups

Starting with SQL Server 2005, users what are not members of the SYSADMIN role don't by default have access to view object definitions. So, SELECT permission on a table or EXEC permission on a stored procedures doesn't confer permission to view the object definition. They must first be explicitly graned VIEW DEFINITION permission.

yes, sorry my bad. I meant database, the point i'm getting across is whether you apply TDE or not if someone has access to query an object in a TDE protected database the data itself is still viewable ;-)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search