SQL Server vs MongoDB: When the cloud is your adversary (Always Encrypted vs Queryable Encryption)

The real barrier to the cloud

When a company still refuses to put its sensitive data in the cloud, the reason usually isn’t cost or performance: it’s data sovereignty. In the cloud, someone else is the administrator of the machine; therefore the provider can, in theory, read the data files, see the data being used in memory, or read the backups. The threat model is no longer the external attacker but the privileged insider hosting the database.

As covered previously on my blog Beyond TDE and TLS: Bridging the Data Security Governance Gap in Lower Environments, various encryption methods can protect you. For example, TLS protects data in transit and TDE protects it at rest, but as soon as the engine runs a query, it handles plaintext in memory. So there are three states to protect: at-rest, in-transit, in-use. The gap this article focuses on is the last one.

That is exactly what Always Encrypted (SQL Server) and Queryable Encryption (MongoDB) target. The common principle: encryption and decryption happen client-side, in the driver; the keys never reach the engine. The data stays encrypted at rest, in transit, and during processing. The DBA, the cloud operator, the hypervisor admin: all of them see only cyphertext.

That leaves one question: if the engine sees only cyphertext, how does it answer a WHERE condition? Both database engines do answer it, but through different technical means.

SQL Server: queryability lives in the cyphertext

To demonstrate all this, let’s start by creating a table with two columns, Salary and Department, encrypted deterministically on one side and randomized on the other:

DROP TABLE IF EXISTS dbo.Employees;
CREATE TABLE dbo.Employees (
  Id         INT IDENTITY(1,1) PRIMARY KEY,
  LastName   NVARCHAR(50) COLLATE Latin1_General_BIN2 NOT NULL,
  FirstName  NVARCHAR(50) COLLATE Latin1_General_BIN2 NOT NULL,
  DeptDet    NVARCHAR(30) COLLATE Latin1_General_BIN2 NOT NULL, -- will be DETERMINISTIC
  DeptRand   NVARCHAR(30) COLLATE Latin1_General_BIN2 NOT NULL, -- will be RANDOMIZED
  SalaryDet  INT NOT NULL, -- will be DETERMINISTIC
  SalaryRand INT NOT NULL  -- will be RANDOMIZED
);
GO

INSERT INTO dbo.Employees (LastName, FirstName, DeptDet, DeptRand, SalaryDet, SalaryRand) VALUES
('Martin',  'Alice', 'Sales', 'Sales', 55000, 55000),
('Dubois',  'Bob',   'Sales', 'Sales', 48000, 48000),
('Bernard', 'Chloe', 'Sales', 'Sales', 52000, 52000),
('Petit',   'David', 'IT',    'IT',    72000, 72000),
('Durand',  'Emma',  'IT',    'IT',    68000, 68000);

Five rows: three Sales, two IT. The BIN2 collation is required by Always Encrypted (link to documentation), and the master key lives outside the database (Key Vault, certificate store, or HSM).

Column encryption isn’t done in T-SQL, because the engine doesn’t have the keys. It’s driven from the client (here in PowerShell), declaring for each value a deterministic column and its randomized twin:

Import-Module SqlServer -MinimumVersion 22.0.59
$sqlConnectionString = "Data Source=.\LAB2025;Initial Catalog=AEDEMO;Integrated Security=True;Encrypt=False;Trust Server Certificate=False"
$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

$encryptionChanges  = @()
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Employees.DeptDet    -EncryptionType Deterministic -EncryptionKey "CEK1"
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Employees.DeptRand   -EncryptionType Randomized    -EncryptionKey "CEK1"
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Employees.SalaryDet  -EncryptionType Deterministic -EncryptionKey "CEK1"
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Employees.SalaryRand -EncryptionType Randomized    -EncryptionKey "CEK1"

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

In this example, I’m working on my 2025 SQL Server instance, on the AEDEMO database, using the column encryption key CEK1 I created beforehand (itself protected by a column master key stored outside the database).

We check that the engine sees the right type per column:

SELECT c.name, c.encryption_type_desc
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.Employees');

The deterministic mechanism works like this: same plaintext, same cyphertext (an injective function). On an Always Encrypted-enabled connection with Parameterization for Always Encrypted and parameters for the predicates (never literals), equality works:

DECLARE @d NVARCHAR(30) = 'Sales';
SELECT DeptDet AS Enc, COUNT(*) FROM dbo.Employees WHERE DeptDet = @d
GROUP BY DeptDet;

The driver encrypts @d with the same key, the server finds the encrypted values that are identical to the parameter, and the result is correct.

You pay for it in three ways…

First weakness: deterministic encryption can cause a data leak. You don’t need the keys to see it. Just connect without Always Encrypted and read the table: the encrypted columns come out as raw binary.

