SQL Server Always Encrypted: How Secure Enclaves Change Query Support
Always Encrypted encrypts SQL Server columns on the client, so the database engine never sees their plaintext. Secure enclaves can run some otherwise blocked query operations inside a protected environment. The article walks through choosing an encryption type, defining the column and configuring a .NET client.
Key points
- Randomized encryption does not support ordinary equality searches the way deterministic encryption does, so use it when minimizing leakage matters most.
- Deterministic encryption allows equality lookups, but matching ciphertext reveals which rows share a value.
- With secure enclaves, range comparisons, LIKE matching and sorting can work for eligible operations and configurations.
- The column encryption key and column master key must exist and be configured before the column is defined.
- Column Encryption Setting=Enabled only turns on client-side processing; it does not configure enclaves by itself.
- Use parameterized queries and verify the SQL Server version, key store and attestation setup before relying on enclave operations.
Always Encrypted keeps selected SQL Server columns encrypted on the client, so the database engine never handles their plaintext. The trade-off is that classic deterministic and randomized encryption support only narrow query shapes, with range comparisons, LIKE-style matching and sorting generally out of reach. The article explains how secure enclaves relax that limit: SQL Server can run supported operations on decrypted data inside a protected execution environment that keeps the plaintext away from the ordinary engine.
The walkthrough covers picking deterministic or randomized encryption for each column, defining the column with a column encryption key, and turning on Column Encryption Setting in a Microsoft.Data.SqlClient connection string. The author stresses that this setting alone does not configure enclaves; the SQL Server version, key store, attestation and driver must all be verified. Parameterized queries are the most predictable way to query encrypted columns.
Real-world use
- Storing national IDs, IBANs or health record fields in an ASP.NET Core API so that even database administrators cannot read them in plaintext.
- Filtering encrypted account balances by range in a finance application that needs more than equality lookups.
- Migrating personally identifiable information columns in an existing SQL Server database to Always Encrypted through Microsoft.Data.SqlClient.
Why it matters
Which queries work on encrypted columns depends on the encryption type and enclave setup, so decide this while designing the schema. Enabling the connection setting alone is not enough.