9

They do say there is no such thing as a “stupid question”, so here goes:

I understand that SQL Server Transparent Data Encryption (TDE) encrypts data at rest, so that your database files (.mdf) and your backup files (.bak) are encrypted should someone break into your storage and steal those files. I also understand that the data is decrypted when read from disk so that it is unencrypted in memory (in motion). Therefore data requested by a user running a remote query (select * from SensitiveData) will be unencrypted when travelling over the network and thus vulnerable to intercept.

So, assuming all the above is correct, here is my stupid question: If my SQL Server instance is on computer A and my TDE-database backups are written off to storage on remote computer B, is the backup operation data encrypted as it travels from computer A to be written to disk at computer B? I assume it must be (because I suppose the encryption operation occurs on computer A first), but I can’t find confirmation of this in any of the Microsoft documentation or on the blogs. And likewise, during a restore operation – were anyone to intercept the data being transferred from disk at computer B to restore the database at computer A – would they find that data in motion encrypted?

  • 2
    It is really a good question – Shanky May 12 '17 at 17:32
6

Yes, backups are encrypted while moving over the network because TDE data is encrypted on disk, and the backup operation never decrypts it.

Paul Randal's Backup Myths:

Myth 30-09) backups read data through the buffer pool

No. The backup subsystem opens its own channels to the database files to avoid the performance hit of having to read everything into SQL Server’s memory and back out to the backup device (and also effectively flushing the buffer pool in the process). If you ask the for page-checksum checking, it uses it’s own small portion of memory.

If the pages were loaded into the buffer pool (the "normal" memory space SQL uses to cache database table and index data), they would have to be decrypted. But backups don't do that, they just dump raw encrypted "extents" (contiguous 8-page chunks) to your backup destination.

I was able to get confirmation from Paul Randal that his above comment is still relevant for TDE:

It works exactly the same way. The buffer pool does encryption then adds a page checksum before writing a page to disk. Backups never read through the buffer pool. So yes, a backup of TDE database has the encryption still in it. Page checksums are validated, but by backup code, not buffer pool code.

In other words, if you've enabled CHECKSUMs on a database, those are added (during normal SQL write operations) after encryption occurs. This means that the backup process can read the raw (encrypted) extent, validate the checksum, and write the backup, all without decrypting the data.

This is almost certainly the reason that (prior to SQL 2016), enabling backup compression on database with TDE didn't do anything, since encrypted data is not very compressible:

This is because when backups of a TDE encrypted database are taken the database pages are not decrypted when backed up. They are backed up in the same encrypted state that they are normally in, then compressed. By it’s nature encrypted data is very unique so data compression doesn’t do much good against encrypted data.

For a restore operation, the same principle applies. The encrypted backup stays encrypted across the network, and is written to the restoring server's disk in their still-encrypted state. They are only decrypted when the database is loaded in memory after the restore is complete.

3

... is the backup operation data encrypted as it travels from computer A to be written to disk at computer B?

Yes, it is decrypted when it enters the buffer pool and encrypted when it leaves. In this situation since we are writing to disk, it is encrypted first and then written. Since the writes are going across the network, the data itself is encrypted but any other parts of the network traffic are not.

... during a restore operation ... would they find that data in motion encrypted?

Yes, since the same as above applies but in reverse order. Data was encrypted on disk, is being read and transferred in the encrypted state. Then it gets to the instance and loaded into the buffer pool where it is unencrypted as a step on the way.

  • 1
    I think this is correct, but I'm not sure that its correct for the reasons you say. I thought that a BACKUP will send raw database EXTENTS (not pages) to disk, therefore bypassing the decryption step when they're loaded into memory. I might be wrong, but am looking for documentation now. – BradC May 12 '17 at 14:38
  • 1
    Found it, see Paul Randal's myth 30-09: "The backup subsystem opens its own channels to the database files to avoid the performance hit of having to read everything into SQL Server’s memory and back out to the backup device". Doesn't mention TDE specifically, but if the backup process is its own channel, it would seem a waste to decrypt just to immediately re-encrypt. It could even validate CHECKSUMS and/or apply compression without decrypting, if those are enabled. – BradC May 12 '17 at 14:52
  • @BradC I wasn't saying that the backup itself would work this way, but how the encryption/decryption process would work with at rest data. If it's ambiguous I'll change it, however I'm not saying this is how a backup works just when and where the encryption/decryption happens. – Sean Gallardy May 12 '17 at 15:22
  • But if the backup process doesn't use the buffer pool, then your reasoning is incorrect, even if the conclusion (backup packets are encrypted) is right for a different reason. – BradC May 12 '17 at 15:27
  • @BradC No, the reasoning is that it is already written to disk so it is already encrypted... Not sure how you're getting that I am stating a backup is decrypted and then re-encrypted going through the BP. I thought it was fairly straight forward saying it was already encrypted so copying to another disk or copying it from another disk does not decrypt it... Not sure how you're confusing this. – Sean Gallardy May 12 '17 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.