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

When to use Transparent Data Encryption (TDE)? Expand / Collapse
Author
Message
Posted Monday, February 27, 2012 6:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 5:54 PM
Points: 8, Visits: 79
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!
Post #1258583
Posted Tuesday, February 28, 2012 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1258973
Posted Tuesday, February 28, 2012 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1258978
Posted Tuesday, February 28, 2012 6:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 5:54 PM
Points: 8, Visits: 79
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.
Post #1259210
Posted Sunday, March 4, 2012 11:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 6,350, Visits: 13,672
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"
Post #1261315
Posted Sunday, March 4, 2012 11:38 AM


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 @ 4:11 AM
Points: 42,994, Visits: 36,150
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 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 #1261318
Posted Monday, March 5, 2012 8:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:39 PM
Points: 1,657, Visits: 4,740
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.
Post #1261596
Posted Monday, March 5, 2012 9:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:27 AM
Points: 191, Visits: 897
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.
Post #1261655
Posted Monday, March 5, 2012 9:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:27 AM
Points: 191, Visits: 897
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
Post #1261661
Posted Monday, March 5, 2012 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 6,350, Visits: 13,672
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"
Post #1261694
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse