1

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 ?

Andy Macnaughton-Jones
  • 985
  • 2
  • 8
  • 16
  • What part of the documentation do you not understand? It seems pretty well explained: https://msdn.microsoft.com/en-us/library/bb934049.aspx. – Gordon Linoff Sep 26 '16 at 14:43
  • 1
    Before they're decrypted, pages are unreadable. There are no rows or columns, so there's no way for the engine to do any computation on them. If you had *column* based encryption, it would be a different matter. To put it differently: the engine doesn't know about encryption at all, as far as it's concerned it's just another layer over the file system. – Jeroen Mostert Sep 26 '16 at 14:45

2 Answers2

1

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

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.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • The question really then is how does it know which rows to read from disk to be able to run a query unless it reads the whole lot, decrypts it all (taking into account the buffer pool) and is then able to work on it "in memory". The example flow from "Understanding how SQL Server executes a query" indicates that SQL Server knows about what pages are what but as it's encrypted it can't know I want to "Select * From myTable where name = 'bob'". What have I missed ? – Andy Macnaughton-Jones Sep 27 '16 at 16:22