First published on MSDN on Oct 05, 2017
Last week at Ignite, we announced a major SQL security investment that enhances Always Encrypted with secure enclave technologies to enable:
on encrypted columns, including pattern matching, range comparisons, and sorting, which unlocks Always Encrypted to a broad range applications and scenarios that require such computations to be performed inside the database system.
- schema changes that involve cryptographic operations on sensitive data, for example: initial data encryption, rotating a column encryption key, or changing a data type of an encrypted column, without migrating the data outside of the database for all cryptographic operations.
Introduced in 2016, Always Encrypted makes it possible to encrypt sensitive data in use to ensure the confidentiality of the data is protected from malware the machines hosting the database may be infected with, and from high-privilege, but unauthorized users, including machine admins, DBAs and administrators of the cloud platform hosting the database. However, in the currently generally available versions of SQL Server (2016 and 2017) and Azure SQL Database, this unparalleled level of protection comes at a price that is too high to some customers: functionality of queries on encrypted columns gets substantially reduced, as equality comparison is the only SQL operation possible on encrypted columns (and only with deterministic encryption). Other challenges, we hear customers often struggle with today, stem from the fact that the SQL Server Engine cannot (by design) perform any cryptographic operations on sensitive data and never has access to the cryptographic keys. To encrypt existing data or make any schema changes involving re-encrypting or decrypting the data, customers need to use tools, such as
Always Encrypted Wizard in SSMS
or the Set-SqlColumnEncryption cmdlet in PowerShell, which load the data, encrypt, decrypt or re-encrypt it and upload the data back to the database. This process can take a long time and is prone to network errors.
To address the above challenges, we plan to leverage cutting-edge secure enclave technologies, such as Intel Software Guard Extensions (SGX). An enclave is a protected region of memory that acts as a trusted execution environment. An enclave appears as a black box to the containing process and to other processes running on the machine. There is no way to view the data or the code inside the enclave from the outside, even with a debugger. In the case of SGX, this guarantee is hardware-based and provided by the processor. Enclaves also ensure that only authorized code is permitted to run inside an enclave and access the data. If the code is altered or tampered with, the operations are denied, and the environment disabled. The enclave enforces these protections throughout the execution of code within it.
The way the enhanced Always Encrypted feature uses enclaves is illustrated on the below diagram. The SQL Server Engine process contains an enclave, loaded with the code implementing cryptographic algorithms, used in Always Encrypted (e.g. AES-256), as well as the code implementing SQL operations, such LIKE predicates or range comparisons (e.g. using '<' or '>'). Before submitting a query to the SQL Server Engine for processing, the SQL client driver inside the application sends the keys, that are required for the operations in the query, to the enclave over a secure channel. When processing queries, the SQL Server Engine delegates rich computations and cryptographic operations on encrypted columns to the enclave, where the data is safely decrypted and processed. Note that sensitive data remains encrypted within the SQL Server Engine, when outside of the enclave.
While the new architecture will enable us to support a broad range of computations on encrypted columns inside the SQL Server Engine, we initially target the most commonly requested operations, such as pattern matching (LIKE), range queries, sorting (ORDER BY), as well as a broad set of data type conversions. The below example shows a query searching employee records based on the last four digits of a social security number (assuming the SSN column is encrypted).
DECLARE @SSNPattern CHAR(11) = '%9838'
SELECT * FROM [dbo].[Employees] WHERE [SSN] LIKE @SSNPattern
Enabling cryptographic operations locally, eliminates the need to move the data out of the database for initial encryption or for other encryption-related schema changes. This greatly improves performance and reliability of such operations and means the operations can be triggered via
ALTER TABLE ALTER COLUMN
T-SQL statements and do not require special client-side tools. Here is an example of a statement that triggers a rotation of a column encryption key:
--Assuming SSN is a plaintext column, the below statement encrypts data in the column.
ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11)
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
Always Encrypted using enclaves is currently in an early access technology preview. If you have a workload that can benefit from the new capabilities, please
sign up for the preview at
and help influence the final shape of this technology in future versions of the SQL platform products and services.
To learn more and see the demos of Always Encrypted using enclaves, you can watch the below recordings of Ignite sessions: