title

Always Encrypted

description

Overview of Always Encrypted that supports transparent client-side encryption and confidential computing in SQL Server and Azure SQL Database

author

jaszymas

ms.author

jaszymas

ms.reviewer

vanto, randolphwest

ms.date

07/25/2022

ms.prod

sql

ms.technology

security

ms.topic

conceptual

helpviewer_keywords

encryption [SQL Server], Always Encrypted

Always Encrypted

TCE Always Encrypted

Always Encrypted, about

SQL13.SWB.COLUMNMASTERKEY.CLEANUP.F1

monikerRange

=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Always Encrypted

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

:::image type="content" source="media/always-encrypted-database-engine/always-encrypted.png" alt-text="Diagram of Always Encrypted.":::

Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (for example, U.S. social security numbers), stored in [!INCLUDEssSDSFull], Azure SQL Managed Instance, and [!INCLUDEssNoVersion] databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the [!INCLUDEssDE]. This provides a separation between those who own the data and can view it, and those who manage the data but should have no access - on-premises database administrators, cloud database operators, or other high-privileged unauthorized users. As a result, Always Encrypted enables customers to confidently store their sensitive data in the cloud and to reduce the likelihood of data theft by their own malicious high-privileged users.

Always Encrypted can be configured to support limited confidential queries on encrypted data - the queries that involve equality comparisons, for example: point lookup-searches or equality joins. This is achieved by leveraging deterministic encryption (see below for more details)

Note

Secure enclaves extend confidential computing capabilities of Always Encrypted with pattern matching, other comparison operators and in-place encryption. For more details, see Always Encrypted with secure enclaves.

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the [!INCLUDEssDE], and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

Configure Always Encrypted

This section provides an overview of setting up Always Encrypted. For details and to get started, see Tutorial: Getting started with Always Encrypted.

To set up Always Encrypted in your database, you need to:

Provision cryptographic keys to protect your data. Always Encrypted uses two types of keys:
- column encryption keys
- column master keys
A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys.

You need to store column master keys in a trusted key store outside of the database system, such as Azure Key Vault, Windows certificate store, or a hardware security module.

Then, you need provision column encryption keys and encrypt each of them with a column master key.

Finally, you need to store the metadata about the keys in your database.
- The column master key metadata captures the location of teh column master key.
- The column encryption key metadata contains the encrypted value of the column encryption key. Note that the [!INCLUDEssDE] never stores or uses the keys of either type in plaintext.
For more information, Overview of key management for Always Encrypted
Configure encryption for selected database columns that contain sensitive data to be protected. This can involve creating new tables with encrypted columns or encrypting existing database columns and existing data. When setting up encryption for a column, you specify the information about an encryption algorithm, a column encryption key to protect the data in the column, and an encryption type. Always Encrypted supports two encryption types:
- Deterministic encryption always generates the same encrypted value for a given plaintext value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, it may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there's a small set of possible encrypted values, such as True/False, or North/South/East/West region.
- Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.
Use deterministic encryption for columns that will be used as search or grouping parameters. For example, a government ID number. Use randomized encryption for data such as confidential investigation comments, which aren't grouped with other records and aren't used to join tables. For details on Always Encrypted cryptographic algorithms, see Always Encrypted cryptography.

You can perform the above steps using SQL tools - SQL Server Management Studio (SSMS), SQL Server PowerShell, or sqlpackage - which automate the setup process.

To ensure Always Encrypted keys and protected sensitive data are never revealed in plaintext to the database environment, the [!INCLUDEssDE] can't be involved in key provisioning and data encryption or decryption operations. Therefore, Transact-SQL (T-SQL) doesn't support key provisioning or cryptographic operations. For the same reason, encrypting existing data or re-encrypting it (with a different encryption type or a column encryption key) needs to be performed outside of the database (SQL tools automate that).

Note

Always Encrypted with secure enclaves lifts some of the above restrictions by allowing cryptographic operations on existing data using Transact-SQL and eliminating the need to move the data outside of the database.

How queries against encrypted columns work

To run a query on encrypted database columns - to insert data to encrypted columns, retrieve plaintext values from encrypted columns, or perform supported operations (e.g., point lookup searches) on columns using deterministic encryption - users and applications issuing the query must to meet the following prerequisites:

Have access to the column master key protecting the data. Key access is required in addition to the database-level permissions, such as SELECT on the table containing the data.
Connect to the database with Always Encrypted enabled in the database connection. Most SQL tools and SQL client drivers support enabling Always Encrypted for database connections.

Note

If the user has required database permissions to read the data, but no access to the keys protected it, the user can still retrieve cyphertext (encrypted) data by connecting to the database without enabling Always Encrypted in the database connection.

Here is how queries on encrypted columns work:

When an application issues a parameterized query, the SQL client driver within the application transparently contacts the [!INCLUDEssDE] (by calling sp_describe_parameter_encryption (Transact-SQL) to determine which parameters target encrypted columns and, thus, should be encrypted. For each parameter that needs to be encrypted, the driver receives the encryption algorithm, encryption type, and key metadata, including the encrypted column encryption key and the location of its corresponding column master key.
The driver calls the key store, containing column master keys, in order to decrypt the encrypted column encryption key values. The resultant plaintext column encryption keys are cached to reduce the number of round trips to the key store on subsequent uses of the same column encryption keys.
The driver uses the obtained plaintext column encryption keys to encrypt the query parameters corresponding to encrypted columns.
The driver substitutes the plaintext values of the parameters targeting encrypted columns with their encrypted values, and it sends the query to the [!INCLUDEssDE] for processing.
The [!INCLUDEssDE] executes the query, which may involve equality comparisons on columns using deterministic encryption.
If query results include data from encrypted columns, the !INCLUDEssDE] attaches encryption metadata for each column, including the information about the encryption algorithm, the encryption type and key metadata, to the result set.
The !INCLUDEssDE] sends the result set to the client application.
For each encrypted columns in the reveived result set, the driver first tries to find the plaintext column encryption key in the local cache, and only makes a round trip to a key store holding the column master key, if it can't find the key in the cache.
The driver decrypts the results and returns plaintext values to the application.

A client driver interacts with a key store, containing a column master key, using a column master key store provider, which is a client-side software component that encapsulates a key store containing the column master key. Providers for common types of key stores are available in client-side driver libraries from Microsoft or as standalone downloads. You can also implement your own provider. Always Encrypted capabilities, including built-in column master key store providers vary by a driver library and its version.

See Develop applications using Always Encrypted for the list of client drivers supporting Always Encrypted and for information on how to develop applications that query encrypted columns.

You can also query encrypted columns using SQL tools, for example Azure Data Studio or SSMS.

Limitations

The following limitations apply to queries on encrypted columns:

Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (for example, greater/less than, pattern matching using the LIKE operator, or arithmetical operations). Deterministic encryption requires a column to have one of the binary-code point (_BIN2) collations.
No computations on on column encrypted using randomized encryption are allowed.

[!NOTE]
Always Encrypted with secure enclaves relaxes the above restriction by allowing pattern matching, comparison operators, sorting, and indexing on columns using randomized encryption. Such operations are allowed on string columns using both _BIN2 and UTF-8 collations.
Query statements that trigger computations involving both plaintext and encrypted data are not allowed. For example:
- Comparing an encrypted column to a plaintext column or a literal.
- Copying data from a plaintext column to an encrypted columns (or the other way around) UPDATE, BULK INSERT, SELECT INTO, or INSERT..SELECT.
- Inserting literals to encrypted columns.
Such statements result in operand clash errors like this:
```
Msg 206, Level 16, State 2, Line 89
    Operand type clash: char(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name = 'ssn') collation_name = 'Latin1_General_BIN2' is incompatible with char
```
Applications must use parameterized queries to insert data to encrypted columns or filter by encrypted columns. Inserting or filtering by literals is not supported. To issue such queries in SQL tools you need to use Parameterization for Always Encrypted available in Azure Data Studio SSMS.
After changing the definition of an encrypted column, execute sp_refresh_parameter_encryption to update the Always Encrypted metadata for the object.

Always Encrypted isn't supported for the columns with the below characteristics:

Columns using one of the following data types: xml, timestamp, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user-defined types.
FILESTREAM columns
Columns with the IDENTITY property.
Columns with ROWGUIDCOL property.
String (varchar, char, etc.) columns with non-bin2 collations when using deterministic encryption.
Columns that are keys for clustered and nonclustered indices when using randomized encryption (indices on columns using deterministic encryption are supported).
Columns included in full-text indexes (Always Encrypted does not support Full Text Search).
Computed columns.
Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted).
Sparse column set.
Columns that are referenced by statistics when using randomized encryption (deterministic encryption is supported).
Partitioning columns.
Columns with default constraints.
Columns referenced by unique constraints when using randomized encryption (deterministic encryption is supported).
Primary key columns when using randomized encryption (deterministic encryption is supported).
Referencing columns in foreign key constraints when using randomized encryption or when using deterministic encryption, if the referenced and referencing columns use different keys or algorithms.
Columns referenced by check constraints.
Columns captured/tracked using change data capture.
Primary key columns on tables that have change tracking.
Columns that are masked (using Dynamic Data Masking).
Columns in stretch database tables. (Tables with columns encrypted with Always Encrypted can be enabled for Stretch.)

[!IMPORTANT]
Stretch Database is deprecated in [!INCLUDE sssql22-md]. [!INCLUDE ssNoteDepFutureAvoid-md]
Columns in external (PolyBase) tables (note: using external tables and tables with encrypted columns in the same query is supported).
Table-valued parameters targeting encrypted columns aren't supported.

The following clauses can't be used for encrypted columns:

The following features don't work on encrypted columns:

SQL Server Replication (transactional, merge, or snapshot replication). Physical replication features, including Always, are supported.
Distributed queries (linked servers, OPENROWSET (Transact-SQL), OPENDATASOURCE (Transact-SQL)).
Cross-Database Queries that perform joins on columns (using deterministic encryption) from different databases.