A One-Slide Summary of the Differences Between TDE and Always Encrypted


The folks on Twitter liked this, so sharing it here:

It’s a one-slide summary from a SQL Critical Care client’s deck, so obviously it’s abridged, but I think it does a pretty good job of summing things up.

Some highlights of the conversation:

That’s it. That’s the post. If you want more words and pictures, follow @BrentO on Twitter.

Previous Post
Remember “Nothing Stops a Hekaton Transaction?” Yeah, About That.
Next Post
[Video] The Top 10 Developer Mistakes That Won’t Scale on Microsoft SQL Server

14 Comments. Leave new

  • John Boncek
    July 31, 2020 9:27 am

    Discussions about protecting data from DBAs, etc., always leave me wondering, “Don’t you have to trust somebody at some point?” Perhaps you could do (or already have) a discussion about a credible scenario where even DBAs aren’t supposed to have access to data, and what would be required to truly achieve that.

    • Roman Brauchle
      August 3, 2020 12:37 am

      As a DBA you should feel lucky about about apps that encrypted data. If you can’t see it plain you don’t need to sign contracts about not using the data. Just think of a database with salaries. Ever signed a contract which said you have to keep it as a secret forerver even if you leave the company?

      • I completely agree! As a DBA, I don’t wanna see anything I shouldn’t be seeing. I’d rather not know.

      • It turns out, using salary as an example, that’s a really hard problem. Sure, I can encrypt salary client side so as a DBA you can’t see it. But, what about paychecks/paystubs? Sure, it’s not exactly salary (your withholding might be different, overtime, etc..) but it gets me close. Okay, encrypt that too. Oh, but then depending on how your payroll and/or bank operates, you might have a ledger entry for that paycheck or a bank transaction record for it. Pretty soon, you’re encrypting a lot of stuff for something as simple as salary.

  • “Discussions about protecting data from DBAs,”… but why does everyone always focus solely on the DBA’s? How about keeping the scope to how it should be.. protect data from any account whom has at least direct read access to sensitive data in the database and has no valid reason to view it – for a hypothetical example I wouldn’t need to see credit card information as my role wouldn’t encompass card payments. I couldn’t write that within the original tweet due to character limitations with what I’d already wrote. Trust went royally out of the window with Snowden; DBAs as well as other type of admins around the world have been paying the price ever since :-). However like Roman, I am more than happy if apps are encrypting data that I and others are not meant to see.

  • TDE is becoming a more commonly-requested feature by third parties, especially financial institutions such as lenders and banks, so it pays to understand it.

    TDE is easy to implement and word as advertised.

    • It’s because they foolishly see the word encryption and think “eh if they do that everything is happy with the world”. It’s a tool in the chest, checks a box and that’s about it. If you are going to hack the data, let’s face it, it’s probably easier and quicker to get what you need via the front door and not try to obtain the underlying database files or backups.

  • Rick McCrea
    July 6, 2021 9:19 pm

    in the age of ransom attacks would it at least make since to have your TDE setup on servers in the DMZ to help with keeping your database base files from being taken over if the systems are compromised?

    • Personalized SQL Server security design is a little beyond what I can do in a blog post comment, but I would point out that if you get hit by ransomware, it doesn’t really matter whether your servers are in the DMZ or not.

  • Rick McCrea
    July 7, 2021 5:32 am

    I just wondered if having the mdf file encrypted would help it not getting encrypted for ransom… I don’t get how the attack happens to the file.

    • No worries – it’s just that explaining ransomware attacks isn’t really the focus of this blog, nor something I can do quickly in the comment section. It’s a great question though and you’re on the right track! Talk more about this with your security team.

  • Hi All,
    I need some basic understanding about page encryption by TDE functionality in SQL server. Looked everywhere on google but didn’t find the answer. Please help me.

    As TDE encrypts the page and not the actual data , so what’s the meaning of encrypting the page and not the data inside it?
    How encrypted page saves the unencrypted data inside it?
    If TDE encrypts the page , then what actually it encrypts inside the page?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.