SELECT FirstName, DeptDet, DeptRand FROM dbo.Employees;

Look at the DeptDet column: Alice, Bob, and Chloe share exactly the same blob, and David and Emma share another. Two distinct values across five rows. The adversary doesn’t know what 0x012536… means, but reads the structure: two departments, one with three people, the other with two, and who goes with whom. The DeptRand column, on the other hand, shows five all-different blobs: nothing to read.

It’s harmless on five rows; it isn’t on a real table. On a low-cardinality column (region, sex, status), the distribution of blobs can be compared to a known distribution, and frequency analysis often reconstructs the plaintext. Microsoft’s documentation puts it bluntly: an unauthorized user can guess information by examining patterns, especially when the set of possible values is small.

Second weakness: randomized encryption blocks every query. With randomized encryption, the driver adds a fresh random value to each cell before encrypting, so the same input produces a different cyphertext every time. Therefore, the leak is gone but so is the query:

DECLARE @d NVARCHAR(30) = 'Sales';
SELECT DeptRand AS Enc, COUNT(*) FROM dbo.Employees WHERE DeptRand = @d
GROUP BY DeptRand;  

Msg 33277, Level 16, State 2, Line 6
Encryption scheme mismatch for columns/variables ‘DeptRand’, ‘@d’. The
encryption scheme for the columns/variables is (encryption_type =
‘RANDOMIZED’, …) and the expression near line ‘6’ expects it to be
DETERMINISTIC, or RANDOMIZED, a BIN2 collation for string data types,
and an enclave-enabled column encryption key, or PLAINTEXT.

The server can no longer compare, since the same plaintext produces a different cyphertext on every row. We gained confidentiality and lost the query. It’s all or nothing.

Third weakness: no range, even with deterministic. Byte equality says nothing about order:

DECLARE @s INT = 55000;
SELECT FirstName FROM dbo.Employees WHERE SalaryDet < @s;

WHERE SalaryDet < @s fails even though the column is deterministic. Sorting, BETWEEN, LIKE: out of reach for Always Encrypted alone.

The verdict is clear: bare Always Encrypted means equality, or nothing (=, IN, GROUP BY, and DISTINCT supported).

MongoDB: queryability lives in a protocol

MongoDB’s Queryable Encryption makes optimal use of randomized encryption. On the server side, everything is Randomized-encrypted, as the documentation explains: the server has no knowledge of the data it processes. The encrypted view (a client connected without the keys) confirms it: even the three Sales employees come out with all-different BinData.

No frequency leak, unlike SQL Server’s deterministic encryption. With the keys, the same find returns the plaintext:

The technical mechanism behind this lies in the collection’s declaration. Each encrypted field carries a queryType, its parameters, and a distinct key (keyId), one Data Encryption Key per field, which is mandatory:

And queries with equality tests, range, and even equality on a field declared as range all work:

On the server side, MongoDB then maintains encrypted index structures, and for each query the driver generates cryptographic tokens that the server checks against those structures without ever seeing the plaintext. This is a searchable encryption scheme. Range is available in GA, with no special hardware.

The real difference comes down to one thing: the driver

On the SQL Server side, the driver’s work stays thin: it encrypts the parameters, rewrites the query, decrypts the results. Queryability itself is already carried by the cyphertext; the driver doesn’t have to handle it. On the MongoDB side, the driver carries the whole protocol: it generates the tokens checked against the encrypted indexes.

Once the collection is in place, find({ department: "Sales" }) is written like a normal query and the driver handles the encryption on its own. Each encrypted field needs its own key (Data Encryption Key). The master key must be pinned, otherwise orphaned keys return an HMAC validation failure error. And the encryption API is only available from a client created as encrypted, so not from a standard Compass connection, for example.

Summary

What if you wanted range while staying on SQL Server?

This is where secure enclaves come in. The engine delegates the computation to an enclave: a protected memory region where the data is decrypted and processed in the clear, out of reach, including from the machine’s administrator. This is what unlocks range, LIKE, sorting, and in-place encryption: inside the enclave the server no longer compares encrypted bytes, it works on the plaintext. This gain has a price. It requires compatible hardware or secure virtualization, an attestation service to deploy and maintain, and keys configured for the enclave, which noticeably increases architectural complexity compared with classic Always Encrypted.

But it also changes the nature of the trust. Queryable Encryption rests on a cryptographic guarantee: the server cannot read, it’s a mathematical property. Enclaves rest on a hardware guarantee: you trust the CPU, and its attestation, to isolate the protected region.

There remains a third path, often mentioned: homomorphic encryption (FHE), which computes directly on the cyphertext without ever decrypting it. Elegant on paper, but out of the game for database search: the computational cost is massive and response time collapses as the volume grows. So the practical choice really does play out between the two worlds described here.