Always Encrypted in Azure: Protecting Data From the People Who Manage It

Here’s a question that makes a lot of DBAs uncomfortable when they really sit with it: if someone compromised my own admin account, or a well-meaning but overly curious teammate had sysadmin, could they just… run a SELECT and see every customer’s Social Security number?

For most databases, the honest answer is yes. Transparent Data Encryption (TDE) protects your data if someone steals the physical disk or a backup file, but once SQL Server loads that data into memory to actually run a query, it’s sitting there in plain text — and anyone with sufficient permissions on the server can read it. TDE was never designed to protect data from the database engine or its administrators. It was designed to protect data at rest.

Always Encrypted is Microsoft’s answer to the harder version of that problem: protecting sensitive data even from people who legitimately administer the database. It’s a genuinely different security model, not just “TDE but stronger,” and understanding exactly where it fits — and where its very real trade-offs live — is what this article is for.

What Always Encrypted Actually Does

The core idea is almost architecturally rebellious for a database feature: the database engine never sees the plaintext value. Encryption and decryption happen entirely on the client side, inside the application or the driver it’s using, before the data ever leaves for the server and after it arrives back. SQL Server just stores and retrieves encrypted bytes — it has no ability to decrypt them, because it never holds the key needed to do so.

That’s the fundamental difference from TDE, worth sitting with for a second:

	TDE	Always Encrypted
What it protects against	Someone stealing physical disks, backup files, or database files	Someone with database-level access (DBAs, cloud operators, attackers with a compromised admin login) reading sensitive columns
Where decryption happens	Inside the database engine, transparently	On the client, before/after the data reaches the server
Can a DBA read protected data?	Yes — once loaded into memory, it’s plaintext to anyone querying it	No — not without the encryption keys, which the DBA typically doesn’t have
Application changes required	None — fully transparent	Yes — client driver support and some query pattern adjustments
Granularity	Whole database	Specific columns you choose

If TDE is a locked warehouse, Always Encrypted is a locked briefcase that only the recipient has the key to — even the warehouse manager can see the briefcase sitting on the shelf, but they can’t open it.

How It Actually Works: Two Keys, Two Encryption Types

Always Encrypted relies on two kinds of keys working together, and getting this relationship straight makes everything else click:

• Column Master Key (CMK) — the “key that protects the key.” This lives outside SQL Server entirely — typically in Azure Key Vault, or a certificate store, or a hardware security module. SQL Server never sees this key at all, only a reference to where it lives.
• Column Encryption Key (CEK) — the actual key used to encrypt your data, but it’s itself encrypted by the CMK, and the encrypted version is what’s stored in the database. SQL Server stores the encrypted CEK, but can’t do anything useful with it without the CMK, which it doesn’t have access to.

When your application connects, the driver (with appropriate permissions) fetches the CMK from Key Vault, uses it to decrypt the CEK, and then uses the CEK to encrypt outgoing parameters or decrypt incoming results — all inside the application process, invisible to SQL Server.

You also choose, per column, between two encryption types, and this choice matters a lot in practice:

• Deterministic encryption — the same plaintext value always produces the same encrypted value. This means you can do equality comparisons (WHERE SSN = @ssn), joins, and grouping on the column — but it also leaks a pattern (an attacker who sees the same ciphertext repeated knows two rows share the same underlying value, even without knowing what that value is).
• Randomized encryption — the same plaintext produces a different ciphertext every time. Much stronger against pattern analysis, but you lose the ability to search, join, group, or index on that column using standard queries.

Practical rule of thumb: use deterministic encryption on columns you need to look up by (like a national ID number used in WHERE clauses), and randomized encryption on columns that are purely stored and displayed, never searched (like a free-text medical note).

Secure Enclaves: The Feature That Changes the Trade-Off

Remember the two encryption types from earlier — deterministic and randomized? Randomized is the safer option, but it used to come with a big downside: you basically couldn’t search, sort, or filter on that column anymore. That made it hard to actually use in real applications. Secure enclaves fix this problem.

