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.