Always Encrypted - SQL Server (2024)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Always Encrypted and Always Encrypted with secure enclaves are features designed to safeguard sensitive information, including credit card numbers and national or regional identification numbers (such as U.S. social security numbers), in Azure SQL Database, Azure SQL Managed Instance, and SQL Server databases. It enables clients to encrypt sensitive data within client applications, ensuring that encryption keys are never exposed to the Database Engine. 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 allows customers to securely store their sensitive data in the cloud, reducing the risk of data theft by malicious insiders.

Always Encrypted has certain restrictions, such as the inability to perform operations on encrypted data, including sorting, filtering (except for point-lookups using deterministic encryption), etc. This means that some queries and applications might not be compatible with Always Encrypted or might require significant changes to the application logic.

To address these limitations, Always Encrypted with secure enclaves enables the database engine to process encrypted data within a protected memory area called a secure enclave. Secure enclaves enhance the confidential computing capabilities of Always Encrypted by supporting pattern matching, various comparison operators, and in-place encryption.

Always Encrypted ensures that encryption is seamless for applications. On the client-side, Always Encrypted-enabled driver encrypts sensitive data before sending it to the Database Engine and automatically rewrites queries to maintain application semantics. It also automatically decrypts query results from encrypted database columns.

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 configure Always Encrypted in your database, follow these steps:

1. 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 the data within 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. After this, you should provision column encryption keys and encrypt each with a column master key.

   Finally, save the metadata about the keys in your database. The column master key metadata includes the location of the column master key. The column encryption key metadata contains the encrypted value of the column encryption key. The Database Engine doesn't store or use any keys in plaintext.

   For more information on managing Always Encrypted keys, see Overview of key management for Always Encrypted.

2. Set up encryption for specific database columns that include sensitive information to ensure protection. This might require creating new tables with encrypted columns or encrypting the existing columns and data. When configuring encryption for a column, you need to specify details about the encryption algorithm, the column encryption key to safeguard the data, and the type of encryption. Always Encrypted supports two types of encryption:

   • 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 might 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 unpredictably. Each identical plaintext input results in a distinct encrypted output. This improves the security of randomized encryption.

To perform pattern matching using comparison operators, sorting, and indexing on encrypted columns, you should adopt Always Encrypted with secure enclaves and apply randomized encryption. Always Encrypted (without secure enclaves) randomized encryption doesn't support searching, grouping, indexing, or joining on encrypted columns. Instead, for columns intended for search or grouping purposes, it's essential to use deterministic encryption. This allows operations such as point lookups, equality joins, grouping, and indexing on encrypted columns.

Since the database system has by design no access to cryptographic keys, any column encryption requires moving and encrypting data outside of the database. This means that this encryption process can take a long time and is vulnerable to network interruptions. Additionally, if you need to re-encrypt a column later, such as when rotating the encryption key or changing encryption types, you'll encounter the same difficulties again. Using Always Encrypted with secure enclaves eliminates the necessity of moving data out of the database. Because the enclave is trusted, a client driver within your application or a tool like Azure Data Studio or SQL Server Management Studio (SSMS) can safely share the keys with the enclave during cryptographic operations. The enclave can then encrypt or re-encrypt columns in place, significantly decreasing the time required for these actions.

For details on Always Encrypted cryptographic algorithms, see Always Encrypted cryptography.

You can perform the above steps using SQL tools:

• Provision Always Encrypted keys using SQL Server Management Studio
• Configure Always Encrypted using PowerShell
• 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 Database Engine 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 can automate that).

After changing the definition of an encrypted column, execute sp_refresh_parameter_encryption to update the Always Encrypted metadata for the object.

Limitations

The following limitations apply to queries on encrypted columns:

• No computations on columns encrypted using randomized encryption are allowed. Deterministic encryption supports the following operations involving equality comparisons - no other operations are allowed.

  • = (Equals) (Transact-SQL) in point lookup searches.
  • IN (Transact-SQL).
  • SELECT - GROUP BY- Transact-SQL.
  • DISTINCT.

  Note

  For applications that need to perform pattern matching, use comparison operators, sort, and index on encrypted columns, you should implement Always Encrypted with secure enclaves.

• Query statements that trigger computations involving both plaintext and encrypted data aren't allowed. For example:

  • Comparing an encrypted column to a plaintext column or a literal.
  • Copying data from a plaintext column to an encrypted column (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 need to use query parameters to provide values for encrypted columns. For example, when you're inserting data into encrypted columns or filtering them using deterministic encryption, query parameters should be used. It isn't supported to pass literals or T-SQL variables that correspond to encrypted columns. For more information specific to a client driver you're using, see Develop applications using Always Encrypted.

  In Azure Data Studio or SSMS, it's essential to apply parameterization for Always Encrypted variables to execute queries that handle values associated with encrypted columns. This includes scenarios such as inserting data into encrypted columns or applying filters on them (in cases where deterministic encryption is used).

• Table-valued parameters targeting encrypted columns aren't supported.

• Queries using the following clauses aren't supported:

  • FOR XML (SQL Server)
  • Format query results as JSON with FOR JSON

• 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 collations other than binary-code point (_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 doesn't support Full-Text Search).
  • Specify computed columns in a table.
  • Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted).
  • Use sparse columns.
  • 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 SQL Server 2022 (16.x) and Azure SQL Database. This feature will be removed in a future version of the Database Engine. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

  • Columns in external (PolyBase) tables (note: using external tables and tables with encrypted columns in the same query is supported).

• The following features don't work on encrypted columns:

  • SQL Server Replication (transactional, merge, or snapshot replication). Physical replication features, including Always On availability group, 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.

Always Encrypted Transact-SQL reference

Always Encrypted uses the following Transact-SQL statements, system catalog views, system stored procedures, and permissions.

Statements

System catalog views and stored procedures

Also see sys.columns for information on encryption metadata stored for each column.

Database permissions

There are four database permissions for Always Encrypted.

The following table summarizes the permissions required for common actions.

Important considerations

• The VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permissions are required when selecting encrypted columns, even if the user doesn't have permission to the column master keys (in their key stores), protecting the columns and doesn't access plaintext attempt.

• In SQL Server, both VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permissions are granted by default to the public fixed database role. A database administrator might choose to revoke (or deny) the permissions to the public role and grant them to specific roles or users to implement more restricted control.

• In SQL Database, the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permissions aren't granted by default to the public fixed database role. This enables certain existing legacy tools (using older versions of DacFx) to work properly. To work with encrypted columns (even if not decrypting them), a database administrator must explicitly grant the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permissions.

Related content

• Always Encrypted documentation

Next step