"The database is encrypted" usually means the disk files are encrypted, which protects against a stolen drive or backup but does nothing against an attacker who logs in with valid credentials. Database and storage encryption comes in layers: volume and storage encryption, transparent data encryption (TDE), column or field encryption inside the database, and application-level or client-side encryption, often built on envelope encryption with a key management service. Each layer stops a different threat and costs something different in performance, query capability and operational risk. This guide helps architects, DBAs and data security engineers choose between them, configure the key hierarchy safely, avoid the outages that lost keys cause, and monitor what matters. General encryption principles are in the Encryption Guide; this page applies them to databases and storage.
Key takeaways
- Pick the layer by the threat. Storage encryption and TDE protect data at rest from media, snapshot and backup theft; only column or client-side encryption protects data from privileged database users and compromised database servers.
- Valid credentials defeat transparent encryption. Anyone the database authenticates sees decrypted data, so access control, MFA and NHI credential hygiene remain the primary defence.
- Envelope encryption is the common pattern. Data keys encrypt data; a key encryption key in a KMS or HSM encrypts the data keys, so rotation and revocation work on small keys, not terabytes.
- Losing the key is losing the data. Back up TDE certificates, monitor KMS key state and test restores, because a disabled or deleted key makes databases and backups unreadable.
- Encryption features can be turned against you. Attackers with storage write access have used legitimate customer-supplied-key encryption to hold data to ransom, so restrict who can choose keys.
The encryption layers and what each protects against
| Layer | Examples | Protects against | Does not protect against | Query impact |
|---|---|---|---|---|
| Volume, disk or storage service encryption | Cloud block and object storage encryption, dm-crypt and LUKS, BitLocker; Amazon RDS encryption at rest | Stolen or discarded media, unauthorised access to raw storage, snapshots copied outside the account (if the key stays private) | Anyone with OS, database or valid application access; a mounted file system is readable | None |
| Transparent data encryption (TDE) | SQL Server, Azure SQL and Oracle TDE; MySQL and MariaDB tablespace encryption | Theft of data files, log files and database backups; storage administrators without database access | Database users and DBAs; data in memory and in transit; SQL injection through the application | Minimal; no application change |
| Column or field encryption in the database | PostgreSQL pgcrypto; database encryption functions | Exposure of selected columns in dumps and to users without the key | Server-side key exposure: PostgreSQL warns the key and decrypted data are briefly on the server, where a system administrator could intercept them | Encrypted columns cannot be indexed or searched normally |
| Client-side or "always encrypted" column encryption | SQL Server Always Encrypted; MongoDB Client-Side Field Level Encryption and Queryable Encryption | DBAs, cloud operators and a compromised database server, because keys never reach the database engine | A compromised application or client that holds the keys | Equality only with deterministic modes; richer queries need enclaves or specialised schemes |
| Application-level envelope encryption | Encrypting fields or objects in code with data keys from a KMS (for example through the AWS Encryption SDK) | Everything below the application, including storage, backups, replicas and database administrators | A compromised application or its KMS permissions | The database stores opaque blobs; search requires separate design such as blind indexes or tokenisation |
The layers stack. A typical regulated design uses storage encryption everywhere, TDE where the database supports it, and client-side or application encryption for the handful of fields whose exposure would be most damaging, such as national ID numbers, card data and authentication secrets.
Transparent data encryption in practice
Microsoft's documentation for SQL Server TDE is a useful model of how TDE works generally. Pages are encrypted before they are written to disk and decrypted when read into memory, so applications need no change and the database does not grow. Backups of a TDE database are encrypted with the same database encryption key (DEK), and tempdb is encrypted automatically when any user database on the instance uses TDE.
- Key hierarchy: Windows DPAPI protects the service master key, which protects the database master key in
master, which protects a certificate (or an asymmetric key in an extensible key management module), which protects the DEK stored in the user database. Moving the protector into an EKM module or HSM separates the key from the server. - What is not covered: system databases (master, model, msdb), FILESTREAM data and buffer pool extension files are not encrypted by TDE, and TDE does not encrypt data in transit. Use file system encryption and connection encryption for those.
- Back up the certificate immediately: without the certificate and private key, a restored or attached database cannot be opened. SQL Server raises error 33091 when the DEK's certificate has not been backed up, and
sys.certificates.pvt_key_last_backup_datebeing NULL shows the same. Never drop the certificate from master, and keep it after disabling TDE because parts of the log may remain protected until a full backup. - Operational effects: enabling TDE runs an encryption scan of every page (SQL Server 2019 and later can suspend and resume it), makes instant file initialisation unavailable, fails if a filegroup is read-only, and starts a new encrypted transaction log while earlier log records stay unencrypted.
- Replication and availability groups: replicated data is not automatically encrypted at subscribers; enable TDE separately. For availability groups, create the master key and certificate on every secondary before creating the DEK on the primary.
Managed services follow the same idea with different controls. Amazon RDS encryption at rest uses AES-256 and covers the instance storage, logs, automated backups, read replicas and snapshots. It can only be chosen at creation: to encrypt an existing instance you snapshot it, copy the snapshot with encryption enabled and restore a new instance. You cannot change the KMS key of an encrypted instance in place, and RDS notes that Oracle and SQL Server TDE can be used in addition, each with its own keys.
Column, client-side and application encryption
When the threat includes privileged database users, the database server itself or a cloud operator, keys must stay outside the database engine.
- SQL Server Always Encrypted: a column master key held in Azure Key Vault, the Windows certificate store or an HSM protects column encryption keys, and the database stores only encrypted CEKs and key metadata. The client driver encrypts parameters and decrypts results. Microsoft positions it as separating data owners from those who manage the data, including DBAs and cloud operators.
- Deterministic vs randomised: deterministic encryption allows equality lookups, joins, grouping and indexing but leaks patterns, and low-cardinality values can be guessed from the ciphertext. Randomised encryption is stronger but supports almost no queries unless secure enclaves are used (SQL Server 2019 and later, Azure SQL Database), which add comparisons, pattern matching, sorting and in-place re-encryption.
- MongoDB: Queryable Encryption stores fields as fully randomised ciphertext while still supporting equality and range queries, with decryption only on the client; customer master keys stay in your KMS and data encryption keys sit in a key vault collection. Client-Side Field Level Encryption supports equality queries only on deterministically encrypted fields.
- PostgreSQL: the core documentation lists password hashing (SCRAM preferred, MD5 deprecated), pgcrypto column encryption, file system or block encryption, network encryption with
hostsslorhostgssencinpg_hba.conf, certificate authentication and client-side encryption. It does not list a built-in TDE feature, so at-rest protection for self-managed PostgreSQL usually means block-level encryption plus client-side encryption for sensitive fields. - Application envelope encryption: the application requests a data key from the KMS, encrypts the field or object locally, stores the encrypted data key next to the ciphertext and discards the plaintext key. To read, it asks the KMS to decrypt the data key. AWS describes the benefits as protecting data keys, encrypting the same data under multiple keys by re-wrapping only the data keys, and combining fast symmetric encryption with easier key management. AWS states that KMS keys never leave its FIPS 140-3 Security Level 3 validated HSMs unencrypted.
Where fields must be searchable without decryption, consider tokenisation or format-preserving approaches instead. See the Tokenisation and Data Masking Guide.
Choosing an approach
| Requirement or constraint | Recommended layer | Notes |
|---|---|---|
| Compliance requirement for encryption at rest, no change to applications | Storage encryption plus TDE | Cheapest to deploy; document that it does not address insider or credential threats |
| DBAs or a hosting provider must not see specific fields | Client-side column encryption (Always Encrypted, CSFLE or Queryable Encryption) or application encryption | Plan query patterns first; deterministic modes leak equality |
| Data shared across many services or copied into analytics | Application envelope encryption with per-domain keys, or tokenisation | Each consumer needs KMS permission, which becomes your access control |
| Multi-tenant SaaS | Envelope encryption with per-tenant data keys or KMS keys | Enables crypto-shredding a tenant by destroying its key; watch KMS request quotas and cost |
| Customer demands control of keys | Customer managed keys in your KMS, or external key management | See the BYOK, HYOK and External Key Management Guide |
| Small team, managed database | Provider encryption at rest with a customer managed key, connection encryption, strong authentication | Add application encryption only for the highest-risk fields |
Large organisations should standardise on a small number of patterns and a single key management service per cloud, so that key policies, rotation and audit are consistent. Smaller organisations get most of the benefit from managed encryption at rest with customer managed keys, combined with strong control of who and what can authenticate to the database.
Keys, rotation and the machine identities that use them
- Separate keys from data: store key encryption keys in a KMS or HSM, never in the database, configuration files or environment variables. The OWASP Cryptographic Storage Cheat Sheet makes the same point and recommends a KEK at least as strong as the DEK.
- KMS permission is the real access control: with application or envelope encryption, the identity allowed to call decrypt on the key can read the data. Those identities are usually service accounts, workload roles and CI pipelines. Scope key policies to named workload identities, deny decrypt to humans by default, and review them like any privileged entitlement. See the AWS KMS Security Guide and the Cryptographic Key Management Guide.
- Rotation: rotate key encryption keys on a schedule and re-wrap data keys; re-encrypt data only when a data key may be compromised. With TDE, changing the DEK triggers a re-encryption scan, so schedule it.
- Key availability: on Amazon RDS, disabling the KMS key stops the database accessing it; the instance becomes inaccessible and, if not recovered within seven days, can only be restored from a snapshot or backup. Treat key disable and deletion as production changes with approval and monitoring.
- Database credentials: because transparent encryption is invisible to authenticated users, rotate database passwords and keys used by applications, prefer short-lived or IAM-based database authentication, and never embed them in code. See the Database Credential and Access Security Guide.
Rolling out database encryption
- Classify data and threats: identify which databases hold regulated or high-impact data and which threats matter: media theft, backup exposure, insider DBA, server compromise, credential theft.
- Baseline: enable storage encryption and connection encryption everywhere (for PostgreSQL,
hostsslentries; for SQL Server, encrypted connections), using customer managed keys where audit or revocation is needed. - Enable TDE in a test environment first: measure the encryption scan time and performance, back up certificates or confirm KMS key access, and rehearse a restore to a different server.
- Roll out by risk tier: production databases holding sensitive data first, during maintenance windows, with a documented rollback (TDE can be turned off and the scan reversed; managed services may require restoring from a pre-change snapshot).
- Add field-level encryption: for the top fields, prototype client-side or application encryption against real query patterns before committing, because retrofitting deterministic or randomised choices later means re-encrypting data.
- Prove recovery: restore an encrypted backup into an isolated environment at least quarterly, including retrieving keys from escrow.
Detection and measurement
- Configuration drift: alert on unencrypted instances, snapshots and storage volumes; on TDE being disabled (SQL Server Audit with the
DATABASE_OBJECT_CHANGE_GROUPaction group records TDE changes); and on snapshots shared outside the organisation. - Key events: KMS key disable, scheduled deletion, key policy changes and grants; decrypt calls from identities outside the expected workload set or at unusual volumes.
- Misuse of encryption features: object writes using customer-supplied keys where applications do not use them, bulk overwrites and lifecycle policy changes on storage buckets.
- Data access: unusual query volume or bulk export by database accounts, since encrypted-at-rest data is returned in plaintext to authenticated sessions.
Metrics:
- Percentage of databases, snapshots and backups encrypted at rest (target 100%).
- Percentage of high-sensitivity fields protected by client-side or application encryption.
- Number of identities with decrypt permission on data keys for sensitive stores, and percentage that are named workload identities.
- Percentage of TDE certificates or protectors with a verified backup or escrow.
- Time since the last successful encrypted-backup restore test per critical database.
Framework mapping
| Framework | Reference | Relevance |
|---|---|---|
| NIST SP 800-53 Rev. 5 | SC-28 Protection of Information at Rest and SC-28(1) Cryptographic Protection; SC-12 Cryptographic Key Establishment and Management; SC-13 Cryptographic Protection; CP-9(8) backup cryptographic protection | At-rest encryption, key management, encrypted backups |
| NIST CSF 2.0 | PR.DS-01 data at rest; PR.DS-11 backups created, protected, maintained and tested | Encryption and recoverable backups |
| OWASP ASVS 5.0 | V11 Cryptography | Application-level encryption requirements |
| MITRE ATT&CK | T1530 Data from Cloud Storage; T1486 Data Encrypted for Impact | Theft from storage and ransom encryption, including abuse of storage encryption features |
| MITRE D3FEND | D3-DENCR Disk Encryption; D3-FE File Encryption | Defensive techniques for storage and file encryption |
Common failure patterns
- Treating TDE as access control: the Snowflake customer breaches used infostealer credentials on accounts without MFA, and the attackers copied tables with normal client tools. Any transparent encryption returns plaintext to an authenticated session.
- Keys stored with the data: TDE certificates and key files backed up to the same location as the database backups, so whoever steals the backup also has the key.
- Lost certificates and disabled keys: a server rebuilt without the TDE certificate backup, or a KMS key disabled during a cleanup, makes production databases and every backup unreadable.
- Encryption features turned into ransomware: in the Codefinger S3 ransomware campaign, attackers with stolen long-term AWS keys re-encrypted objects using SSE-C with a key only they held. Block customer-supplied-key encryption where it is not needed and remove long-term keys.
- Over-broad decrypt permissions: application-level encryption rolled out with a key policy that lets every developer role and CI pipeline decrypt, which recreates the exposure encryption was meant to remove.
- Deterministic encryption on low-cardinality fields: encrypting status, gender or region fields deterministically, where values can be inferred from frequency.
Practitioner checklist
- Map each sensitive data store to the threats that matter and the encryption layer that addresses each.
- Enable storage and connection encryption everywhere, with customer managed keys for regulated data.
- Enable TDE where supported, back up and escrow certificates or protectors, and alert on missing backups.
- Use client-side or application envelope encryption for the most sensitive fields, chosen against real query patterns.
- Store key encryption keys in a KMS or HSM and scope decrypt permission to named workload identities.
- Treat KMS key disable, deletion and policy changes as monitored, approved production changes.
- Block customer-supplied-key encryption on storage where no application needs it.
- Rotate and shorten the life of database credentials used by applications and pipelines.
- Test restores of encrypted backups, including key retrieval, at least quarterly.
Standards and references
- NIST: SP 800-53 Rev. 5
- Microsoft Learn: Transparent data encryption (TDE)
- Microsoft Learn: Always Encrypted
- AWS: AWS KMS cryptography essentials and envelope encryption
- AWS: Encrypting Amazon RDS resources
- PostgreSQL: Encryption Options
- MongoDB: Queryable Encryption
- OWASP: Cryptographic Storage Cheat Sheet
Related NHI Mgmt Group resources: Encryption Guide · Cryptographic Key Management Guide · AWS KMS Security Guide · Tokenisation and Data Masking Guide · Database Breach Response Playbook