SQL: Practical SQL Server Encryption - TDE, Always Encrypted, and When to Use Each

SQL: Practical SQL Server Encryption - TDE, Always Encrypted, and When to Use Each

Encryption in SQL Server is often discussed as a checklist item:

  • Is the database encrypted?
  • Do we need Always Encrypted?
  • What does compliance require?

What gets discussed far less often is how these features behave in real systems, what problems they actually solve, and what trade-offs they introduce.

This post looks at Transparent Data Encryption (TDE) and Always Encrypted from a practical standpoint: what each one protects, what it doesn’t, and how to decide which one belongs in your design.

The First Question to Ask: What Are You Protecting Against?

Before choosing an encryption feature, you need to be clear about the threat model.

Broadly, SQL Server encryption addresses three different risks:

  • Physical access to storage
  • Lost disks, stolen backups, compromised snapshots
  • Unauthorized access to database files
  • Copying MDF/LDF or backup files to another server
  • Privileged access inside SQL Server
  • DBAs, sysadmins, or attackers with database access

TDE and Always Encrypted target different risks. Treating them as interchangeable leads to disappointment.

Transparent Data Encryption (TDE)

TDE encrypts:

  • Data files (MDF/NDF)
  • Transaction logs
  • Backups (encrypted pages are backed up - and this is separate to backup encryption)

Encryption happens:

  • At rest
  • At the page level
  • Transparently to applications

From an operational perspective, TDE is simple:

  • No application changes
  • No schema changes
  • No query changes

Once enabled, SQL Server handles the rest.

What TDE Does Not Do

TDE does not:

  • Encrypt data in memory
  • Encrypt data over the network
  • Hide data from DBAs or sysadmins
  • Prevent queries from seeing plaintext values

Anyone who can run a SELECT can still read the data. That’s not a flaw — it’s by design.

When TDE Is the Right Choice

TDE is a good fit when:

  • You need to protect backups and data files
  • You want minimal application impact
  • Compliance requires encryption at rest
  • The main risk is lost or stolen storage

In many environments, TDE is the correct default.

If you want to learn more about TDE, I have a free eBook you can download here

Always Encrypted

Always Encrypted is fundamentally different. It ensures that:

  • Sensitive data is encrypted before it reaches SQL Server
  • SQL Server never sees the plaintext
  • Encryption keys are stored outside SQL Server

This protects against:

  • Curious DBAs
  • Compromised SQL Server instances
  • Certain classes of insider threat
  • Access by hosting organization staff

From a security perspective, this is a major shift.

Always Encrypted introduces real constraints:

  • Limited query patterns
  • Restricted operators and functions
  • Indexing limitations
  • Application driver requirements
  • More complex key management

Some operations move from the database engine to the client. This is not a transparent form of encryption.

Deterministic vs Randomized Encryption

Always Encrypted offers two modes:

Deterministic

  • Allows equality comparisons and joins
  • Leaks equality patterns

Randomized

  • Stronger security
  • No comparisons or joins

Choosing between them is a design decision, not a checkbox.

When Always Encrypted Is the Right Choice

Always Encrypted makes sense when:

  • DBAs must not see certain data
  • Regulatory or contractual requirements demand it
  • The application can tolerate query limitations
  • You control the client drivers and code
  • You need to protect application data from hosting organization staff

It is not a general-purpose encryption solution.

Comparing TDE and Always Encrypted

Aspect TDE Always Encrypted
Protects data at rest ✔ ✔
Protects backups ✔ ✔
Protects from DBAs ✖ ✔
Application changes needed ✖ ✔
Query limitations ✖ ✔
Transparent to SQL ✔ ✖

They solve different problems.

Using Both Together

TDE and Always Encrypted are not mutually exclusive. A common and sensible pattern is:

  • TDE for full database and backup protection
  • Always Encrypted for a small number of highly sensitive columns

This layered approach reduces risk, limits complexity, and avoids over-encrypting everything.

Common Misconceptions

TDE encrypts my data from everyone

It doesn’t. It encrypts storage, not access.

Always Encrypted is more secure, so we should use it everywhere

Security is not binary. Overuse can cripple performance and usability.

Compliance requires Always Encrypted

Most compliance frameworks only require encryption at rest, which TDE satisfies.

Final Thoughts

Encryption features are tools:

  • TDE is about operational safety.
  • Always Encrypted is about trust boundaries.

Using the right one - or both - depends on understanding what you’re protecting against, not on ticking a security box.

Good encryption design is not about encrypting everything. It’s about encrypting the right things, in the right place, for the right reason.

Learn more about SQL Server Encryption

If you really want to learn about SQL Server encryption and how to protect your organization’s secrets right now, we have an online on-demand course that you can enrol in, right now. You’ll find it at SQL Server Encryption for Developers and DBAs

2026-02-01