So what is a secure enclave? Think of it as a small, locked room inside the server’s memory. Data can go into this room and be temporarily unlocked (decrypted) so the server can work with it — but nobody outside the room can see what’s happening inside. Not the DBA, not a hacker, not even other parts of SQL Server itself. Once the work is done, the data goes back to being locked.

This “locked room” is backed by real hardware protection — either Intel SGX or Virtualization-based Security (VBS), depending on the setup.

Because of this, you get the best of both worlds: you keep the stronger randomized encryption, but you can now do things that were impossible before, like:

• Searching with LIKE
• Comparing ranges (e.g., “find all values between X and Y”)
• Sorting
• Grouping
• Joining tables

It also lets you encrypt a column on an existing large table directly, without the old painful process of exporting all the data, encrypting it outside the database, and importing it back in.

One thing to plan for: if you’re using Azure SQL Database and want Intel SGX enclaves specifically, your database needs to be on the vCore model with DC-series hardware. Not every pricing tier supports this, and availability depends on your region — so check this ahead of time rather than assuming it’ll just work.

When Do You Actually Need Always Encrypted?

This is the question that matters more than any feature detail, because Always Encrypted is genuinely more work to implement than most encryption features, and it’s not the right tool for every “we should encrypt this” conversation.

Reach for Always Encrypted when:

• You need to protect specific columns from people who otherwise have full database access — DBAs, cloud platform operators, or anyone with a compromised admin credential. This is the scenario it was built for, and nothing else in the SQL Server security toolkit does this.
• You’re storing regulated data with strict “need to know” requirements — Social Security numbers, national ID numbers, credit card numbers, health record identifiers — where compliance frameworks (HIPAA, PCI-DSS, GDPR) specifically care about limiting who within your own organization can view the raw value, not just protecting against external theft.
• Your organization has a genuine separation-of-duties requirement — for example, a cloud-hosted database managed by a third-party operations team who should be able to keep the database running, patched, and backed up, without ever being able to read the sensitive columns inside it.
• You want to reduce your own liability in the event of a breach — if an attacker compromises your database server entirely but the sensitive columns are Always Encrypted and the keys live in a separately secured Key Vault, the stolen data is useless to them.

You probably don’t need it when:

• Your actual concern is data at rest (stolen disks, stolen backups) — that’s TDE’s job, and TDE is transparent with essentially no application changes.
• Your concern is masking data from lower-privileged application users while DBAs still legitimately need full visibility for support and troubleshooting — that’s what Dynamic Data Masking is for, and it’s dramatically simpler to implement.
• Your concern is restricting which rows a user can see, not encrypting specific columns — that’s Row-Level Security, a completely different tool.
• You need to run complex analytical queries, full-text search, or reporting across a column, and you can’t justify the query limitations or secure enclave setup — this is a real, legitimate reason to look elsewhere, or to encrypt a narrower set of columns than you initially planned.

A useful gut-check question: “If I gave a brand-new DBA full sysadmin rights on this database tomorrow, is there a column I’d genuinely not want them to be able to read?” If yes, that column is an Always Encrypted candidate. If the honest answer is “no, DBAs should reasonably be able to see this,” you’re probably looking at masking or RLS instead.

A Real-World Example

Picture a healthcare SaaS company storing patient records for multiple hospital clients in a shared Azure SQL Database. Their compliance team has a specific, non-negotiable requirement: the company’s own internal database administrators — who legitimately need full access to keep the system running, tune performance, and troubleshoot issues — must never be able to view patients’ Social Security numbers or specific diagnosis codes, even though they can see everything else about the record (appointment times, provider names, billing status).

They implement Always Encrypted on exactly two columns: SSN (deterministic, since it’s occasionally looked up by exact match during identity verification) and DiagnosisNotes (randomized, since it’s only ever displayed, never searched). The Column Master Key lives in Azure Key Vault, access-controlled so that only the clinical application’s managed identity — not the DBA team’s accounts — can retrieve it. When a DBA runs SELECT * FROM Patients during a troubleshooting session, those two columns come back as unreadable encrypted binary. The clinical application, connecting with the right driver and Key Vault access, decrypts them seamlessly for authorized clinicians. Compliance audit: satisfied. DBA team: still fully capable of doing their job on every other column.

A second, quieter example: a fintech company encrypts customers’ bank account numbers with Always Encrypted specifically so that if their Azure SQL Database were ever compromised through a misconfigured firewall rule or a leaked admin credential, the account numbers themselves would still be useless to whoever got in — the actual decryption keys live in a separately access-controlled Key Vault that the compromised database credential has no path to.

Setting It Up: The Short Version

The full setup has more moving parts than most encryption features, but the shape of it looks like this:

1. Create the keys (via SSMS wizard, PowerShell, or T-SQL/PowerShell together — you can’t create the CMK/CEK metadata purely in T-SQL without a client-side tool involved, since the actual cryptographic key generation happens outside the engine):

Using the SSMS wizard: right-click your database → Tasks → Encrypt Columns → this launches the Always Encrypted wizard, which walks you through selecting columns, choosing deterministic vs. randomized per column, and creating (or selecting an existing) Column Master Key — pointing it at Azure Key Vault is the recommended production pattern.

Using PowerShell (for automation/repeatable deployment):

# Requires the SqlServer PowerShell module
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings `
  -KeyURL "https://mycompanykeyvault.vault.azure.net/keys/AlwaysEncryptedCMK/abcd1234"

New-SqlColumnMasterKey -Name "CMK_Patients" `
  -InputObject $database -ColumnMasterKeySettings $cmkSettings

