Home > Documentation > SQL Server > Security > Always Encrypted

Always Encrypted

Introduction to Always Encrypted

Always Encrypted is a client-side encryption technology designed to protect sensitive data in Azure SQL Database and SQL Server. It ensures that sensitive data is always encrypted, even when it's being processed in memory by SQL Server.

This feature enhances data security by encrypting data at the application's data source and decrypting it at the application's destination. This means that data is encrypted when it's sent from the client to the server and remains encrypted while it's stored in the database. Only authorized clients with the appropriate encryption keys can access the plaintext data.

Note: Always Encrypted provides robust security for sensitive data, but it's crucial to manage your encryption keys securely. Loss of keys will result in permanent loss of access to the encrypted data.

Key Benefits:

  • Data Protection at Rest and in Transit: Sensitive data is encrypted both when stored in the database and when sent over the network.
  • Client-Side Encryption: Encryption and decryption happen on the client side, ensuring SQL Server never sees the plaintext data.
  • Separation of Duties: Database administrators (DBAs) can manage the database and access encrypted data without needing access to the plaintext data.
  • Application Transparency: For many operations, applications can use Always Encrypted with minimal or no changes.

How Always Encrypted Works

Always Encrypted uses two main types of keys:

  • Column Master Keys (CMKs): These are keys stored securely outside the database, such as in Azure Key Vault, Windows Certificate Store, or a Hardware Security Module (HSM).
  • Column Encryption Keys (CEKs): These are symmetric keys used to encrypt the actual data within the database columns. CEKs are encrypted using a CMK.

When data is inserted or updated:

  1. The client application retrieves the CEK.
  2. The CEK is used to encrypt the data.
  3. The CEK is then encrypted using the CMK.
  4. The encrypted data and the encrypted CEK are sent to SQL Server.

When data is queried:

  1. SQL Server returns the encrypted data and the encrypted CEK to the client.
  2. The client application retrieves the CMK.
  3. The CMK is used to decrypt the CEK.
  4. The decrypted CEK is then used to decrypt the data.

Tip: Always Encrypted supports two encryption types: deterministic and probabilistic. Deterministic encryption allows for equality lookups, joins, and WHERE clauses, while probabilistic encryption provides stronger security but limits query operations.

Implementing Always Encrypted

Implementing Always Encrypted involves several steps:

  1. Choose a Key Store: Select where to store your Column Master Keys (e.g., Azure Key Vault).
  2. Create Column Master Keys: Generate and store your CMKs in the chosen key store.
  3. Create Column Encryption Keys: Generate CEKs and encrypt them using your CMKs.
  4. Configure Database Tables: Modify your table schemas to specify which columns will be encrypted and which CEK to use.
  5. Update Applications: Modify your client applications to support Always Encrypted, ensuring they have access to the necessary keys and can perform the encryption/decryption operations.

Example SQL Statement:

To alter a table to encrypt a column:


ALTER TABLE Customers
ALTER COLUMN CreditCardNumber VARCHAR(25) ENCRYPTED WITH
    (ENGORITHM = 'AEAD_AES_256_CBC', COLUMN_ENCRYPTION_KEY = 'MyCustomerCreditCardKey')

Important: Ensure your SQL Server client drivers (e.g., .NET Framework Data Provider for SQL Server, ODBC Driver for SQL Server) are updated to versions that support Always Encrypted.

Always Encrypted with Secure Enclaves

Always Encrypted with Secure Enclaves further enhances security by allowing computations on encrypted data within a secure enclave on the server. This enables operations like range comparisons and grouping on encrypted columns while keeping the keys and plaintext data isolated from the SQL Server process.

This feature requires specific hardware and driver support, making it suitable for scenarios demanding the highest level of data protection.

Related Documentation: