Data Protection is always on high priority for serious businesses and government organisations. On the other hand, the existing and established data protection techniques are being constantly challenged by new public cloud topologies.
Always Encrypted enable SQL Database Engine to keep data encrypted all the time (At Rest, In Use and In Transit). That means SQL Server Database Engine would put an additional protection layer on your data and making sure that unauthorised person (including DBAs, Developers) cannot see plain text value. It is very beneficial from a security perspective as it reduces the attack surface area.
Always Encrypted can be configured for individual database columns storing sensitive or confidential data. Always Encrypted uses two types of keys,
- Column Encryption Key (CEK)
- Column Master Key (CMK)
Column Encryption Key is used to encrypt column data, while Column Master Key is a Key-Protecting Key (KPK) which protect one or more CEK.
SQL Server Database Engine store configuration for each encrypted column in database metadata securely. Database Engine never store Column Master Key (CMK) in Database Server.
Encryption Key should be stored in External Key Store, i.e. Azure Key Vault, Certificate Store or HSM.
Always Encrypted at Rest and SQL Database Engine
Always Encrypted in Azure SQL Database and SQL Server protects sensitive data at rest on the server, ensuring sensitive data never appears as plaintext inside the database system.
SQL Server Database Engine computes the result set including encrypted columns in the result set and attaches the encryption metadata for individual columns.
Randomized Encryption vs Deterministic Encryption
As the name suggest Random, the algorithm generates un-deterministic value for the column every time. It provides great value but with no SQL Operations. It is an excellent option for tokenized data where query operations are limited to token value and not against data. i.e. Card Holder Data (CHD), SAD (Sensitive Authorisation Data). Indexing columns encrypted using randomised encryption is not supported.
If Randomised Encryption defeats the purpose of the data column than the Deterministic Encryption is a natural choice. The encryption option allows transparent retrieval of data and SQL Equality Operators (e.g. where, joins, distinct, group by), but no other operators.
Always Encrypted in Motion and Transport Layer Security
SQL Dataset remains encrypted on the move, too. The SQL Database Engine keep encryption intact while processing query and result set. The result set consists Encrypted CEK and other encryption metadata (SQL Server never attaches CMK – it always remain safe in external key store).
Additionally, transport layer security (e.g. TLS 1.2) add encryption wrapper on transmitting data. It provides two-fold encryption and reduces “Attack Surface Area”.
Always Encrypted in Use and Client Applications
To access encrypted data columns in plain text, an application must use Always Encrypted enabled client driver.
- Application issues a parameterised query, the driver, transparently collaborate with the SQL Database Engine and retrieve and identify encrypted columns present in parameterised query.
- Driver obtains encryption algorithm, encrypted CEK and location of CMK.
- Driver contacts the Key Store (i.e. Windows Certificate Store, Azure Key Vault or HSM) and retrieves CMK to decrypt CEK.
- Encrypt value of each parameter need to be encrypted.
- Cache CEK to reduce the number of round trips.
- Send a query to SQL Server Database Engine for processing.
Server computes the result set and transmit the resultset through a secure network.
- The driver first tries to find cached CEK otherwise retrieve through steps mentioned above (2,3,4,5).
- Decrypt the result set encrypted columns into plain text.
Encryption Key Caching at Client Application
The driver first tries to find the CEK in the local cache, and only makes around to the CMK, if it cannot locate the key in the cache.
Summary
Data threat are at its highest level; exploiters are advancing technology and exploiting known vulnerabilities. I see Always Encrypted is a significant step towards data protection and data privacy.
Public Cloud has many great benefits to offer but public accessibility and shared infrastructure poses unique and distinguish threats, SQL Server Always Encrypted would provide a sufficient countermeasure against these threats.
Nothing comes without a price tag, including this feature. Trade-offs are additional storage requirement, computing overhead (client and server), cost and engineering. However, when it comes to security, every case must be evaluated on sitting threats and risks.