Always Encrypted

SQL Server 2019 includes Always Encrypted, an encryption technology first introduced in SQL Server 2016 which allows clients to encrypt sensitive data inside client applications with the key benefit of never revealing the encryption keys to the database engine.

When using Always Encrypted, data never appears in plain text when querying it, and it is not even exposed in plain text in the memory of the SQL Server process. Only client applications that have access to the relevant keys can see the data. This feature is ideal for protecting data from even highly privileged users such as database administrators and system administrators. It does not prevent them from administrating the servers, but it does prevent them from viewing highly sensitive data such as bank account details.

Algorithm types

Always Encrypted uses the AEAD_AES_256_CBC_HMAC_SHA_256 algorithm. There are two variations: deterministic and randomized. The deterministic encryption always generates the same encrypted value of a given input value. With this encryption type it is possible for your application to perform point lookups, equality joins, indexing and grouping on the encrypted column. The only potential issue of using this encryption type is if the encrypted column contains few values or if the statistics about plaintext data distribution is publicly known – in such cases, an attacker might be able to guess the underlaying plaintext values.

The randomized variation is far less predictable hence more secure but this means that it does not allow such operations mentioned earlier on potential encrypted columns. The different encryption types raise interesting choices for application developers. For example, if you know that your applications must issue group or join-based queries on encrypted columns, then you will have no choice but to use the deterministic algorithm. With the introduction of secure enclaves in SQL Server 2019 support for richer functionality on encrypted columns is now possible, which will be discussed later in the chapter.

Setup

Setting up Always Encrypted is straightforward. For a complete tutorial on how to do this please see the following link: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-wizard?view=sql-server-ver15.