Always Encrypted vs CLE

Question

We currently use column-level encryption in SQL Server 2008/2012 but plan to migrate to SQL 2019.

I see articles regarding TDE vs Always Encrypted (AE) and understand the differences. Since AE encrypts specific columns, much like CLE does, it seems this is a more apples-to-apples type comparison. In our use case we are considering using AE instead of CLE. In both cases, they would be used in conjunction with TLS for encrypting data in transfer. AE would give us the additional benefit over CLE that "high-privileged unauthorized users, can't access the encrypted data." Also, since we are not performing comparison operations on our encrypted data, and our data is basic varchar type, the limitations of AE would not come into play for our use cases.

Has anyone made a "switch" from using CLE to using AE for sensitive data? If so, any caveats or lessons learned to share?

Answer 1

Hi @Mark Sanchez ,

Available in all editions of SQL Server, cell-level encryption can be enabled on columns that contain sensitive data. Always Encrypted is available in SQL Server 2016 and later, but only in Enterprise editions.

Encryption and decryption occurs via the client driver. This means that some actions that occur only server-side will not work when using Always Encrypted. These actions include (but are not limited to):
• Copying data from one column to another via an UPDATE, BULK INSERT(T-SQL), SELECT INTO, INSERT..SELECT.
• Triggers, temporal tables, sparse columns, full-text, in-memory OLTP, and Change Data Capture (CDC).

Suggest you read the MS document Always Encrypted and the blog SQL Server Encryption Explained: TDE, Column-Level Encryption and More to get more information.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 2

Thanks. One of our vendors of an application we've integrated mentions the same server-side actions as to why they don't currently support AE.

BTW, AE has also been supported on SQL Server Standard 2016 since SP1.