May 14, 2013 at 2:41 pm
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
May 14, 2013 at 3:00 pm
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 14, 2013 at 3:23 pm
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
May 14, 2013 at 3:50 pm
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?
May 14, 2013 at 9:57 pm
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
May 14, 2013 at 11:14 pm
You would more likely use a certificate for your column encryption.
What Gail is saying is that someone who is sysadmin on another instance could take the backup and the cert and restore it elsewhere and as they are sysadmin will automatically have database permission to open the encryption key.
They do still of course need to know the password that the cert backup was encrypted with, and be able to access the disk location where it's stored.
There are many factors, but essentially the weaker your security around the stored backups the more possible it becomes.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply