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 CMK is generated using the Utimaco CNG provider via the HSM, and it is 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 ciphertext through emerging patterns within the encrypted columns. Deterministic encryption should not be used where a small set of values are presented, for example True/False, Yes/No etc. Randomized encryption is more secure, as it produces different ciphertext values from the same plaintext every time the data is encrypted, eliminating the predictable aspect associated with deterministic encryption. However, this also removes the ability to perform any search operations on the encrypted data.