Encrypting SQL Server: Options, Prerequisites, and What Existing Applications Won't Let You Do

Start With the Threat Model, Not the Feature Name

"Encrypt the database" means different things depending on who is asking, and each SQL Server feature answers a different version of the question. Before picking one, be clear on what you're actually defending against:

None of these is "the" answer. A realistic compliance posture usually needs several of them layered, not one chosen instead of the others.

Encryption at Rest: Transparent Data Encryption (TDE)

TDE encrypts the physical data and log files at the page level, transparently to every query, application, and report already pointed at the database. Nothing in the application layer has to change. It moved from Enterprise-only into Standard Edition starting with SQL Server 2019, which removed the biggest historical barrier to adopting it.

The entire operational risk lives in one place: the certificate protecting the database encryption key lives in master, not in the database itself, and a database restored elsewhere without that certificate is permanently unreadable, with no recovery path. This site already covers TDE in depth, so rather than repeat it:

The AlwaysOn case deserves a callout here specifically because it's the same recurring pattern this blog keeps coming back to: a server-scoped object doesn't travel with the database through an AG. Server logins don't (see AlwaysOn with Standard vs. Contained Databases), and neither does the TDE certificate. Add an encrypted database to an AG without creating the identical certificate on every secondary first, and that secondary simply cannot bring the database online.

Encryption in Use: Always Encrypted

TDE's blind spot is exactly what Always Encrypted (SQL Server 2016+) is built for: data that stays encrypted everywhere, on disk, in memory, on the network, and in the query plan, decrypted only inside the client driver, using a key SQL Server itself never has access to. A sysadmin running SELECT * FROM Customers sees ciphertext in the encrypted columns, not plaintext.

Two encryption types, with a real tradeoff:

• Deterministic - the same plaintext always produces the same ciphertext, which allows equality comparisons, joins, and grouping on the encrypted column, at the cost of leaking that two rows share a value (a frequency-analysis risk on low-cardinality data like a country code or a status flag).
• Randomized - the same plaintext produces different ciphertext every time, the stronger option, but the database engine can no longer compare, sort, or index on that column at all. Every such operation has to move to the client, on decrypted values.
• Secure enclaves (SQL Server 2019+) relax this by decrypting inside a protected memory region on the server itself, enabling range queries and pattern matching that plain Always Encrypted can't do, at the cost of additional infrastructure (Virtualization-based Security or SGX, depending on platform) and typically an Enterprise Edition requirement, worth confirming against the specific version's licensing terms before planning around it.

Keys: a Column Master Key (CMK) stored outside SQL Server entirely (Windows certificate store, Azure Key Vault, or an HSM), and a Column Encryption Key (CEK) stored inside the database but itself encrypted by the CMK. SQL Server only ever sees the encrypted CEK; it never sees a key capable of decrypting data.

-- The client driver does the actual encrypt/decrypt work; SQL Server only
-- stores and returns ciphertext. Requires the connection string flag:
-- "Column Encryption Setting=Enabled"

ALTER TABLE dbo.Customers
ALTER COLUMN NationalId varchar(20)
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = CEK_Customers,
    ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
);

Encrypting Backups, Independently of TDE

If a database isn't TDE-encrypted but its backups leave the building, to offsite storage, a cloud backup target, or a managed service provider, BACKUP ... WITH ENCRYPTION encrypts the backup file itself using a certificate or asymmetric key, available in Standard Edition since SQL Server 2014:

BACKUP DATABASE ProductionDB
TO DISK = 'D:\Backup\ProductionDB.bak'
WITH ENCRYPTION (
    ALGORITHM = AES_256,
    SERVER CERTIFICATE = Backup_Cert
), COMPRESSION;

The same rule as TDE's certificate applies here without exception: the certificate encrypting the backup has to be backed up and stored separately from the backups it protects, or those backups become exactly as unrecoverable as the TDE scenario. If TDE is already on, backups inherit that encryption automatically; explicit backup encryption is for the case where TDE is off, or as a deliberate second layer.

Encryption in Transit: TLS / Force Encryption

Everything above protects data sitting somewhere. None of it protects data moving between the application and SQL Server over the network, that's a separate setting entirely: Force Encryption on the instance, backed by a real certificate.

The Manual Predecessor: Cell-Level Encryption Functions

