February 27, 2012 at 6:40 pm
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!
February 28, 2012 at 8:59 am
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
February 28, 2012 at 9:04 am
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
February 28, 2012 at 6:03 pm
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.
March 4, 2012 at 11:27 am
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" 😉
March 4, 2012 at 11:38 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2012 at 8:00 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 5, 2012 at 9:09 am
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.
March 5, 2012 at 9:15 am
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
March 5, 2012 at 9:59 am
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" 😉
March 5, 2012 at 10:37 am
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.
March 5, 2012 at 11:11 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 5, 2012 at 11:29 am
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.
March 5, 2012 at 11:49 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 6, 2012 at 8:00 am
MSSQLTips has a good article about the performance impact of TDE...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply