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, March 5, 2012 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:23 PM
Points: 8, Visits: 82
Thanks everyone for the great insight. It seems in our situation that TDE is the best solution, aside from the performance hit to the server. The reason being that we're not trying to limit query access, we're only trying to protect the data at rest. Unfortunately, it feels a bit overkill, but I don't know what else to recommend that will meet the requirement, other than continuing to try to convince people that it's not necessary in the first place.
Post #1261718
Posted Monday, March 5, 2012 11:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 1,706, Visits: 4,848
kilkenny (3/5/2012)
Thanks everyone for the great insight. It seems in our situation that TDE is the best solution, aside from the performance hit to the server. The reason being that we're not trying to limit query access, we're only trying to protect the data at rest. Unfortunately, it feels a bit overkill, but I don't know what else to recommend that will meet the requirement, other than continuing to try to convince people that it's not necessary in the first place.

Of course restricting access to the physical storage system is important. However, my impression is that the majority of security breaches involving "data at rest" would be the ubiquitous scenario where an employee or contractor extracts a copy of production data to a local database or Excel sheets, and then their laptop gets lost or stolen.
Post #1261732
Posted Monday, March 5, 2012 11:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:23 PM
Points: 8, Visits: 82
I think you nailed it on the head. And in that case, the only protection is in the collaboration processes and making sure that people are handling data appropriately. Really makes TDE seem unimportant by comparison, but its much easier to implement than all the time spent checking out your contractors and employees.
Post #1261743
Posted Monday, March 5, 2012 11:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 1,706, Visits: 4,848
kilkenny (3/5/2012)
I think you nailed it on the head. And in that case, the only protection is in the collaboration processes and making sure that people are handling data appropriately. Really makes TDE seem unimportant by comparison, but its much easier to implement than all the time spent checking out your contractors and employees.

Where I work, we are regulated by HIPAA. All company assigned laptops have Symantec's PGP whole disk encryption installed, local group policy to disable USB storage devices on all desktops across the company, annual required security and PHI training, etc.
Post #1261748
Posted Tuesday, March 6, 2012 7:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:30 AM
Points: 2,885, Visits: 3,253
MSSQLTips has a good article about the performance impact of TDE...
http://www.mssqltips.com/sqlservertip/2641/sql-server-transparent-data-encryption-tde-performance-comparison


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1262222
Posted Tuesday, May 14, 2013 2:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:58 AM
Points: 79, Visits: 672
GilaMonster (3/4/2012)
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.


GilaMonster mentions that someone can steal a backup and read the encrypted data if they have sysadmin access. I assume access to the encrypted data is only possible if they have sysadmin access to the source data, correct? Wouldn't they need to backup and restore they keys from the source to the destination server to have access? Or can they steal/borrow a backup from a SQL Server they don't have access to and restore on a server where they are sysadmin and read the encrypted data? If yes, what is the process?

Thanks



Post #1452842
Posted Tuesday, May 14, 2013 3:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 1,706, Visits: 4,848
sjs-36273 (5/14/2013)
GilaMonster (3/4/2012)
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.


GilaMonster mentions that someone can steal a backup and read the encrypted data if they have sysadmin access. I assume access to the encrypted data is only possible if they have sysadmin access to the source data, correct? Wouldn't they need to backup and restore they keys from the source to the destination server to have access? Or can they steal/borrow a backup from a SQL Server they don't have access to and restore on a server where they are sysadmin and read the encrypted data? If yes, what is the process?

Thanks

Membership in SQL Server's SYSADMIN role doesn't confer any special admin privillage to the host server's file system. If ACL is enabled on the host server, someone would need local admin membership (or permissions evelvated to them by local admin) to gain access to the actual mdf data file.
Post #1452853
Posted Tuesday, May 14, 2013 3:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
sjs-36273 (5/14/2013)
GilaMonster (3/4/2012)
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.


GilaMonster mentions that someone can steal a backup and read the encrypted data if they have sysadmin access. I assume access to the encrypted data is only possible if they have sysadmin access to the source data, correct? Wouldn't they need to backup and restore they keys from the source to the destination server to have access? Or can they steal/borrow a backup from a SQL Server they don't have access to and restore on a server where they are sysadmin and read the encrypted data? If yes, what is the process?

Thanks

The key point you may have missed is bolded above. It is known that you must have the keys and the backup to make a database encrypted with TDE viewable. If you can restore to the original instance then the keys are present. However if someone gets the backup of a database protected with TDE and does not have the ability to restore and view the database in the original instance, then they must also have the encryption keys to restore along with the backup to a new instance.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1452861
Posted Tuesday, May 14, 2013 3:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:58 AM
Points: 79, Visits: 672
opc.three (5/14/2013)
sjs-36273 (5/14/2013)
GilaMonster (3/4/2012)
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.


GilaMonster mentions that someone can steal a backup and read the encrypted data if they have sysadmin access. I assume access to the encrypted data is only possible if they have sysadmin access to the source data, correct? Wouldn't they need to backup and restore they keys from the source to the destination server to have access? Or can they steal/borrow a backup from a SQL Server they don't have access to and restore on a server where they are sysadmin and read the encrypted data? If yes, what is the process?

Thanks

The key point you may have missed is bolded above. It is known that you must have the keys and the backup to make a database encrypted with TDE viewable. If you can restore to the original instance then the keys are present. However if someone gets the backup of a database protected with TDE and does not have the ability to restore and view the database in the original instance, then they must also have the encryption keys to restore along with the backup to a new instance.



opc.three - In the paragraph I have a question about, GilaMonster was referring to Column Level Encryption not TDE. Does what you said change for column level encryption?



Post #1452868
Posted Tuesday, May 14, 2013 9:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
Pretty much the same deal. If you steal a backup with encrypted columns and restore it to a new instance you still need to, at minimum, be able to decrypt the database master key using its password. So, if you have the backup and are sysadmin somewhere else you still do not have an automatic path to see the encrypted column data.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1452910
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse