Field-level encryption with AES-256-GCM and per-tenant derived keys

The “encryption at rest” your database offers protects against exactly one scenario: someone walks off with the disk. It does not help if the attacker reaches the database through the application or with a valid account — because then the engine politely decrypts everything for them.

Field-level encryption is the answer for data that genuinely matters: a few columns, encrypted in the application, with keys the database never sees.

When is it worth it?

Not for everything. Encrypting a field effectively removes it from the database’s search engine: no more LIKE, no ordering, no useful indexing. So you choose deliberately:

• national identifiers, health data, banking details;
• fields you read rarely and search even more rarely;
• data with an explicit requirement behind it (contractual or regulatory).

The practical rule: if you need to search it often, you probably do not want field-level encryption — or you need a blind index, covered below.

Why AES-256-GCM?

Because it is an authenticated cipher (AEAD): it gives you confidentiality and a guarantee that the ciphertext was not tampered with. With an unauthenticated mode such as plain CBC, an attacker can alter data without you noticing — and you “successfully” decrypt garbage.

Alongside the ciphertext, GCM produces an authentication tag. Change a single bit and decryption fails. That is exactly the behaviour you want.

The format I store, in a single binary column:

[1B alg id][1B key version][12B nonce][ciphertext][16B tag]

Two details that pay off later: the algorithm id lets you migrate to a different cipher without a flag day, and the key version makes rotation possible at all. Both go into the AAD as well (below), so neither can be tampered with.

How do you derive a key per tenant?

A single key for all clients means one compromise exposes all of them. The solution: a master key kept in a vault (Azure Key Vault or equivalent) and, from it, a derived key per tenant using HKDF-SHA256:

// info binds the derived key to the tenant and to its purpose
var key = HKDF.DeriveKey(
    HashAlgorithmName.SHA256,
    ikm: masterKey,
    outputLength: 32,
    salt: tenantSalt,
    info: Encoding.UTF8.GetBytes($"field-encryption:v1:{tenantId}"));

The benefits: you store one key rather than N; you can deterministically reconstruct any tenant’s key; and compromising one client’s derived key reveals nothing about the others.

The master key should leave the vault as little as strictly necessary, and derived keys live only in memory.

What is critical about the nonce?

This is where mistakes are fatal rather than merely unpleasant.

A nonce must never be used twice with the same key. With GCM, reuse does not merely weaken encryption — it enables recovery of the material needed to forge authentication tags. It is a total break, not a degradation.

Practical rules:

• 12 bytes (96 bits) is the size to use — and the only size .NET’s AesGcm accepts;
• store it next to the ciphertext — it is not secret, only unique;
• a counter is the stronger construction if you can guarantee it never repeats across processes, restarts and replicas. In a horizontally scaled SaaS that is genuinely hard, so a cryptographic RNG is the pragmatic default;
• with random 96-bit nonces the safe limit is a published number, not a feeling: NIST SP 800-38D §8.3 caps the random construction at 2^32 encryptions per key. Count encryptions per key version and rotate before you approach it — a background re-encryption sweep on a large table, on top of normal traffic, gets there faster than you would guess.

What is additional authenticated data (AAD)?

A subtle and very useful detail. GCM lets you bind the ciphertext to a context that is not encrypted but is authenticated.

If you put the tenant identifier, the field name and the row key into the AAD, then a ciphertext can no longer be moved from its row to another, or from one client to another. Decryption will fail, because the context no longer matches.

Without AAD, someone with write access to the database can copy an encrypted field from one row into another and produce a valid-looking change. With AAD, they cannot.

Know the boundary, though: AAD gives you context binding, not temporal integrity. An attacker with write access can still restore an older ciphertext of the same cell — same tenant, same field, same row, so the AAD matches perfectly — or simply null the column. Rolling a status or a balance back to a previous valid value is often the attack that actually matters. Defending against that needs row versioning or an audit trail, which is a separate mechanism.

One practical gotcha: if the row key is database-generated, you do not have it at encryption time. Either generate keys client-side (UUIDv7 keeps index locality) or write in two phases.

How do you search encrypted data?

You cannot — not directly. You have three options, in order of preference:

Approach	What it allows	What it leaks
Do not search	Reads by primary key	Nothing
Blind index (deterministic HMAC of the normalised value)	Exact-equality lookup	That two rows hold the same value
Deterministic encryption	Equality + grouping	More than a blind index; avoid

The blind index is the usual compromise: keep the field encrypted with GCM for reading and, separately, a column holding HMAC(index_key, normalised_value) that you index. You search by HMAC, not by value.

Derive the index key per tenant, exactly like the encryption key — info: "blind-index:v1:{tenantId}:{fieldName}". This is the detail that is easiest to get wrong and most expensive to get wrong: with a single global index key, the same national ID hashes to the same value in every customer’s data, so anyone who reaches the database can correlate a person across your whole customer base. That is precisely the linkage the per-tenant encryption keys exist to prevent. The cost is that cross-tenant equality search becomes impossible — which, in a multi-tenant product, is the correct outcome.

Two more things to accept knowingly:

• a blind index reveals equality patterns within a tenant. For fields with few distinct values, that can be enough to infer content; truncating the index deliberately induces collisions and blunts frequency analysis, at the cost of a post-filter decrypt pass;
• the normalisation must be canonical and frozen (Unicode form, case folding, trimming). Change it later and every existing index entry is silently orphaned.

How do you rotate keys?

First, be precise about what you are rotating — the two cases are not the same:

What you rotate	How	What it actually buys you
Derived key (bump info to ...:v2:...)	Re-derive from the same master	A fresh nonce budget, a clean break after a leaked derived key
Master key (new key in the vault)	Re-derive everything from the new master	Recovery from a compromised master — the only rotation that helps there

Bumping the version string re-derives from the same master key. That is genuinely useful — it resets your 2^32 counter and contains a leaked tenant key — but if the master itself leaks, every version derived from it falls with it. Design for both from the start: keep a master generation identifier alongside the key version, and make sure your key resolver can hold two masters at once during a transition.

The mechanics, either way:

1. bump the version (derived) or provision a new master generation (master);
2. always write with the current version;
3. on read, resolve the key from the stored prefix — so old and new coexist;
4. re-encrypt gradually, in the background or on first touch of the row;
5. only retire the old key once a query confirms zero rows still carry its version.

Rotation then costs performance, not availability — provided you put the version there on day one. Watch step 4 in particular: a background sweep is itself a large batch of encryptions, so count it against the nonce budget of the new key.

The mistakes that matter

1. Reused nonce. The only one on this list that is catastrophic.
2. Ignoring the nonce budget — random nonces are safe until 2^32 per key, and a re-encryption sweep burns through that faster than daily traffic.
3. Unauthenticated mode (CBC without a MAC) — undetectable tampering.
4. One key for all tenants — a single compromise takes them all.
5. A global blind-index key — the per-tenant encryption keys stop mattering, because the index correlates people across every customer anyway.
6. No key version — you are stuck when you need to rotate.
7. No AAD — ciphertexts become movable between rows.
8. Encrypting everything — you lose search and gain very little.

If you are working on a product where a few fields genuinely need protection and want a review of the key scheme before it reaches production, that is a conversation I am glad to have.