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 ««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: Wednesday, January 7, 2015 3:12 PM
Points: 8, Visits: 88
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 3,734, Visits: 8,584
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.



"The universe may be complicated, but life is only as complicated as you choose to make it."
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: Wednesday, January 7, 2015 3:12 PM
Points: 8, Visits: 88
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 3,734, Visits: 8,584
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.



"The universe may be complicated, but life is only as complicated as you choose to make it."
Post #1261748
Posted Tuesday, March 6, 2012 7:59 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 23, 2016 5:06 AM
Points: 3,034, Visits: 3,709
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 2016, 2014, 2012, 2008 R2, 2008 and 2005. 11 May 2016: now over 37,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
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: Friday, August 12, 2016 12:35 PM
Points: 79, Visits: 723
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 3,734, Visits: 8,584
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.



"The universe may be complicated, but life is only as complicated as you choose to make it."
Post #1452853
Posted Tuesday, May 14, 2013 3:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:21 AM
Points: 7,927, Visits: 14,309
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: Friday, August 12, 2016 12:35 PM
Points: 79, Visits: 723
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: 2 days ago @ 11:21 AM
Points: 7,927, Visits: 14,309
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