THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Can you still use DBCC PAGE to access data in dropped tables if TDE is turned on?

Yes.

#ShortestPostEver

@rob_farley 

 

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:

image

And here’s an example that is:

image

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).

image

...which also worked in both databases.

image

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.

TSQL2sDay150x150

Published Wednesday, August 12, 2015 11:59 AM by Rob Farley
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

SQL Admin said:

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

August 17, 2015 4:57 AM
 

Rob Farley said:

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.

August 17, 2015 5:19 AM
 

PhyDataDBA said:

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.

August 18, 2015 9:24 AM
 

Rob Farley said:

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.

August 18, 2015 5:55 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement