Sql-server – Is network traffic encrypted when writing remote backups using SQL Server TDE

encryptionNetworksql servertransparent-data-encryption

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?

Best Answer

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.

Related Question