Should You Use Always Encrypted?

Everybody wants an easy button to keep their SQL Server data safe at rest. Always Encrypted can keep your most sensitive data – think credit cards and social security numbers – safe by encrypting them in the database driver, running on the app server. That way, when it gets to the SQL Server, it’s already encrypted. As far as SQL Server is concerned, it really is always encrypted.

But that comes with a few big drawbacks. They’re really well-documented, but here’s the highlights:

Do you need to query that data from other apps? Do you have a data warehouse, reporting tools, PowerBI, Analysis Services cubes, etc? If so, those apps will also need to be equipped with the latest database drivers and your decryption certificates. For example, here’s how you access Always Encrypted data with PowerBI. Any app that expects to read the encrypted data is going to need work, and that’s especially problematic if you’re replicating the data to other SQL Servers.

Do users perform range scan queries? For example, say you’ve got a customers table, and your customer support team wants to run queries like this:

SELECT * FROM dbo.Customers WHERE Location LIKE ‘%Chicago%’

Nobody ever reads the documentation

Unfortunately, if the Location field is Always Encrypted, then it’s encrypted inside the engine, and SQL Server can’t do a string search inside the contents. SQL Server will return a full list of the Location field for every Customer back to the app server’s database driver, which then decrypts every location and does a string comparison. That’s not gonna be a good time.

Do you use full text indexes? If you’re doing the above design pattern and you’ve chosen to use full text indexes instead, no can do. That’s one of the many non-supported features.

So when does Always Encrypted actually make sense? Remember at the beginning of the post when I mentioned credit card numbers and social security numbers? You shouldn’t be doing any of these things on that kind of data. Always Encrypted is great for very limited amounts of data that you don’t search on or display to end users – data that you need to hold, but you really wish you didn’t ever have to show to anybody.

Or the way I say it to clients is, if you would use the encrypted data in the WHERE clause – and especially if you would get back more than one row – Always Encrypted probably isn’t a good fit.

Previous Post
ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures
Next Post
What Are Poison Waits?

33 Comments. Leave new

  • One other biggie I’m surprised you didn’t mention is the binary collation for these columns. That’s one I think will play a little havoc with people that are encrypting text.

    • Steve – yeah, that’s a great point! When I explain these limitations, that’s scared off every customer I’ve had so far, so I’ve never even gotten to the collation part, hahaha.

      • I’m just getting into researching Always Encrypted for potential use in our organization. Came to your blog as I always do for this kind of thing. Is the binary collation issue you & Steve are discussing related to the use of deterministic encryption or something I’ve yet to read/understand?

  • From an audit perspective, always encrypted offers a lot of benefits as far as implementation. I would argue that your probably encrypting PII and PHI, which shouldn’t be surfaced in a reporting platform. Location by itself in the example is not an personal identifier, and probably doesn’t require encryption. Bottom line, if your DBAs and developers have unrestricted access to PII and PHI then you’re probably doing it wrong in the first place.

    • Jared – I’ve got some bad news for you.

      Out in the real world, most people are doing it wrong.

    • If the DBAs (not developers…that’s a different story) don’t have unrestricted access to all information in the database, how can anyone expect them to Administer the instance (btw-you do know that is the “A” in DBA, right?)? That’s what NDAs and real management policies are for. As a long-time DBA, in the real world, I’ve never had the time to go looking for someone’s PII or PHI, and I would argue that those “dbas” who seem to, need to be fired and publicly flogged for disparaging the good name of DBAs everywhere.

  • @Brent – I want to take a minute to thank you and your team for making such informative and enjoyable topics everyday. It’s one of the highlights of my day to (most likely) learn something new while getting a chuckle at the same time. You guys are SUPERB!!

  • That whole “Deterministic vs Random” encryption is a scary one. Deterministic opens you up to known-plaintext type attacks. Of course, if you select Random, every lookup is essentially a scan and not a seek (and one that brings all the rows back to the driver).

  • Deterministic isn’t so cryptographically insecure. I read a paper on it and there is some interesting math behind it. Of course, if someone knows a value, they can certainly find other matching values.

  • Trying to find out if you can use ‘always encrypt’ and not have the app server have access to the decrpyt but rather the report server. Reason I am looking to do this is because we are going to have a multi tenant database that we do not want ss’s displayed in the application but they do need to be displayed in the final report. Does anyone have any information if this is possible or even if this is a disaster of an idea?

    • Does the report server go through the app server? I’m confused about the architecture. If these are separate servers, then yes. The key is on the client (report server, Windows app, mobile, etc.). This client queries the db and gets the encrypted values, and decrypts them. If you don’t have the key, then that server can’t decrypt. If you query that column without having the key available, you get binary encrypted data.

  • Yes, all three are separate servers: App server, DB server, SSRS server. I am hoping to have just the SSRS server have the ability for plain text. The app would send the request for the report and the report would run and store off to a secure location. The app does not need the ability to see the pii. Only the SSRS needs that ability when generating a final report for the client. Thank you for your response\help on this Steve.

    • Olivia Allard
      May 28, 2019 3:17 pm

      Hey Scott – did you ever find a solution for this? I’m working through the same problem.

  • What do you suggest to use to encrypt account numbers, SSNs, challenge responses, etc? We were looking to use Always Encrypt, but we are still researching.

  • I remember seeing some presentation by MS for SQL 2017 or 2019, where MS handled/avoided the issue of sending the whole data set back to the client where filtering happens – by creating a secure memory area or something… Am i talking crap? Can you please comment on this?

    March 27, 2019 9:33 pm

    SELECT * FROM dbo.Customers WHERE accountno =’123456789′. I would like to encrypt account column. can I use SQLSERVER TDE for this purpose?

  • I am a little confused on this topic as well, So for securing Credit card numbers end to end, I can use Always encrypted, but should I use Deterministic or Randomized?
    The way i understand is that it has to deterministic because the credit card numbers will be associated to some groups (names), yes this will apparently make it less secure compare to randomized approach, yet this is the option we use.

    • For encryption of credit cards, you do t want advice from a blog post comment. Hire a security consultant.

      • I agree with Brent. There are pros and cons to all these security decisions. You can certainly get some thoughts here, or somewhere like SQLServerCentral, but you don’t want to build this yourself by guessing what might or might not work.

        Security and encryption are no joke. Hire someone with experience to help with this piece.

        • No i am not designing anything i am just reading up on the topic, so mistakes are acceptable lol
          I just wanted see if i am at least thinking in the right direction

  • We need to prove to external auditors that the DBA team can’t access market sensitive information in the database.

    Always Encrypted was looking good, until you hit the limitations with BI tools. KasperOnBI suggested using certificates on an AS server to do the decryption, which won’t prevent DBAs from accessing the data if the AS server is under their support.

    Any suggestions?

    • Scotty – that’s a permissions issue rather than encryption. (Encrypting things doesn’t stop someone from having the permissions & capability to decrypt them.)

      It’s well beyond a blog post comment – this is when you bring in a security consultant.