Yes.
#ShortestPostEver
Oh, ok… I’ll explain some more.
Transparent Data Encryption encrypts data at rest. That’s the stuff that’s on disk – the encryption happens when the data is written to disk, and the decryption happens as the data is loaded into RAM from the disk. The engine handles this so that it’s invisible to the user, applications, and so on. Without it, you can open an MDF/NDF file in a hex editor and read the contents. With it, you can’t.
Here’s an example with a database that’s not encrypted:
And here’s an example that is:
I searched for some of the text that I could see – successfully in the first, unsuccessfully in the second.
I also used SQL Server to show me the contents of a page using DBCC PAGE, and could do this successfully (once I’d closed the files in the hex editor and brought the databases back online).
…which also worked in both databases.
I had hoped this would work okay, because I figured that DBCC PAGE would have to pull the data into RAM again (remember this system was offline – the pages weren’t in RAM before), and that it would decrypt this as it did it. But I wondered if DBCC PAGE might be slightly lower-level, and bypass it somehow. I argued with myself that if TDE was indeed Transparent, it shouldn’t care… what if my application relied on using DBCC PAGE, it’s a known feature, even if it is officially undocumented (which is where my doubts set in).
But as you see, it worked okay.
But what if I dropped the table first? Would SQL then go “Hang on – this page isn’t one that I have control over any more…” and refuse to decrypt it?
No – it works just the same.
Even if you drop an object, you can still access the pages that it used until they get overwritten. You won’t be able to read them with a hex editor, but DBCC PAGE will still read them in decrypted form, letting you pull that data out.
And yes, you can even use DBCC WRITEPAGE to overwrite the bytes in their unencrypted form, so that you can use (dangerous) method of fixing corruption, even in an encrypted database. I’ve just redone my fix for Steve Stedman’s 10th Corruption Challenge, and it worked just fine on an encrypted version of the database.
It’s still T-SQL Tuesday on the topic of encryption, so I’m throwing this one into the mix for that as well.
This Post Has 4 Comments
TDE is used to encrypt the physical files , enabling encryption will secure the physical files so they cannot be used without specific key ,
So if you attach them in a SQL instance which have the key installed they will be normally accessed , so it’s obvious that you can access the pages contents using DBCC Page , however you cannot attach them in a different instance
by the way I might be wrong because I am new DBA , and currently learning
Yup, it’s fairly obvious – but it’s quite reasonable to expect that it might only decrypt pages that are part of known allocations or something like that. It’s good that you found it obvious though.
Not surprised DBCC page works like this with TDE.
TDE is supposed to present data transparently in every way to any authorized connection or process.
I would be more surprised if this did not work with TDE.
Right, but it’s not too much of a stretch to wonder if the TDE process might only apply to data that it knows about.