New-SqlColumnEncryptionKey -Name "CEK_Patients" `
  -InputObject $database -ColumnMasterKey "CMK_Patients"

2. Encrypt the target columns, either through the wizard or, if using secure enclaves for in-place encryption, directly via T-SQL:

ALTER TABLE Patients
ALTER COLUMN SSN varchar(11)
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = CEK_Patients,
    ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) WITH (ONLINE = ON);

3. Configure your application connection string to enable Always Encrypted and grant the client’s identity access to the Key Vault CMK:

Server=tcp:myserver.database.windows.net;Database=PatientsDB;
Column Encryption Setting=Enabled;Authentication=Active Directory Managed Identity;

That last line matters — using a managed identity for the application’s Key Vault access (rather than a stored secret) keeps you consistent with the same “no stored credentials” pattern that shows up everywhere else in a well-run Azure environment.

4. Confirm your client driver supports it. This isn’t automatic for every driver or tool — you need a version of ADO.NET, JDBC, ODBC, or the relevant driver that explicitly supports Always Encrypted, and older application code sometimes needs updates to work correctly against encrypted columns (especially around parameterized queries, which Always Encrypted requires for writes).

Now, the Real Question: Does It Hurt Application Performance?

Yes — but the honest, useful answer is “it depends on what you encrypt and how you query it,” not a flat percentage. Let’s break down where the cost actually shows up.

Where the overhead genuinely exists:

• CPU cost of encryption/decryption on the client. Every value going into or coming out of an encrypted column has to be encrypted or decrypted by the application/driver. For typical OLTP row-by-row access, this is usually small and unnoticeable. For queries returning huge result sets full of encrypted columns, the cumulative client-side CPU cost is real and measurable.
• Query plan limitations without secure enclaves. Without secure enclaves, randomized-encrypted columns can’t be used in WHERE, ORDER BY, GROUP BY, or joins at all — any attempt forces you to pull data client-side and filter there, which can be dramatically slower than letting the engine do it, especially on large tables. This isn’t a small performance tax; it can be an entirely different (and much worse) execution strategy if your schema wasn’t designed with this limitation in mind.
• Key retrieval and caching. The first time a client needs a CMK, it makes a call out to Key Vault, which has real network latency. Drivers cache this after the first use, so it’s a one-time cost per connection lifecycle rather than a per-query tax — but it’s worth knowing about if you’re troubleshooting a slow first query after a new connection opens.
• Index behavior on deterministic columns. Deterministic encryption supports equality-based indexing reasonably well, but the index itself is operating on encrypted values — it works, but it’s not quite the same cost profile as an index on a plaintext column.
• Secure enclave overhead. Enclave-based confidential queries (pattern matching, ranges, sorting on randomized columns) genuinely cost more server-side CPU than an equivalent plaintext query, because the engine is doing real cryptographic work inside the enclave for every row touched. It’s dramatically better than “impossible,” which was the prior state, but it’s not free.

Where it turns out to matter less than people expect:

• If you only encrypt a small number of genuinely sensitive columns (not the whole table) and design queries to filter on non-encrypted columns where possible, the practical impact on most OLTP workloads is modest.
• Deterministic encryption with equality lookups (the classic “find this customer by SSN” pattern) performs reasonably well — it’s specifically the case Always Encrypted was designed to handle efficiently.
• For most line-of-business applications — the kind doing normal transactional reads and writes rather than heavy analytical scans — the overhead is generally described as acceptable relative to the security benefit, which is exactly why it’s a recommended pattern for regulated columns rather than a niche curiosity.

Where it can genuinely hurt if you’re not careful:

• Encrypting a column you frequently filter, sort, or join on, using randomized encryption, without secure enclaves — this is the classic mistake that turns a fast indexed query into a full client-side table scan.
• Encrypting far more columns than you actually need to, “just to be safe” — every additional encrypted column is additional client-side crypto work and additional query-pattern restrictions, so the discipline of encrypting only what genuinely needs this level of protection matters a lot.
• Reporting or analytics workloads that need to aggregate, sort, or search broadly across an encrypted column without enclaves — this is a real architectural mismatch, and the honest fix is usually either enabling secure enclaves, decrypting into a separate reporting store with different access controls, or reconsidering whether Always Encrypted is the right tool for that particular column.

The practical verdict: for a handful of genuinely sensitive columns, queried the way Always Encrypted expects (equality lookups on deterministic columns, or enclave-backed queries on randomized ones), most teams find the performance cost real but manageable — a reasonable price for keeping DBAs and attackers alike out of your most sensitive data. For broad, unrestricted encryption applied carelessly across many columns with heavy ad-hoc querying, the cost can be significant enough to change your architecture. Test with your actual query patterns and realistic data volumes before committing — this is not a feature where “it should be fine” is a substitute for measuring it.

Best Practices

• Encrypt the smallest set of columns that actually need this protection — resist the urge to encrypt an entire table “for safety.”
• Use deterministic encryption only where you genuinely need to search/join on the column; default to randomized otherwise.
• Store the Column Master Key in Azure Key Vault, not a local certificate store, for production workloads — it’s centrally auditable and integrates with managed identity.
• Use managed identity for your application’s Key Vault access rather than a stored secret, consistent with the “no stored credentials” pattern that should run through your whole Azure environment.
• If your query patterns genuinely need range comparisons, pattern matching, or sorting on encrypted columns, plan for secure enclaves from the start rather than discovering the limitation in production.
• Test realistic query patterns and data volumes before rolling this out broadly — the performance story is workload-dependent, not a fixed number you can look up.
• Keep a clear list of who can access the CMK versus who can access the database — the entire value of this feature evaporates if the same people end up with both.

Key Takeaways

• Always Encrypted protects sensitive columns from anyone with database-level access, including your own DBAs — a fundamentally different threat model than TDE, which only protects data at rest.
• It works by keeping encryption keys entirely outside the database engine’s reach, with encryption/decryption happening client-side.
• Deterministic encryption supports searching/joining but leaks value-equality patterns; randomized encryption is stronger but historically far more query-restricted — until secure enclaves closed much of that gap.
• Reach for it when you have a genuine “protect this from privileged insiders” requirement — not as a general-purpose replacement for TDE, masking, or row-level security, which solve different problems.
• Yes, it affects performance — mainly through client-side crypto overhead and query-pattern restrictions on non-enclave randomized columns — but for a well-scoped set of sensitive columns queried sensibly, most teams find the cost acceptable relative to what it protects. The only way to know for your application is to test it with your real query patterns before committing.