Encryption at Rest: TDE, Backup Encryption, Always Encrypted, and the Keys Behind Them

Encryption at rest answers a specific question: if someone gets your files, can they read them? A stolen backup, a decommissioned disk, a copied data file — without encryption, each is a full copy of your database in the wrong hands. With it, they're noise. This is part three of the series — part one covered authentication and network, part two covered auditing and threat detection.

One theme runs through all of this: the encryption is the easy part. The keys are the whole game. Lose control of them and the data is exposed; lose the keys themselves and the data is gone. Plan the keys first.

Transparent Data Encryption (TDE)

TDE encrypts the database's data and log files — and the backups taken from it, and tempdb — at rest, transparently to the application. No query changes, no application changes; the engine encrypts pages on the way to disk and decrypts them on the way back into memory. It's the standard answer to "protect the data files and backups if the media is lost or stolen."

Underneath, it's a key hierarchy: a database encryption key (DEK) does the actual encryption, and it's protected by a certificate in the master database, which is in turn protected by the database master key and the instance's service master key. You mostly don't touch the machinery — but you must respect one rule above all others.

Back up the TDE certificate and its private key, and store them separately from the database. Without that certificate you cannot restore the backup or attach the files — anywhere, ever. Microsoft's own documentation flags this in a caution: data loss results if the certificate is no longer available. The same feature that protects your data from a thief will lock you out just as completely if the certificate is lost. And know TDE's limits: it protects data at rest only — not data in transit, and not a privileged user who can simply query the tables.

Backup encryption

Backups are the copies most likely to leave the building — offsite, to the cloud, onto tape or object storage. SQL Server can encrypt the backup itself using a certificate or asymmetric key, independent of whether the source database uses TDE. If your databases aren't running TDE but your backups travel, backup encryption closes that gap on its own. The custody rule is identical: whatever key or certificate encrypted the backup must be backed up and guarded separately, because it's the only thing that can restore it.

Always Encrypted — shielding data even from the DBA

TDE and backup encryption protect data from someone who gets the files. They do nothing against someone with legitimate query access — including the DBA. Always Encrypted is the feature for when specific columns must be hidden even from the people who administer the database.

The trick is where the encryption happens: in the client driver, not the server. The application encrypts sensitive values before they're sent, and SQL Server stores and returns only ciphertext — it never sees the plaintext or holds the keys to decrypt it. Two keys make this work: a column encryption key (CEK) encrypts the data, and a column master key (CMK) protects the CEK and lives outside the database — in the Windows certificate store, an HSM, or Azure Key Vault. Because the server never has the CMK, the database only ever holds encrypted values and metadata; a compromised server, or a curious administrator, still can't read the protected columns.

• Deterministic encryption always produces the same ciphertext for the same value, so you can do equality lookups and joins — at the cost of revealing patterns (equal values look equal).
• Randomized encryption is more secure but can't be searched or joined on directly.
• Secure enclaves extend what's possible — pattern matching, range comparisons, and in-place encryption — while keeping the keys protected inside a trusted region of memory.

Use it surgically, on the columns that truly demand separation from administrators — national IDs, card numbers, credentials — not as a blanket over the whole database. It's a scalpel, not TDE's blanket.

The key hierarchy behind all of it

Every option above comes down to a chain of keys: the service master key protects the database master key, which protects the certificates and asymmetric keys, which protect the DEKs and CEKs that encrypt the data. Whoever controls the top of that chain ultimately controls access to the data. The real work of encryption at rest isn't turning it on — it's rotation, backup, and custody: rotating keys on a schedule, backing them up so you can always recover, and deciding who holds them. Instance-local certificates are the simplest path, but they tie the keys to the box and put custody entirely on you.

Advanced: external key management (EKM) with Azure Key Vault

When keys shouldn't live on the database server at all, Extensible Key Management (EKM) lets SQL Server hand key storage to an external system — a hardware security module or a cloud key vault. The SQL Server Connector for Azure Key Vault implements EKM so that TDE, backup encryption, and Always Encrypted master keys can be protected by HSM-backed, customer-managed keys held in Azure Key Vault instead of certificates on the instance.

The payoff is separation of duties and control. The keys live in a vault with its own access policies, its own audit log, and centralized rotation — so the DBA can run the database without ever holding the keys, and the security team can grant, rotate, and revoke access independent of whoever administers SQL Server. In Azure SQL, "TDE with a customer-managed key in Key Vault" is the fully managed version of exactly this pattern (BYOK). EKM works for SQL Server on-premises and on Azure VMs. The tradeoff is a new dependency: the database now needs to reach the vault, so the vault's availability, your key backups, and your outage behavior become part of the design.

Picking a key-management provider

Choosing where the keys live is as much a governance decision as a technical one. Weigh these:

• Where you already operate. Azure Key Vault if you're on Azure; AWS KMS or CloudHSM on AWS; an on-premises HSM (Thales, Entrust) if you're in your own datacenter or must keep keys physically in-house. Match the key store to where the data and the team already are.
• Compliance and residency. FIPS 140-2 Level 2 vs. 3, key-residency requirements, and who is legally permitted to hold the keys often narrow the field before anything else does.
• The availability dependency you're accepting. An external key store means the database's availability now depends on reaching it. Design for the key store's HA, keep verified key backups, and know the failure mode — what happens to a running database, and to a restart, if the vault is briefly unreachable.
• Cost and operations. A managed vault and a dedicated HSM have very different price and effort profiles — per-operation costs, rotation workflow, and disaster recovery for the keys themselves.
• Control vs. simplicity. Instance-local certificates are the least to manage but put custody on the box. An external provider buys separation of duties and central control at the price of a dependency you have to run.

The question that usually settles it: who must be able to control and revoke access to the keys, independent of whoever administers the database? Answer that honestly and the provider mostly chooses itself.

The series: Part 1 — Authentication, Network & Data Protection  ·  Part 2 — Auditing & Threat Detection

If you'd rather have a senior DBA plan and implement encryption and key management on your environment, that's what I do. See SQL Server DBA services, review real client results, or get in touch.

---