January 16, 2018 at 12:01 am
Comments posted to this topic are about the item Disk Encryption and The Impact to DBAs
January 16, 2018 at 7:29 am
Where I work, we've started implementing SQL Server TDE, which I believe has many advantages over FDE (Full Disk Encryption) solutions.
TDE:
- Is free with the SQL Server license. Starting with 2016 SP1, TDE is now available on Standard and Express editions.
- Encrypts only the databases that need encryption.
- Typically, the performance impact is not significant or even noticeable, at least not from my experience across a wide variety of large databases.
- The encryption and re-encryption process is asynchronous, so initial implementation and key rotation requires no downtime.
- The files can be archived or moved across environments without in it's original encrypted form (assuming your migrate the certificate as well).
However, if I ever had a need to encrypt the entire server file system, perhaps to protect things like connection strings, logs, and application code, then I might consider Window's native BitLocker for full disk encryption. Has anyone here had experience with running SQL Server under BitLocker? If so, then what is the performance impact for this specific implementation?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 16, 2018 at 10:33 am
Eric M Russell - Tuesday, January 16, 2018 7:29 AMWhere I work, we've started implementing SQL Server TDE, which I believe has many advantages over FDE (Full Disk Encryption) solutions.TDE:
- Is free with the SQL Server license. Starting with 2016 SP1, TDE is now available on Standard and Express editions.
- Encrypts only the databases that need encryption.
- Typically, the performance impact is not significant or even noticeable, at least not from my experience across a wide variety of large databases.
- The encryption and re-encryption process is asynchronous, so initial implementation and key rotation requires no downtime.
- The files can be archived or moved across environments without in it's original encrypted form (assuming your migrate the certificate as well).However, if I ever had a need to encrypt the entire server file system, perhaps to protect things like connection strings, logs, and application code, then I might consider Window's native BitLocker for full disk encryption. Has anyone here had experience with running SQL Server under BitLocker? If so, then what is the performance impact for this specific implementation?
The drawback of TDE is it will encrypt tempdb as well. I do not know how this will impact non-TDE databases though but would like to invite everyone to share their experience.
As for BitLocker, our sys admin team enabled BitLocker on production servers (SQL2014) without my knowledge and I didn't even notice it, although they might have rebooted the box during the process - I was new and they didn't bother to tell me much at the time. So performance-wise, I'd say the impact is negligible.
However, I did run into an interesting issue. Sys admin added a new BitLocked drive to one of the servers. When I tried to move a critical database from a unencrypted drive to this new drive using detach/attach, the resultant database became read-only. Bummer. In a rush I used the detach/attach to move it back to the regular drive and it failed with some nasty error message. I don't remember what it was. Luckily I was able to restore from a backup. Next, I tried to 'RESTORE' onto the encrypted drive, again the resultant database was in read-only mode. I googled a full day the next day and found some article pointing to permission issue. It looked like the drive was encrypted by sys admin using a privileged account and the account used by SQL Server didn't have sufficient permission to access the database, even though it was able to create the database (via attach and restore command) on the encrypted drive. I'm skeptical about this theory but have not had the opportunity to try again or dig further.
January 16, 2018 at 11:15 am
Eric M Russell - Tuesday, January 16, 2018 7:29 AMWhere I work, we've started implementing SQL Server TDE, which I believe has many advantages over FDE (Full Disk Encryption) solutions.TDE:
- Is free with the SQL Server license. Starting with 2016 SP1, TDE is now available on Standard and Express editions.
- Encrypts only the databases that need encryption.(snip)
Hi --
There are some shops that cannot move up to 2016 or newer and may be handcuffed to a lower version. BitLocker was rejected by the Infrastructure manager in favor of SafeNet ProtectFile There was an issue that caused him to reject BitLocker, but he is out of town so I can't ask him. The article recounts some of our challenges with SafeNet.
January 16, 2018 at 11:18 am
At the very end of the article there is a statement " If your SAN offers encryption at rest within the SAN, that will likely be much faster than a client-side EAR tool, and perhaps without any noticeable degradation in performance.". Need some clarification on this sentence. Does it mean that encryption offered by SAN may turn out to be better than Always Encrypted ? I am assuming here that SAN in this context means any I/O subystem and EAR refers to Always Encrypted only and not TDE.
On a different note, we are vendors of a system which stores documents and other non-relational data outside of the SQL Server on the file system. In such cases, it seems logical to recommend encryption at the file system level since it has the capability to cover both the SQL Sever databases as well as the documents. Some of our customers have already done this without our knowledge and their in-house administrators claim that there is no noticeable degradation in SQL Server performance. Do others have a similar experience ? Which also begs another question, if encryption/decryption has to be done at some layer, why would the TDE perform better than that offered by an I/O vendor ? Does the TDE use technology which is superior and/or consume lesser computer resources ? The article does not seem to clarify this.
January 16, 2018 at 12:10 pm
Based on replies above, I'd like to add a couple of things. It's true that enabling TDE on one database will result in SQL Server also encrypting TEMPDB, but that's by design, because temporary tables can potentially contain data from a TDE protected database. But this hasn't presented an issue from my experience. Whatever the technical details, TDE truly is transparent and you'd hardly know it's there (unless you attempt to restore or move the database and forgot to restore the certificate to the destination server first).
Also, some of our databases are hosted in Azure SQL (PaaS), where only database level TDE or Always Encrypted is an option and 3rd party full disk or file based encryption isn't an option. When you start moving into the cloud, you lose some of the control over the operating and file system and have to work inside the database box.
However, you can still implement full disk or file encryption on an Azure VM (Iaas) running SQL Server Enterprise/Standard.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 16, 2018 at 12:40 pm
lbnayak - Tuesday, January 16, 2018 11:18 AMAt the very end of the article there is a statement " If your SAN offers encryption at rest within the SAN, that will likely be much faster than a client-side EAR tool, and perhaps without any noticeable degradation in performance.". Need some clarification on this sentence. Does it mean that encryption offered by SAN may turn out to be better than Always Encrypted ? I am assuming here that SAN in this context means any I/O subystem and EAR refers to Always Encrypted only and not TDE.On a different note, we are vendors of a system which stores documents and other non-relational data outside of the SQL Server on the file system. In such cases, it seems logical to recommend encryption at the file system level since it has the capability to cover both the SQL Sever databases as well as the documents. Some of our customers have already done this without our knowledge and their in-house administrators claim that there is no noticeable degradation in SQL Server performance. Do others have a similar experience ? Which also begs another question, if encryption/decryption has to be done at some layer, why would the TDE perform better than that offered by an I/O vendor ? Does the TDE use technology which is superior and/or consume lesser computer resources ? The article does not seem to clarify this.
Don't confuse Always Encrypted with disk encryption / TDE. Always Encrypted is intended to keep only certain data (say a PII column in a table) encrypted from "end-to-end." So that in the database, the column is encrypted, when being sent across the wire to the client, it's still encrypted, once it gets to the client it gets decrypted.
Whereas TDE or full-disk encryption is intended to protect an entire database / disk while *AT REST.* So if someone has access to read the data in a protected database, they can *unless* that data is encrypted in some way.
You can layer these protections, if needed, as well. So as an example, if you have a listing of your secret agents, you use Always Encrypted to ensure that the information is secured both in transit and in the database, with TDE to ensure that even if someone gets a backup of the database (or even copies of the actual MDF / LDFs) they still won't be able to retrieve the information.
January 16, 2018 at 3:26 pm
jasona.work - Tuesday, January 16, 2018 12:40 PMlbnayak - Tuesday, January 16, 2018 11:18 AMAt the very end of the article there is a statement " If your SAN offers encryption at rest within the SAN, that will likely be much faster than a client-side EAR tool, and perhaps without any noticeable degradation in performance.". Need some clarification on this sentence. Does it mean that encryption offered by SAN may turn out to be better than Always Encrypted ? I am assuming here that SAN in this context means any I/O subystem and EAR refers to Always Encrypted only and not TDE.On a different note, we are vendors of a system which stores documents and other non-relational data outside of the SQL Server on the file system. In such cases, it seems logical to recommend encryption at the file system level since it has the capability to cover both the SQL Sever databases as well as the documents. Some of our customers have already done this without our knowledge and their in-house administrators claim that there is no noticeable degradation in SQL Server performance. Do others have a similar experience ? Which also begs another question, if encryption/decryption has to be done at some layer, why would the TDE perform better than that offered by an I/O vendor ? Does the TDE use technology which is superior and/or consume lesser computer resources ? The article does not seem to clarify this.
Don't confuse Always Encrypted with disk encryption / TDE. Always Encrypted is intended to keep only certain data (say a PII column in a table) encrypted from "end-to-end." So that in the database, the column is encrypted, when being sent across the wire to the client, it's still encrypted, once it gets to the client it gets decrypted.
Whereas TDE or full-disk encryption is intended to protect an entire database / disk while *AT REST.* So if someone has access to read the data in a protected database, they can *unless* that data is encrypted in some way.
You can layer these protections, if needed, as well. So as an example, if you have a listing of your secret agents, you use Always Encrypted to ensure that the information is secured both in transit and in the database, with TDE to ensure that even if someone gets a backup of the database (or even copies of the actual MDF / LDFs) they still won't be able to retrieve the information.
Thank you for your response. I do understand the rationale for Always Encrypted and the differences with TDE. To me, it seems like the file system offers ALMOST the same protection as TDE and with same performance characteristics. In our case, where there are documents residing on the file system, why would I chose TDE over file system encryption ? The article does not seem to address this question. A response from Eric Russell elsewhere suggests that, in the cloud you may not have control over the operating system and TDE & Always Encrypted may be the only option available .
Regarding the Always Encrypted option, I did not frame the question correctly. My apologies. I do understand that the goal of Always Encrypted is end-2-end encryption. Even the DBA cannot see the data unless there is collusion with the administrators of the keys/certificates. I wasn't sure how the SAN encryption compared to this ability of Always Encrypted. Does it offer similar protection ? That line in the article alluded to that possibility by mentioning it in the same line. And if a SAN offers this possibility, shouldn't a similar protection be available in other IO subsystems ?
January 16, 2018 at 4:31 pm
lbnayak - Tuesday, January 16, 2018 3:26 PMThank you for your response. I do understand the rationale for Always Encrypted and the differences with TDE. To me, it seems like the file system offers ALMOST the same protection as TDE and with same performance characteristics. In our case, where there are documents residing on the file system, why would I chose TDE over file system encryption ? The article does not seem to address this question. A response from Eric Russell elsewhere suggests that, in the cloud you may not have control over the operating system and TDE & Always Encrypted may be the only option available .
Regarding the Always Encrypted option, I did not frame the question correctly. My apologies. I do understand that the goal of Always Encrypted is end-2-end encryption. Even the DBA cannot see the data unless there is collusion with the administrators of the keys/certificates. I wasn't sure how the SAN encryption compared to this ability of Always Encrypted. Does it offer similar protection ? That line in the article alluded to that possibility by mentioning it in the same line. And if a SAN offers this possibility, shouldn't a similar protection be available in other IO subsystems ?
Understood.
Working from the bottom up, I would suspect SAN encryption works out to be more akin to full-disk-encryption (FDE) rather than Always Encrypted. As for why you might want TDE vs FDE, I would think that depending on your setup and FDE provider, your backups may *NOT* be encrypted with FDE, whereas a TDE backup is encrypted.
January 16, 2018 at 5:56 pm
"Starting with 2016 SP1, TDE is now available on Standard and Express editions."
Just small correction- TDE is still available on Enterprise Ed only- it's Always Encrypted that is available on ALL Ed 2016 from SP1:
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016
January 17, 2018 at 7:35 am
Yuri55 - Tuesday, January 16, 2018 5:56 PM"Starting with 2016 SP1, TDE is now available on Standard and Express editions."Just small correction- TDE is still available on Enterprise Ed only- it's Always Encrypted that is available on ALL Ed 2016 from SP1:
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016
Yes, taking a closer look that appears to be the case, 2016 SP1 Standard still doesn't support TDE. It seems to me that encryption should not the considered an exclusively "Enterprise" feature. Essentially, any database server that will host production data (which certainly includes Standard and even Express) should be enabled with any security related feature.
2016 Express edition, despite being limited to 1 or 2 GB of memory, even supports In-Memory OLTP now. Other than prototyping, I'm not sure why anyone would want to use In-Memory OLTP in conjunction with Express edition. It seems TDE would be far more essential since Express edition can typically be found on low end hardware in a closet, or on someone's laptop, where it's potentially more exposed to theft.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 17, 2018 at 8:06 am
Another difference between FDE and TDE is who owns the process and controls the keys: the local server admin or the database administrator. Depending on the IT organization, this could be two different roles, possibly two different departments and executive management chains.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 17, 2018 at 10:05 am
Eric M Russell - Wednesday, January 17, 2018 7:35 AM(snip)
Yes, taking a closer look that appears to be the case, 2016 SP1 Standard still doesn't support TDE. It seems to me that encryption should not the considered an exclusively "Enterprise" feature. Essentially, any database server that will host production data (which certainly includes Standard and even Express) should be enabled with any security related feature.2016 Express edition, despite being limited to 1 or 2 GB of memory, even supports In-Memory OLTP now. Other than prototyping, I'm not sure why anyone would want to use In-Memory OLTP in conjunction with Express edition. It seems TDE would be far more essential since Express edition can typically be found on low end hardware in a closet, or on someone's laptop, where it's potentially more exposed to theft.
I could not agree more. Microsoft is inadvertently driving people to FDE and third party column encryption solutions by trying to force people to upsize the edition of SQL Server that they use.
Thanks
John.
January 17, 2018 at 11:56 am
Re: TDE/FDE, and AE. They use different paradigms. They aren't intended to be related.
TDE/FDE are protecting the server, and loss of the physical assets. In this case, you have to trust the admins of the servers, and essentially trust the server. The wire, memory, and clients aren't encrypted.
AE does not trust the server at all. It trusts the clients, and all encryption occurs client side. Note that you can't encrypt everything because the optimizer can't read the encryption in 2016/2017 (maybe in the future). The idea here is that only encrypted data exists outside the client (including the wire).
January 18, 2018 at 7:52 am
It's different paradigms, but we have to make choices about which paradigm is most appropriate for the given environment and application. Regarding Always Encrypted, maybe one day someone will invent an implantable chip so that data is un-encrypted only inside the mind of the end user. Or perhaps something like smart contact lenses with augmented reality (ie: you look at encrypted data and what you see is un-encrypted), and the lenses only work for the individual they were paired with. Someone must be listening, because whenever I say stuff like this, it happens a couple of years later.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply