CODEWITHSUNDEEP
sqlsql-server-2008encryptiontde
Andy Macnaughton-Jones
Andy Macnaughton-Jones

Reputation: 1055

When does TDE decryption take place

With a TDE encrypted database I understand that data is encrypted on a per page basis as it's written to the database. When I run a query that joins a few tables together and applies some filtering to those tables, when does the decryption occur ?

Does it have to decrypt the tables first then perform the joins and filtering or is it able to do that joining and filtering with encrypted data and then just decrypt the results ?

Upvotes: 1

Views: 598

Answers (2)

Ross Bush
Ross Bush

Reputation: 15175

It appears that the decryption is performed as the rows are read from disk. Notice that data in rest(saved to disk) is only considered protected by TDE. Once in memory, the data is no longer protected by tde.

TDE and Decryption

TDE is designed to protect data at rest by encrypting the physical data files rather than the data itself. This level of protection prevents the data and backup files from being opened in a text editor to expose the file’s contents.

TDE encryption occurs prior to writing data to disk, and the data is decrypted when it is queried and recalled into memory. This encryption and decryption occurs without any additional coding or data type modifications; thus it’s transparency. Once the data is recalled from disk, into memory, it is no longer considered to be at rest. It has become data in transit, which is beyond the scope of this feature. As such, alongside TDE, you should consider applying additional supporting layers of protection to your sensitive data, to ensure complete protection from unauthorized disclosure. For example, you may wish to implement, in addition to TDE, encrypted database connections, cell-level encryption, or one-way encryption. For additional data in transit protection that is required, externally from the database, you may need to consult with, or defer to, your Network Administration team.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294277

From MSDN:

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory.

You need to understand how the buffer pool works. The buffer pool is a cache of the data on disk. Queries always read data from the BP and write changes to the BP (simplified explanation). Encryption occurs when data is transferred from BP to disk and decryption occurs when data is transferred from disk to BP. Read Understanding how SQL Server executes a query for details how all this works.

Upvotes: 1

Related Questions