Always Encrypted

Always Encrypted is a client-side encryption technology for Microsoft SQL Server that protects sensitive data, such as credit card numbers or identification numbers, stored in SQL Server databases. Always Encrypted ensures that sensitive data is never seen by database administrators (DBAs) and other high-privilege users, and it helps protect against data breaches from inside the organization. Using Always Encrypted, sensitive data is encrypted in the client application, and only the authorized client applications or trusted drivers can access the data in plaintext.

Key Concepts

  • Client-Side Encryption: Encryption and decryption happen within the client application, not on the SQL Server.
  • Always Encrypted Keys: Master keys and data encryption keys are managed securely outside of SQL Server, often in a Windows Certificate Store or Azure Key Vault.
  • Column Master Key (CMK): A key that encrypts column encryption keys. Managed outside SQL Server.
  • Column Encryption Key (CEK): A key used to encrypt the data in the columns. Encrypted by a CMK.
  • Deterministic vs. Randomized Encryption: Deterministic encryption allows equality and range lookups on encrypted data, while randomized encryption provides stronger security but limits query operations.

How it Works

When Always Encrypted is enabled for a column, the client driver intercepts sensitive data before sending it to SQL Server. The client driver encrypts the data using a Column Encryption Key (CEK), which itself is encrypted by a Column Master Key (CMK). The encrypted data is then sent to SQL Server and stored. When a client application queries this data, the driver retrieves the encrypted data, uses the CEK to decrypt it, and then returns the plaintext data to the application.

Benefits

  • Enhanced Security: Protects sensitive data from unauthorized access by privileged database users and attackers who gain access to the database.
  • Compliance: Helps meet regulatory compliance requirements (e.g., GDPR, HIPAA) for data protection.
  • Simplified Key Management: Integrates with secure key stores like Azure Key Vault for robust key management.
  • Application Transparency: For applications using supported drivers, the encryption/decryption process is largely transparent, minimizing application code changes.

Getting Started

Implementing Always Encrypted involves several steps:

  1. Configure Key Stores: Set up your Column Master Keys in a secure store (e.g., Windows Certificate Store, Azure Key Vault).
  2. Create Column Encryption Keys: Generate CEKs and encrypt them using your CMKs.
  3. Modify Table Schema: Alter your tables to designate specific columns for Always Encrypted and specify the encryption type (deterministic or randomized).
  4. Update Client Applications: Ensure your client applications are using a version of the SQL Server client provider that supports Always Encrypted and configure them to access the CEKs.

Example SQL Syntax for Enabling Always Encrypted

This is a conceptual example. Actual implementation involves key management outside of SQL. The exact syntax may vary. Consult the official SQL Server documentation for the most up-to-date syntax and procedures.


ALTER TABLE Customers
ALTER COLUMN SSN ADD ENCRYPTED BY
(ENCRYPTION_TYPE = DETERMINISTIC,
  COLUMN_MASTER_KEY = 'MyCMKName',
  COLUMN_ENCRYPTION_KEY = 'MyCEKName');

Considerations and Limitations

  • Performance: Encryption and decryption operations can introduce some performance overhead.
  • Query Restrictions: Randomized encryption limits the types of operations you can perform on encrypted columns (e.g., no range queries or joins based on encrypted values).
  • Indexing: Deterministic encryption allows for indexing, but performance benefits might differ compared to non-encrypted columns.
  • Compatibility: Requires specific client driver versions and SQL Server configurations.

Resources

Resource Description
Get Started with Always Encrypted Step-by-step guide to implementing Always Encrypted.
Always Encrypted Key Management Details on managing Column Master Keys and Column Encryption Keys.
Always Encrypted Client Support Information on compatible client drivers and applications.