SQL SERVER – Transparent Data Encryption and Frequently Asked Questions

In the recent past, I have been traveling to Delhi to meet customers and also attend a few conferences which my company has been sponsoring. These travel experiences give me an opportunity to meet folks who read my blog regularly as a face-to-face interaction. Most of these interactions mean I get to spend time with really smart people who quiz me for which I get back to my blog for answers. Last week, when I was at a conference, one DBA walked up to me and said – “Hey Pinal, I am a DBA from a reputed banking company. We are in the process of deploying TDE for one of our databases which is in SQL Server 2008 R2. I had a few doubts, can I ask you?” Well, as simple as this interaction went, the whole conversation spanned for close to 30 mins and I am doing a summary of the conversation in this blog for your reference.

When does TDE encrypt the DB?

Once Transparent Data Encryption is enabled by issuing the “Alter Database” command, SQL Server performs basic checks such as Edition Check, Read-only Filegroups, and presence of DEK etc. Once the checks are complete, the command returns immediately with success. This does not mean that the database encryption is complete. Since Encryption is done in the I/O path, all the data pages that are already written to the disk prior to enabling the Encryption have to be read into the memory and then written back to the disk after encrypting the page. This process is also referred to as “Encryption scan”. This task is carried out by Background processes (system SPIDS). The encryption scan, which runs asynchronously to the DDL, takes a shared lock on the database. All normal operations that do not conflict with these locks can proceed without being blocked.

You mentioned READONLY DB, tell me more?

Transparent data Encryption does not work on a database that contains any filegroups that are marked Read-Only or any files that are marked as Read-only. Alter Database fails with an error message (33118) clearly indicating the reason for the failure. Users can enable read-only property on the filegroups once the encryption scan is completed. However, no DDL related to TDE (DEK change) can be run on the database until the read-only property is removed.

What happens to TLog files?

Encryption works differently on a Transaction Log and is complicated. Since Transaction Log is designed to be Write-Once fail safe, TDE does not attempt to Encrypt the contents of the Log file that were written to the disk already. Similarly, the log header cannot be re-written because of this write-once principle so there is no guarantee that log records written to the log even after TDE is enabled will be encrypted. The smallest unit of encryption for log files is a virtual log file (VLF). So either an entire virtual log file (VLF) is encrypted or it’s not. Also the entire VLF is encrypted with the same key. When encryption is turned on for a database, the next time the log manager moves to a new VLF, this new VLF will be encrypted. So there is no deterministic order between when data pages are encrypted by the scan vs. when the log is encrypted.

I thought these were some of the interesting conversations I have had in the recent past that are worth a mention to share. If you have used TDE in your environment, do let me know. I would love to know your experiences in working with it. Is there any catch that you want me to explain next time? Write it over as comments and would love to explore more.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Server Encryption, SQL Server Security
Previous Post
SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?
Next Post
SQL SERVER – FIX – Error: Alter failed for Server ‘Server Name’ in Check Integrity task

Related Posts

8 Comments. Leave new

  • dhavalcharadva
    September 7, 2015 7:24 pm

    Hi Pinal !

    I have done some research on data protection and have many option. And one of them is TDE. I have also explored CLE(Cell Level Encryption).

    I want advise on what is more helpful and also make my application security compliance. We are searching for proper data protection solution. We are managing user’s personal data and we are already PCI compliant.

    Any help will be great.

    • Will need to know more about how the application wants to implement security and then the recommendations can be given. I am would be interested in understanding the nuances and using TDE and/or CLE are absolutely fine provided they fit your requirement.

  • Thanks for reply.

    Actually our IT team is telling me that we want to make our application new European security standard compliance as we are data collector , and we are storing user’s personal data.

    IT head is telling me that we must need encryption of data and is by application. But my argument was we have another choice to secure data instead of making it just unreadable. Because as I read some of links for this new standard

    So do you aware about this new European security standard or any that says how data collector should protect their data in case they are storing personal data.

    And I just discovered that we are using SQL Server BI Edition and that edition doesn’t support TDE :(. So now is any alternatives ?

    I have this 3 links regarding European standard.

  • when we encrypt the database with tde algorithm, what changes do we have to do on the connection string ?

    • That’s the beauty of TDE. There is no change needed in application as the encryption of data happens only for “data at rest” – in database files.

  • David Lathrop
    March 7, 2017 12:59 pm

    One correction to your section on the Transaction Log. At the end of the encryption scan, the T-Log is forced to a new VLF by “zeroing out” the rest of the current VLF. The good news is that you know that any new logged content after that will be encrypted. On the flip side, this “zeroing out” will have an I/O impact and block normal writing to the log, particularly with large VLFs. Also, until all unencrypted VLFs are overwritten, you will still have some unencrypted content in your T-Log.

    If anyone wonders, there are actually two DEKs stored in the header pages of database, log and backup files. This allows backups and restores to work mid-encryption scan, and applying a log backup that has a DEK change in the middle of it is transparent.

  • Hi Pinal,

    What are the possibilities that the encryption may fail in middle and after reissuing the alter db to resume it completes successfully.

    Thank you in advance.


Leave a ReplyCancel reply

Exit mobile version