Column-level encryption vs Always encrypted

A common question from developers & data owners  is what benefits does Always Encrypted offer over column level encryption  aka cell level encryption?    First thing to understand is what are the basic differences between the two methods - Column-level encryption vs Always encrypted

Always Encrypted encrypts sensitive data in client applications without revealing the encryption keys to the database engine, providing separation between data owners and data managers (DBAS , Server Administrators etc) . 

When a column intended for encryption is set up - the DBA must specify encryption algorithm and cryptographic keys. There is a concept of : column encryption keys & column master keys.

An Always Encrypted enabled client driver is required to access stored data. Once the driver gets the encryption metadata information required from the database , it has to then obtain the column master key from the designated key store.

As all encryption \ decryption activity occurs through the client - driver - the result is that server-side only activity only won't work in an Always Encrypted scenario.

Here are some examples :

1) Moving\copy data from  column to column  - using UPDATE,SELECT INTO.,INSERT..SELECT and BULK INSERT

2)Triggers, temporal tables, Change Data Capture (CDC) ,sparse columns, full-text, in-memory OLTP

For a full set up details of what data structures are  supported  and always encrypted limitations  check the Microsoft documentation 

Cell-level encryption or aka column-level encryption is enabled on columns with data classified as sensitive. The data is encrypted on disk, remaining encrypted in memory until the DECRYPTBYKEY function is used to decrypt it. (The data is encrypted by the EncryptByKey  function). Therefore, although the SQL data is encrypted, it is not secure beyond simply using a function in the user context to decrypt it.   

From a security perspective - this means if a login has the relevant privileges - such as sysadmin - they can access the data by simply logging on to the SQL Server , executing a SELECT statement with the DECRYPTBYKEY and view the decrypted data.    

An added step - as a  function (DECRYPTBYKEY) is required to decrypt the data, client applications and associated code need altering to work with column-level encryption.

If you want to see more details : The simple guide to column-level encryption

Notes

This is brief summary of differences between Always-Encrypted and column-level encryption. Both methods have their own details related to setup. Deciding on which method to use will depend on whether there is a requirement to separate the access of data between DBAs and data owners.

If there is an absolute requirement to block DBAs or database managers from viewing the encrypted data , than Always Encrypted is the way to go.   Otherwise column-level encryption may be a more practical approach. 

Read more on SQL Server Encryption

The simple guide to column-level encryption

TLS v SSL : which should you use

 A simple guide to Transparent Data Encryption in AWS RDS SQL Server