Always Encrypted in Plain Words

Some data should remain unreadable even to the database engine that stores it. Always Encrypted moves encryption and decryption to a trusted client, with important effects on queries and key management.

Follow the Always Encrypted Data Path

With Always Encrypted, an enabled client driver encrypts selected column values before sending them to SQL Server and decrypts results after receiving them. The database engine stores encrypted bytes and does not need the plaintext key for ordinary storage. This changes what a privileged server operator can see. It also changes what the engine can compute on that column.

I explain the data path first because the feature name sounds like a general switch. It is a column-level design involving the client driver, metadata, keys, and application behavior. A direct query from a tool without the correct encryption support will see ciphertext or fail for protected operations. The client is part of the security boundary.

Separate the Two Key Roles

A column encryption key protects the data values. A column master key protects the column encryption key and is referenced through an external key store or supported provider. SQL Server stores metadata and encrypted key material, while the usable master key stays outside the engine. Back up and govern those keys through the approved process.

I ask who can recover the keys after a disaster. A perfect database backup is incomplete if the restore environment cannot access the master key. Rotate and test key access as an operational task, not an afterthought. Keep duties separate where policy requires it. The person who can restore a database should not automatically receive the ability to read every protected value.

The key catalog shows the metadata objects that support protected columns. It does not expose the usable master key. I capture this inventory during migration planning so the application and recovery teams agree on which key paths must work after a restore.

SELECT name, key_store_provider_name, key_path
FROM sys.column_master_keys
ORDER BY name;

Compare Encryption Modes

Deterministic encryption produces the same encrypted value for the same plaintext under the same key and settings. That supports equality comparisons and some joins, but it reveals repetition patterns. Randomized encryption produces different ciphertext for repeated plaintext and provides stronger pattern protection, while limiting comparisons. The choice follows the application’s query needs and threat model.

I ask whether the column must be searched by equality or only stored and displayed. If search is unnecessary, randomized encryption is the simpler privacy choice. If equality is required, document the leakage trade-off and test the queries. Do not choose deterministic mode merely because it makes an old WHERE clause work. The security effect of repeated values matters.

Know the Always Encrypted Query Limits

The server cannot perform arbitrary operations on encrypted plaintext. Sorting, pattern matching, range predicates, and expressions can be limited depending on encryption mode and supported features. Secure enclaves expand some operations in supported configurations, but they require their own setup and attestation design. Test the exact queries and driver versions your application uses.

I have seen a design test only INSERT and SELECT, then discover that a report uses LIKE on the protected column. The report did not disappear from the business requirement. Inventory every predicate, join, sort, and aggregation before migration. A protected column changes the query contract, not just its storage format.

Inspect Column Metadata

Catalog views show which columns are configured for Always Encrypted and which encryption type they use. The metadata does not prove that the client has the key or that the application uses the feature correctly. It is a useful inventory before a schema change or security review. Run the query in the database that owns the tables.

I include the column and encryption type in the design record. If an application later adds a new report, the developer can see which fields require driver support and special query handling.

SELECT s.name AS schema_name,
       t.name AS table_name,
       c.name AS column_name,
       c.encryption_type_desc,
       c.column_encryption_key_id
FROM sys.columns AS c
JOIN sys.tables AS t
  ON t.object_id = c.object_id
JOIN sys.schemas AS s
  ON s.schema_id = t.schema_id
WHERE c.encryption_type IS NOT NULL
ORDER BY s.name, t.name, c.column_id;

Test Always Encrypted With the Real Driver

An enabled application driver can parameterize and encrypt values according to column metadata. A handwritten literal query in SSMS is not a substitute for testing the application path. Driver version, connection settings, key provider, and parameter types all affect success. Test reads, writes, error paths, and key-unavailable behavior in a representative environment.

I include a negative test: an account that can query the database but lacks key access should not see plaintext. Then I test the authorized application identity. Both outcomes matter. A migration that works only from an administrator’s workstation is unfinished. Make the application deployment process include key access and driver configuration.

Plan Migration and Rotation

Encrypting an existing column involves data movement and application coordination. Estimate the time and space using the actual table, then plan a change window and rollback. Changing keys later can require data or key metadata operations depending on the rotation type. Follow the supported tooling for the chosen design and test a full restore with key access.

I avoid promising a one-command retrofit. Large tables, indexes, dependencies, and application queries all affect the migration. Keep a copy of the schema and dependency inventory. Protect key material during backup and transfer. A secure feature implemented through an unreviewed key file on a shared drive defeats its purpose.

Remember Other Data Paths

The feature protects selected columns from server-side plaintext access. Other columns remain visible unless separately protected. Application logs, exports, caches, and screenshots can still expose decrypted values. Decide where the client is allowed to display or store them. Audit who can use the key, not only who can SELECT from the table.

I ask the application owner where the value goes after decryption. That question catches reporting tools and debug logs. A secure database column cannot control every downstream copy. Data classification and retention rules must follow the plaintext into the trusted client environment.

Decide Whether It Fits

Use Always Encrypted when the threat model requires the server to store protected values without plaintext access and the application can accept query and key-management changes. For transparent storage encryption against stolen files, TDE answers a different question. For connection traffic, TLS answers another. Choose the feature that matches the exposure you are trying to reduce.

What operations must SQL Server perform on this column? Answer that before selecting encryption mode. The feature is powerful when designed with the application. It is frustrating when added after the query contract is already fixed. Plain words and an end-to-end test save a lot of surprise.

Related reading on this blog: TDE, Column Encryption and Always Encrypted Compared and SQL SERVER 2016: Encrypt Your PII Data: Notes from the Field #132.

Always Encrypted is not a server checkbox, it is a client-and-key design for selected columns.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.