Always Encrypted is a data encryption technology that helps protect sensitive data at rest on the server, during movement between client and server, and while the data is in use, ensuring that sensitive data never appears as plaintext inside the database system.

The following illustration depicts the Always Encryption process.

  1. A user tries to execute a SQL statement from an application
  2. The SSN is encrypted in the database, so it is re-routed to the Enhanced ADO.NET Library
  3. The SSN the user specified is then encrypted and sent onto SQL Server
  4. SQL Server creates a SQL statement from the request, plus the SSN replaced with the cipher value
  5. The database then filters all rows that DO NOT match the cipher value
  6. The data is re-routed through the Enhanced ADO.NET Library so the cipher value can be decrypted
  7. The result set is returned to the user with the decrypted value

 

Type of Always Encrypted

Deterministic Encryption:

  • Always generates the same encrypted value for any given plain text value
  • Allows grouping, filtering by equality, and joining tables based on encrypted values
  • Allows unauthorized users to guess information about encrypted values by examining patterns in the encrypted column
  • Must use a column collation with a binary2 sort order for character columns

Random:

  • Encrypts data in a less predictable manner
  • Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns

Column Master Keys

  • Used to encrypt column encryption keys
  • Must be stored in a trusted key store
  • Information about column master keys, including their location, is stored in the database in system catalog views

Column Encryption Keys

  • Used to encrypt sensitive data stored in database columns
  • Can be encrypted using a single column encryption key
  • Encrypted values of column encryption keys are stored in the database in system catalog views
  • Store column encryption keys in a secure/trusted location for backup

 

Limitations and Technologies NOT Supported

  • Encrypted columns do not allow range-like operations such as ‘>, <‘ , ‘LIKE’, etc.
  • Passing encrypted values to functions, user-defined or otherwise, is not allowed(the database doesn’t have access to the unencrypted values)
  • Equality comparisons can only be performed on columns that use deterministic encryption
  • Indexes can only be applied to deterministic encryption columns
  • Need same column encryption key for columns that are joined
  • Constant expressions that refer to encrypted columns not allowed ex. WHERE SSN = ‘111-11-1111’, but WHERE SSN = @SSN is because the driver works with the SqlParameter class
  • Unsupported data types: xml, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, and UDFs
  • Currently the only driver that supports this feature is .NET 4.6
  • This is NOT TDE
  • Encrypted columns take significantly more space
  • String-based columns that are encrypted with deterministic encryption must use a _BIN2 collation (e.g. Latin1_General_BIN2)
  • The following data types are NOT supported as encrypted columns, per the documentation:text/ntext/image ,XML/hierarchyid/geography/geometry,alias types/user-defined data types,SQL_VARIANT,rowversion (timestamp) ,Sparse columnset (sparse columns are okay, provided the table doesn’t contain a columnset),Built-in alias types, e.g. SYSNAME,Identity columns,Computed columns,Temporal tables,Triggers are partially supported,Full-text search,Replication (need more research),In-Memory OLTP,Stretch Database