🗄️ SQL Server: Fix an Always Encrypted Column Rejecting a Simple WHERE Equality Filter Because It Uses Randomized Instead of Deterministic Encryption
Always Encrypted supports two encryption types per column: deterministic, which always produces the same ciphertext for the same plaintext value and therefore supports equality comparisons directly, and randomized, which produces different ciphertext every time specifically to resist frequency-analysis attacks on low-cardinality data. That extra security costs the server its ability to tell whether two randomized-encrypted values are equal at all without decrypting them first, so a plain equality filter against a randomized column isn’t just slower — it’s outright rejected or returns zero rows.
🔎 The Problem
CREATE TABLE Customers (
SSN varchar(11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ...)
-- chosen for extra security
);
SELECT * FROM Customers WHERE SSN = @ssn;
-- Error, or the parameterized query returns zero rows even for a
-- value that's definitely in the table - randomized ciphertext for
-- the same plaintext never matches itself
✅ Fix: Use Deterministic Encryption for Anything You Need to Filter On
- Use deterministic encryption specifically for any column you need to filter, join, or group on by equality, accepting the reduced protection against frequency analysis as the tradeoff for that column being queryable at all in its encrypted form.
- Keep randomized encryption only for columns that are purely stored-and-retrieved by some other key, never searched on directly by their own value.
- If a column genuinely needs both strong protection against frequency analysis and the ability to be searched, look at whether a separate searchable-hash pattern, computed client-side and stored alongside it, fits your threat model better than trying to force equality search onto randomized ciphertext directly.
🚨 Why This Is Dangerous
- Choosing randomized encryption for a column without checking whether any existing query filters on it is a mistake that often isn’t caught until that query runs in production and returns zero rows against data that’s actually there, well after the column’s encryption type is already locked in and expensive to change.
Randomized encryption’s whole job is making sure the same value never looks the same way twice — asking the database to find a match against that is asking it to do the one thing the encryption was designed to prevent.
