Always Encrypted


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 Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access. By ensuring on-premises database administrators, cloud database operators, or other high-privileged unauthorized users, can't access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to store their data in Azure, and enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Always Encrypted provides confidential computing capabilities by enabling the Database Engine to process some queries on encrypted data, while preserving the confidentiality of the data and providing the above security benefits. In SQL Server 2016 (13.x), SQL Server 2017 (14.x) and in Azure SQL Database, Always Encrypted supports equality comparison via deterministic encryption. See Selecting Deterministic or Randomized Encryption.


In SQL Server 2019 (15.x), secure enclaves substantially extend confidential computing capabilities of Always Encrypted with pattern matching, other comparison operators and in-place encryption. See Always Encrypted with secure enclaves.


Always Encrypted is available in all editions of Azure SQL Database, starting with SQL Server 2016 (13.x) and all service tiers of SQL Database. (Prior to SQL Server 2016 (13.x) SP1, Always Encrypted was limited to the Enterprise Edition.) For a Channel 9 presentation that includes Always Encrypted, see Keeping Sensitive Data Secure with Always Encrypted.

Typical Scenarios

Client and data on-premises


Client on-premises with data in Azure


Client and Data in Azure

A customer has a client application, hosted in Microsoft Azure (for example, in a worker role or a web role), which operates on sensitive data stored in a database hosted in Azure (SQL Database or SQL Server running in a virtual machine on Microsoft Azure). Although Always Encrypted doesn't provide complete isolation of data from cloud administrators, as both the data and keys are exposed to cloud administrators of the platform hosting the client tier, the customer still benefits from reducing the security attack surface area (the data is always encrypted in the database).

How it Works





The server computes the result set, and for any encrypted columns included in the result set, the driver attaches the encryption metadata for the column, including the information about the encryption algorithm and the corresponding keys. The driver first tries to find the plaintext column encryption key in the local cache, and only makes a round to the column master key if it can't find the key in the cache. Next, 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.

For details of how to develop applications using Always Encrypted with particular client drivers, see Develop applications using Always Encrypted.


Encryption and decryption occurs via the client driver. This means that some actions that occur only server-side will not work when using Always Encrypted. Examples include copying data from one columng to another via an UPDATE, BULK INSERT(T-SQL), SELECT INTO, INSERT..SELECT.

Here's an example of an UPDATE that attempts to move data from an encrypted column to an unencrypted column without returning a result set to the client:

update dbo.Patients set testssn = SSN

If SSN is a column encrypted using Always Encrypted, the above update statement will fail with an error similar to:

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

To successfully update the column, do the following:

  1. SELECT the data out of the SSN column, and store it as a result set in the application. This will allow for the application (client driver) to decrypt the column.
  2. INSERT the data from the result set into SQL Server.


In this scenario, the data will be unencrypted when sent back to the server because the destination column is a regular varchar that does not accept encrypted data.

Selecting Deterministic or Randomized Encryption


  • Deterministic encryption always generates the same encrypted value for any given plain text 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. Deterministic encryption must use a column collation with a binary2 sort order for character 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.

Configuring Always Encrypted


Task SSMS PowerShell T-SQL
Creating key metadata in the database. Yes Yes Yes
Creating new tables with encrypted columns Yes Yes Yes
Encrypting existing data in selected database columns Yes Yes No


Always Encrypted with secure enclaves, introduced in SQL Server 2019 (15.x), does support encrypting existing data using Transact-SQL. It also eliminates the need to move the data outside of the data for cryptographic operations.



For details on configuring Always Encrypted see:

Getting Started with Always Encrypted

Use the Always Encrypted Wizard to quickly start using Always Encrypted. The wizard will provision the required keys and configure encryption for selected columns. If the columns you're setting encryption for already contain some data, the wizard will encrypt the data. The following example demonstrates the process for encrypting a column.



  2. Right-click your database, point to Tasks, and then click Encrypt Columns to open the Always Encrypted Wizard.
  3. Review the Introduction page, and then click Next.
  6. For each column selected for encryption, select an Encryption Key. If you have not previously created any encryption keys for this database, select the default choice of a new autogenerated key, and then click Next.
  9. On the Summary page, review the options you've selected, and then click Finish. Close the wizard when completed.

Feature Details

  • 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).

  • Queries on columns encrypted by using randomized encryption can't perform operations on any of those columns. Indexing columns encrypted using randomized encryption isn't supported.


Always Encrypted with secure enclaves, introduced in SQL Server 2019 (15.x), addresses the above limitation by enabling pattern matching, comparison operators and indexing on columns using randomized encryption.




Always Encrypted isn't supported for the columns with the below characteristics. For example, if any of the following conditions apply to the column, the ENCRYPTED WITH clause can't be used in CREATE TABLE/ALTER TABLE for a column:

  • 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.
  • Columns that are keys for clustered and nonclustered indices when using randomized encryption (deterministic encryption is supported).
  • Columns that are keys for fulltext indices when using randomized encryption (deterministic encryption is supported).
  • Computed columns.
  • Sparse column set.
  • Columns that are referenced by statistics when using randomized encryption (deterministic encryption is supported).
  • Columns using alias types.
  • 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).
  • 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).
  • 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:

  • Transactional or merge replication
  • Distributed queries (linked servers, OPENROWSET(T-SQL), OPENDATASOURCE(T-SQL))

Tool Requirements

  • SQL Server Management Studio version 18 or higher is recommended to run queries that decrypt the results retrieved from encrypted columns or insert, update, or filter encrypted columns.
  • Requires sqlcmd version 13.1 or higher, which is available from the Download Center.

Database Permissions

There are four permissions for Always Encrypted:





The following table summarizes the permissions required for common actions.

Querying encrypted columns X X

Important notes:


  • The two VIEW permissions are required when selecting encrypted columns, even if the user doesn't have permission to decrypt the columns.





WITH (  
     KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420'  
    ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003200660061006600640038003100320031003400340034006500620031006100320065003000360039003300340038006100350064003400300032003300380065006600620063006300610031006300284FC4316518CF3328A6D9304F65DD2CE387B79D95D077B4156E9ED8683FC0E09FA848275C685373228762B02DF2522AFF6D661782607B4A2275F2F922A5324B392C9D498E4ECFC61B79F0553EE8FB2E5A8635C4DBC0224D5A7F1B136C182DCDE32A00451F1A7AC6B4492067FD0FAC7D3D6F4AB7FC0E86614455DBB2AB37013E0A5B8B5089B180CA36D8B06CDB15E95A7D06E25AACB645D42C85B0B7EA2962BD3080B9A7CDB805C6279FE7DD6941E7EA4C2139E0D4101D8D7891076E70D433A214E82D9030CF1F40C503103075DEEB3D64537D15D244F503C2750CF940B71967F51095BFA51A85D2F764C78704CAB6F015EA87753355367C5C9F66E465C0C66BADEDFDF76FB7E5C21A0D89A2FCCA8595471F8918B1387E055FA0B816E74201CD5C50129D29C015895CD073925B6EA87CAF4A4FAF018C06A3856F5DFB724F42807543F777D82B809232B465D983E6F19DFB572BEA7B61C50154605452A891190FB5A0C4E464862CF5EFAD5E7D91F7D65AA1A78F688E69A1EB098AB42E95C674E234173CD7E0925541AD5AE7CED9A3D12FDFE6EB8EA4F8AAD2629D4F5A18BA3DDCC9CF7F352A892D4BEBDC4A1303F9C683DACD51A237E34B045EBE579A381E26B40DCFBF49EFFA6F65D17F37C6DBA54AA99A65D5573D4EB5BA038E024910A4D36B79A1D4E3C70349DADFF08FD8B4DEE77FDB57F01CB276ED5E676F1EC973154F86  
CREATE TABLE Customers (  
    CustName nvarchar(60)   
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    SSN varchar(11)   
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    Age int NULL  

See Also