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%’
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.
36 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?
Nah, something totally different.
Thanks. I’ll keep reading. Discovering many reasons not to do this, like you mention your clients do. Has this attitude/opinion/etc changed since you originally wrote this?
Mark – no.
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!!
Alan – hahaha, thanks!
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.
Right, that’s the problem – if you want to find, say, SSNs or salaries, you just build a numbers table, compare values, and voila…
Or passwords for that matter, using any of the common password dumps like: https://github.com/danielmiessler/SecLists/tree/master/Passwords
[…] https://www.brentozar.com/archive/2017/08/use-always-encrypted/ […]
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.
Hey Scott – did you ever find a solution for this? I’m working through the same problem.
[…] https://www.brentozar.com/archive/2017/08/use-always-encrypted/ […]
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.
Brian – that’s kinda beyond the scope of advice I can do in a blog post comment, sorry.
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?
Balaji – that is the Secure Enclave improvement for SQL Server 2019.
SELECT * FROM dbo.Customers WHERE accountno =’123456789′. I would like to encrypt account column. can I use SQLSERVER TDE for this purpose?
Mahendran – for questions about other topics, head to a Q&A site like https://dba.stackexchange.com.
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.
Another disadvantage of Always Encrypted is that you must use parameters to insert/update, so you can’t insert/update a batch of records at once; it has to be one row at a time, so you can assign the encrypted fields to parameters… hello rbar.
But, it is handy that you can see the data unencrypted in SSMS, and search (for a single exact match) without any bother.
Still looking for that option with only pros and no cons… wish me luck.
Always Encrypted (AE) bring more questions than answers. I have a requirement where some sensitive data need to be hidden from the cloud admin. AE can fulfill this need as far as I can see but… This column is also searchable for us, so this lead me to AE with enclave. SQL 2022 improved it a little from 2019 has I read. We are also using replication, no chance for this part on my research – I believe this is not working. To solved the bin2 collation issue in my early tests, I choose to convert my varchar column to nvarchar (this is the easiest way I think). I need to figure out how AE enclave will perform. Is this a rbar generator ? What about indexing ? what about stats? What about the impacts of enclave to the server (memory, cpu)? Is replication really nailed or enclave will let it go ? Not a lot of comments on this feature from real world experience out there. Nothing replace tests but some advice from experts are always welcome.
Sure, for personal advice on your particular architecture, click Consulting at the top of the screen.