Before Always Encrypted existed, column-level encryption meant calling ENCRYPTBYCERT, ENCRYPTBYASYMKEY, ENCRYPTBYKEY, or ENCRYPTBYPASSPHRASE explicitly in T-SQL, storing the result as varbinary, and decrypting explicitly with the matching DECRYPTBY... function on every read. It still works, on every edition, and is occasionally still the right answer on an older version or an edition where Always Encrypted's driver requirements aren't met, but it comes with none of Always Encrypted's automation: the application has to know which columns are encrypted and call the right function on both write and read, the column can't be indexed, searched, or sorted at all (it's an opaque binary blob to the engine), and key management is entirely manual.

What Existing Applications Won't Let You Do

This is where an encryption project actually gets expensive, not in the SQL Server configuration itself.

Always Encrypted's real constraints

• A hard driver floor. Only client drivers built with Always Encrypted support (a sufficiently recent Microsoft.Data.SqlClient/System.Data.SqlClient, ODBC Driver, or JDBC Driver, plus the Column Encryption Setting=Enabled connection string flag) can decrypt the columns at all. An application on an old driver doesn't get an error explaining why, it just gets ciphertext back as an opaque value.
• Reporting, BI, and ETL tools need the same driver-level support and connection string flag. A tool that connects with a generic or outdated provider, which describes a lot of legacy reporting infrastructure, will show binary garbage instead of values, with no indication that's what happened.
• Feature incompatibilities that don't announce themselves until you hit one: full-text search cannot index ciphertext, computed columns and constraints referencing encrypted columns have restrictions, and Change Data Capture / Change Tracking / replication have documented limitations on encrypted columns that vary by SQL Server version, check current Microsoft documentation for the specific version before assuming a feature combination works.
• Query rewrites, not just configuration. A randomized column can't appear in a WHERE clause comparison, a JOIN predicate, an ORDER BY, or a LIKE at all; a deterministic column can be compared for equality but nothing else. Reports and queries built before encryption was added routinely need rewriting, not just a driver upgrade.

TDE's constraints are lighter, but not zero

• Backup compression effectively stops working. Encrypted data has no exploitable pattern left for a compression algorithm, so a compressed backup of a TDE-enabled database typically lands close to the uncompressed size. This is a real, immediate storage and backup-window planning cost that catches teams who tested compression ratios before enabling TDE and never re-measured after.
• The initial encryption scan touches every existing page once and can run long on a large database; it belongs in a planned maintenance window, not a Tuesday afternoon.

What's Actually Sensible

• TDE, close to by default. Low application impact, no query rewrites, genuinely effective against the "stolen disk or backup" threat, and available without an Enterprise license since SQL Server 2019. The main cost is operational discipline around the certificate, not engineering time.
• TLS / Force Encryption, always, with a real certificate. Cheap, standard practice, and "encrypted at rest but plaintext on the wire" is an incomplete story most auditors will ask about directly.
• Always Encrypted, surgically, not as a blanket policy. Pick the handful of columns that genuinely need to stay opaque even to sysadmin, national IDs, payment data, health data, and budget real engineering time for the driver upgrades and query rewrites those specific columns will require. Encrypting everything with Always Encrypted "to be safe" is usually how a project stalls.
• Backup encryption as an explicit second layer when backups leave the building to a target that shouldn't be trusted with plaintext, independent of whether TDE is already protecting the live database.

The Risks That Don't Make the Compliance Slide

• A false sense of security. "The database is encrypted" gets reported to auditors and executives as one fact when it's actually several separate, narrow facts. TDE says nothing about who can query the data; Always Encrypted on three columns says nothing about the other two hundred. Naming the specific threat each control covers, the way the table at the top of this article does, is what keeps that gap from surviving an audit until an incident finds it.
• Certificate and key rotation is a standing operational task, not a one-time setup step, for TDE, backup encryption, and Always Encrypted's CMK alike. Each needs a defined rotation cycle and a retention policy for old keys, since backups and data encrypted under a prior key still need that prior key to read.
• Performance is usually fine, capacity planning often isn't. TDE's steady-state CPU overhead is modest on modern hardware; the backup-compression loss is the cost that actually shows up on a storage bill and a backup-window schedule, and it's the one most often missed until the first post-encryption backup run.

Comparison

Questions People Actually Ask