Transparent Data Encryption Considerations

I was spending some time recently looking over Transparent Data Encryption (TDE). TDE is a fairly straightforward concept: the data on disk is encrypted (data files, log files, backup files), but to the end user no special decryption method is necessary to query the data. If you have permissions to read the data (say, as a member of the db_datareader database role), you can do so easily through whatever means you want. If the actual physical data files fall into the “wrong” hands, they cannot be unencrypted without the associated and necessary certificates.

As great as this may sound, there are two things that immediately come to my mind that makes me hesitant to implement TDE on a shared instance of SQL Server.

TDE encrypts your tempdb

You have to dig deep into your books online in order to find this nugget of information:

“The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server. For more information about the tempdb system database, see tempdb Database.”

Yeah, that’s right, if you enable TDE for just one, teeny, tiny database, then your one and only tempdb database will be encrypted as a result. And yes, this could be a performance issue, especially for a shared instance that has some moderate level of tempdb activity. By the way, wouldn’t it be nice if SQL Server came with a tempdb for each database? Up until now I may have said “no”, but with TDE my opinion changes.

TDE does not encrypt FILESTREAM data

Dig a little deeper in the BOL entry and you will also find this:

“FILESTREAM data is not encrypted even when TDE is enabled.”

Just to be clear, you could have a situation where you have a database that uses FILESTREAM to store data, you decide to enable TDE in order to protect your sensitive data inside that database, you degrade tempdb performance after enabling TDE, and your data is still not protected. In other words, you aren’t protecting what you think you are protecting but in order to make up for it you are killing performance for everyone else.

Bottom line: if you plan on using TDE, make sure you understand what you are doing. And practice your restores often, it takes a while to get used to carrying those certificates with you everywhere.

http://www.johnsansom.com John Sansom

That’s quite a show stopper right there with regard to the tempdb being encrypted when TDE is enabled for a single database and was not something I was aware of. It could even rule out the possibility of its use completely for some environments.

Thanks for the heads up!
http://sqlblog.com/blogs/michael_coles/default.aspx Sergeant SQL

Encrypting TempDB makes perfect sense. If you didn’t encrypt TempDB you can leak information as unencrypted data is being stored in it. If you’re worried about performance keep your TDE-encrypted databases on separate instances from your non-encrypted databases. Logs are also encrypted, btw.

The reason Filestream is not encrypted is because TDE operates on SQL Server’s IO buffers. Filestream bypasses the IO buffers to give you performance enhancement. I suspect you could use Windows file encryption (EFS, BitLocker) facilities on Filestream data, although I have to admit I haven’t actually tried it yet.

For me the downside of TDE is that it only protects against file theft. Anyone who can get their hands on a login with appropriate rights can see all your data as if it were stored unencrypted. Cell-level encryption gives you fine-grained control (the trade-off being performance, of course).

Thomas LaRock

good points Michael, thanks.

i don’t think many people are aware about the tempdb encryption. it’s not that it doesn’t make sense to need your tempdb to be encrypted; it’s that it isn’t well known, in my opinion. and it could also be the case that it might make more sense to have a tempdb for each database, rather than only one shared tempdb per instance. of course that will never happen, but we can all dream, right?

http://sqlblog.com/blogs/michael_coles/default.aspx Sergeant SQL

Hey Thomas,

You’re right, the TempDB encryption is buried in the fine print when it should be circled in red. I try to draw attention to it every time I give an encryption presentation, along with the other “little things” that seem to fly under the radar with TDE–the things that can have a direct impact on your architecture decisions and performance.

Your post will definitely get people thinking about some of the “side effects” of TDE. And that’s exactly what they need to help them make better decisions about when and where to use this feature! Kudos!