SQL Clone
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
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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.
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28678 Visits: 11495
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 is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Ryan Kilkenny
Ryan Kilkenny
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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.
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28678 Visits: 11495
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 is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
EdVassie
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13795 Visits: 3894
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,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
sjs-36273
sjs-36273
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 741
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
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28678 Visits: 11495
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 is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38954 Visits: 14411
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
sjs-36273
sjs-36273
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 741
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?
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38954 Visits: 14411
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
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