Always Encrypted is a feature in Windows SQL Server 2019 designed to protect sensitive data both at rest and in flight between an on-premises client application server and Azure or SQL Server database(s).
Data protected by Always Encrypted remains in an encrypted state until it has reached the on–premises client application server, this effectively mitigates man in the middle attacks and provides assurances against unauthorized activity from rogue DBAs or admins with access to Azure / SQL server Databases. Always Encrypted was designed to be used in conjunction with Transparent Data Encryption; however, TDE is NOT a requisite for implementing Always Encrypted.
Configuring Always Encrypted involves creating and provisioning cryptographic keys, specifically:
-
A Column Master Key – The CMK, is an asymmetric RSA encryption key of size 2048 bits
-
One or more Column Encryption Key(s) - A CEK, is a symmetric AES key of size 256 bits
The CEK is responsible for encrypting the database column data while the CMK is protected by the Utimaco HSM and is responsible for wrapping (encrypting) the CEK. The Column Master Key is generated using the Utimaco CNG provider via the HSM and stored in an encrypted state within the HSM.
It is recommended that the server configured with Always Encrypted be located on a different server than that on which the database resides.
Always Encrypted supports two named types of encryptions, Deterministic and Randomized. Selecting deterministic encryption means that the same encrypted value will be produced from the same plaintext value each time encryption occurs, this allows for point lookups, equality joins, grouping and indexing on encrypted columns. However, this has implications on the security of the data as it potentially allows an attacker to ‘guess’ the plaintext from the recurring cipher text through emerging patterns within the encrypted columns. Deterministic encryption should not really be used where a small set of values are presented, example True / False, Yes / No etc. Randomized encryption is more secure, as it produces different cipher text values from the same plaintext every time the data is encrypted, eliminating the predictable aspects associated with deterministic encryption, however, this also removes the ability to perform any search operations on the encrypted data.