Recommend a Solution for Data Protection (Relational) — Lesson

Data protection for Azure's relational platforms — Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs — is where many AZ-305 candidates lose easy marks. The exam rarely asks "is encryption turned on?" — it asks which encryption primitive, combined with which masking or isolation control, satisfies a set of regulatory, operational, and performance constraints at the lowest total cost. Getting this right means knowing which layer of the stack each feature protects: physical media, in-flight traffic, in-memory data, or the human operator looking over the DBA's shoulder.

This lesson walks through the seven controls the exam expects you to recommend confidently: Transparent Data Encryption (TDE), Always Encrypted (deterministic and randomized), Dynamic Data Masking (DDM), Row-Level Security (RLS), SQL auditing, Microsoft Defender for SQL, and network isolation via private endpoints, service endpoints, and server firewall rules. For each, you will learn what it protects, what it does NOT protect, how it composes with the others, and the decision points that distinguish a correct AZ-305 answer from a distractor. The lesson deliberately excludes backup/restore design (covered under LO24) and high-availability topology (LO27) — data protection here means confidentiality, integrity, and attribution, not durability or uptime.

Why This Matters

A mis-designed data-protection story is the most expensive mistake an Azure architect can ship. The fine for a single exposed column of PII under GDPR can exceed 4% of global annual revenue; in one widely-cited 2023 Azure postmortem, a customer left Dynamic Data Masking enabled on a reporting replica but forgot that DDM is a presentation-layer control — a developer with UNMASK permission exported a CSV and the breach notification followed two weeks later. You do not want to be the architect who confused "masked" with "encrypted." AZ-305 deliberately plants this confusion in its multiple-choice distractors.

This matters for your certification score and for your career. Every landing-zone review at every large enterprise includes a relational-data-protection sub-checklist, and the architect who can distinguish Always Encrypted with randomized columns (no range scans, no equality joins, client-driven key management) from TDE with a customer-managed key in Azure Key Vault (server-side, transparent to apps, protects the .mdf file but NOT a live query) is the architect who leads that review. The Well-Architected Framework's Security pillar treats data classification as the starting point — everything else, from SKU choice to network topology, follows from how sensitive the data is and who must not see it.

Prerequisites

Before this lesson, you should already be able to:

• Describe the Azure SQL product family — Azure SQL Database (PaaS, single DB / elastic pool), Azure SQL Managed Instance (near-100% SQL Server surface), SQL Server on Azure VMs (IaaS). Self-check: which of the three supports server-level SQL Agent jobs out of the box?
• Explain Azure Key Vault (AKV) and Managed HSM, including the difference between software-protected and HSM-protected keys. Self-check: which FIPS level does Managed HSM meet?
• Recognise Microsoft Entra ID (formerly Azure AD) authentication for Azure SQL and what a managed identity is. Self-check: can a managed identity be used to authenticate to Azure SQL Database from an App Service?
• Read an Azure resource hierarchy (management group → subscription → resource group → resource) and map role assignments onto it. Self-check: which scope does the SQL Security Manager built-in role target?
• Interpret a basic VNet diagram with subnets, NSGs, service endpoints, and private endpoints. Self-check: does a private endpoint deploy into the PaaS tenant or into the customer VNet?

Learning Objectives

By the end of this lesson, you will be able to:

1. Analyse a relational-data-protection requirement and classify it into one of four protection surfaces: at-rest, in-flight, in-use, or presentation.
2. Recommend between TDE with service-managed keys, TDE with customer-managed keys (BYOK), and Always Encrypted given regulatory, operational, and performance constraints.
3. Design a column-level encryption strategy using Always Encrypted, correctly choosing between deterministic and randomized encryption types per column.
4. Evaluate when Dynamic Data Masking is sufficient and when it is not, and pair it with Row-Level Security to enforce per-tenant data visibility.
5. Recommend a SQL auditing and threat-detection posture using Microsoft Defender for SQL, and integrate audit logs with a Log Analytics workspace.
6. Design a network-isolation pattern for Azure SQL using private endpoints, disabled public access, and VNet-level egress controls.

Building Blocks

Transparent Data Encryption (TDE) — Analogy: a whole-disk encryption on your laptop — if someone steals the drive, they get ciphertext. Formal: page-level AES-256 encryption of data files, log files, and backups, driven by a Database Encryption Key (DEK) that is itself wrapped by a TDE Protector (either service-managed or a key in Azure Key Vault). Why it matters: mandatory for most compliance regimes; transparent to the application; protects offline copies of the database.

Customer-Managed Key (CMK / BYOK) — Analogy: owning the master key to your apartment building instead of renting it from the super. Formal: a key in Azure Key Vault or Managed HSM that wraps the DEK; the customer controls rotation, access, and revocation. Why it matters: required by regulators that demand the ability to revoke access cryptographically (e.g., FedRAMP High, certain EU financial regulators); revoking the CMK renders the database unreadable even to Microsoft.

Always Encrypted (AE) — Analogy: a letter you seal before handing it to the post office — the postal workers route the envelope but never read the contents. Formal: client-side column encryption where the driver (on the application server) holds the Column Encryption Key (CEK); the server stores and queries only ciphertext. Why it matters: the only built-in Azure SQL feature that protects data from a privileged DBA or from a compromised SQL instance.

Deterministic vs. Randomized Encryption — Analogy: deterministic is like always translating "cat" to "xyz" — you can still look it up; randomized translates "cat" to a new string every time — nobody can look it up, not even you. Formal: deterministic produces the same ciphertext for the same plaintext (supports equality lookups, joins, group-by on the encrypted column); randomized produces different ciphertext each time (no server-side operations on the column). Why it matters: choosing the wrong one either breaks queries or leaks frequency information.

Dynamic Data Masking (DDM) — Analogy: redacting a document with a black marker before photocopying it for the public reading room — the original file still has the text. Formal: a presentation-layer feature that obscures column values in query result sets for users without the UNMASK permission. Why it matters: trivially cheap to apply; useful for non-production support access; does NOT encrypt, does NOT prevent a SELECT + export by a privileged user.

Row-Level Security (RLS) — Analogy: a bouncer at a club who checks your ID and only lets you into the rooms on your wristband. Formal: a SECURITY POLICY bound to an inline table-valued function that filters rows before they return to the caller, based on SESSION_CONTEXT, USER_NAME(), or DATABASE_PRINCIPAL_ID(). Why it matters: enables safe multi-tenant shared-schema designs without per-tenant views.

Microsoft Defender for SQL — Analogy: a smoke detector plus a security guard — it alerts on fires and prowlers. Formal: a paid add-on that runs vulnerability assessments and anomaly detection against Azure SQL databases, surfaces SQL-injection and brute-force alerts, and integrates with Microsoft Defender for Cloud. Why it matters: the default AZ-305 answer whenever the scenario mentions "detect anomalous queries" or "vulnerability assessment."

Private Endpoint (PEP) — Analogy: a dedicated private tunnel into the PaaS building, instead of using the public front door with a guest-list. Formal: a NIC deployed into a customer subnet that terminates a Private Link to the PaaS resource, giving it a routable private IP; public network access can then be disabled. Why it matters: removes the database from the public internet entirely; the default answer whenever the scenario says "no public internet exposure."

Deep Dive

Transparent Data Encryption at rest

TDE is on by default for every new Azure SQL Database and Managed Instance, using a service-managed key. Every page written to the .mdf and .ldf files is encrypted with AES-256; every backup is encrypted; every geo-replica and failover group inherits TDE automatically. The key hierarchy is: Database Encryption Key (DEK) → wrapped by → TDE Protector → (either) service-managed symmetric key OR an RSA-2048/3072/4096 key in Azure Key Vault (BYOK).

[!TIP] The exam tests the BYOK scenario relentlessly. The moment you see "customer must be able to cryptographically revoke Microsoft's access," the answer is TDE with customer-managed key in Azure Key Vault — not Always Encrypted, which protects against a different threat (DBAs), and not disable encryption (always wrong).

Switching from service-managed to customer-managed does not re-encrypt the data — only the TDE Protector (the key-encryption key) changes. That makes rotation cheap: a BYOK rotation updates the wrap of the DEK; the database pages do not get rewritten.

The autoRotationEnabled: true line is the AZ-305 "nice-to-know": when the key rotates in the vault, Azure SQL picks up the new version within 24 hours.

[!WARNING] If the customer-managed key is deleted or access revoked, the database transitions to Inaccessible state within minutes; the Recoverable window is 30 days, after which the data is irrecoverable. Soft-delete and purge-protection on the vault are not optional — they are a deployment gate.

Always Encrypted — protecting data from the DBA

Always Encrypted moves the trust boundary. The SQL engine never sees plaintext; instead, the .NET/ODBC/JDBC driver on the application tier decrypts column values on their way out and encrypts parameters on their way in. There are two key tiers: the Column Encryption Key (CEK) lives in the database (encrypted), and the Column Master Key (CMK) — which wraps the CEK — lives in Azure Key Vault or a Windows certificate store. A SQL login with db_owner cannot read plaintext unless they also hold the CMK in the vault — a separation of duties the exam loves.

There are two variants: Always Encrypted (classic, since SQL 2016) and Always Encrypted with secure enclaves (added 2019). The enclave variant lets the server perform LIKE, range, and rich queries on encrypted data inside a VBS or SGX enclave — it is the right choice when the application needs predicates richer than equality but still cannot trust the DBA. On Azure SQL Database, enclaves are supported in the Premium and Business Critical service tiers with VBS attestation via Microsoft Azure Attestation.

[!NOTE] Connection strings for Always Encrypted require Column Encryption Setting=Enabled and the application's identity must have get, unwrapKey, verify, and sign on the CMK in Key Vault. Forgetting the vault access policy is the number-one cause of SqlException: Failed to decrypt column in production rollouts.

Dynamic Data Masking — the cheap control

DDM is a presentation-layer redactor. It is configured per column with a masking function — default, email, random, partial(1,"XXXXXXX",0), etc. — and applied to every query result except when the caller holds the UNMASK permission or is the database owner.

DDM is useful when the threat is "a support engineer looks at a screen" — not when the threat is "a DBA exports data." The exam regularly tests this distinction in scenarios where developers ask for "protecting SSNs" with only a performance budget — the trap answer is Always Encrypted (operationally heavier, requires driver support); the right answer depends on who the attacker is. If the threat model is "shoulder-surfing and casual misuse by privileged staff," DDM suffices. If the threat is "malicious DBA or compromised SQL service account," only Always Encrypted helps.

Row-Level Security — multi-tenant by design

RLS binds a predicate function to a table and the SQL engine applies it to every SELECT, UPDATE, and DELETE. A common pattern is a TenantId column joined against SESSION_CONTEXT set by the application's middleware.

[!IMPORTANT] RLS is a filter, not encryption. If the threat model includes a db_owner who can drop the policy, RLS is insufficient on its own — pair it with auditing and strict role separation. RLS also interacts subtly with query plans: a badly-written predicate function can tank a plan by forcing nested loops.

SQL auditing and Defender for SQL

SQL auditing streams AUDIT events to one of three destinations: Storage Account, Log Analytics workspace, or Event Hub. For AZ-305 scenarios that mention "central SIEM" or "365-day retention with KQL queries," the answer is Log Analytics with long-term archive to storage. The per-GB ingestion cost of Log Analytics usually wins over the operational cost of parsing storage-account blobs.

Microsoft Defender for SQL layers anomaly detection on top. It costs roughly $$15$ per server per month and surfaces alerts for SQL-injection patterns, brute-force attempts, access from unusual locations, and suspicious data exfiltration (large result sets after midnight). It also runs weekly vulnerability assessments with baseline drift. For AZ-305, if the scenario mentions "detect anomalous queries" or "vulnerability baseline," Defender for SQL is the answer.

Network isolation

Public access to Azure SQL is gated by three knobs, in order of strength:

1. Server firewall rules — simplest, IP-allowlist-based. Good for bootstrap, weak for long-term.
2. Service endpoints — extend a VNet identity to the Azure SQL PaaS front door; traffic stays on the Microsoft backbone but the endpoint is still publicly resolvable.
3. Private endpoints — the database gets a private IP inside the customer VNet. Combined with publicNetworkAccess: 'Disabled', the server is only reachable via Private Link.

[!WARNING] A private endpoint alone does not block the public FQDN. You must ALSO set publicNetworkAccess: Disabled on the SQL server — otherwise a credential leak still lets anyone on the internet connect.

Worked Examples

Easy — Regulatory revocation

Problem. Contoso Health must comply with an EU regulation requiring the cryptographic ability to revoke Microsoft's access to patient records within 24 hours. Data remains queryable by internal analysts via Power BI today. Which data-protection control satisfies this requirement at the lowest operational cost?

Solution.

1. Identify the threat — "cryptographic revocation of provider access."
2. Eliminate Always Encrypted — it protects against DBAs but does not satisfy "provider access revocation" at the at-rest layer, and it breaks Power BI on those columns.
3. Eliminate DDM and RLS — presentation/filter layers; no cryptography.
4. TDE with a customer-managed key in Azure Key Vault (BYOK) is the match. Revoking the key wraps the database in ciphertext within minutes; Power BI continues to work as long as the key is active.

[!NOTE] Key insight: "provider revocation" is always TDE BYOK; "DBA protection" is always Always Encrypted; they are different threat models and the exam tests them as if they were the same.

Medium — Multi-tenant SaaS with PII columns

Problem. You are designing a shared-schema SaaS app on Azure SQL Database (Business Critical). Each row in dbo.Patients has a TenantId. The app stores SSN (exact equality lookups must work for identity matching) and ClinicalNotes (free text, never queried, high sensitivity). Support engineers have db_datareader. What do you recommend?

Solution.

• RLS filter on TenantId via SESSION_CONTEXT — mandatory for tenant isolation.
• Always Encrypted with deterministic on SSN — identity matching needs equality.
• Always Encrypted with randomized on ClinicalNotes — never queried server-side, highest protection.
• DDM on both columns for support tooling — a belt-and-braces layer. DDM is cheap and handles the shoulder-surfing case before AE even enters the picture.
• TDE stays on (default service-managed key is fine unless regulators require BYOK).
• Private endpoint + publicNetworkAccess: Disabled + Defender for SQL Standard.

[!NOTE] Key insight: the deterministic-vs-randomized choice is PER COLUMN. Never pick "one or the other" as a blanket answer — the exam punishes that.

Hard — Cross-region DR with revocable keys

Synthetic scenario (AZ-305 style). Fabrikam runs Azure SQL Managed Instance in North Europe with a failover group to West Europe. Regulatory review demands: (1) customer-managed keys; (2) the same revocation event must disable both replicas; (3) enclave queries on the PatientName column for partial-name search; (4) no public internet exposure on either replica.

Solution.

• Deploy a single Azure Key Vault with a customer-managed RSA key, OR a pair of region-local vaults replicating the same key — the former simplifies revocation (one delete, two replicas disabled) but creates a cross-region dependency on the vault. AZ-305 prefers the single-vault design with Geo-Redundant vault configuration, exactly because of constraint (2).
• Configure TDE with the customer-managed key on the primary; the secondary inherits automatically.
• Enable Always Encrypted with secure enclaves on the Business Critical tier on both replicas; register both MI instances with Microsoft Azure Attestation in a region close to the primary.
• Use deterministic encryption on PatientName only if exact-match queries suffice; if partial-name search is required, use randomized encryption + enclaves to run LIKE inside the trusted enclave.
• Private endpoint in each region's hub VNet; publicNetworkAccess: Disabled on both MI instances.
• Defender for SQL on both; audit logs streamed to a single central Log Analytics workspace in the primary region with cross-region link.

[!NOTE] Key insight: constraint (2) — "same revocation disables both" — is what forces the single-vault design. The exam frequently puts this constraint in the scenario to make the "two vaults, one per region" distractor wrong.

Visual Explanations

Figure 1 — Protection-surface decision tree

Caption: Each protection surface maps to one primary control. The surfaces are composable — a production design usually implements four or five of them at once.

Figure 2 — Always Encrypted key hierarchy

Caption: The CMK never leaves the vault; the driver requests an unwrap operation each session and caches the CEK in process memory. The SQL engine only ever sees ciphertext.

Figure 3 — Network isolation states

Caption: Firewall and service endpoint still route through the public FQDN. Only a private endpoint combined with publicNetworkAccess: Disabled removes the database from the public internet entirely.

Table — Control vs. threat matrix

Caption: Read down a row for "which controls mitigate this threat"; read across a column for "what this control does and does not cover."

Table — Service-tier feature gates

Caption: Enclaves are the main tier gate — if the scenario mentions enclave queries, the only correct SKU is Premium/Business Critical. Managed Instance uses VNet injection by default instead of Private Endpoint.

Common Mistakes

❌ Myth: "Dynamic Data Masking protects SSNs from export."

✅ Reality: DDM only obscures result sets at the presentation layer. A user with UNMASK — or db_owner, or who runs SELECT via the SSMS "Export Data" wizard — gets plaintext.

Why it's tricky: The Azure Portal UI shows DDM in the same "Data Discovery & Classification" blade as Always Encrypted, suggesting parity. They are not peers. DDM is a redactor; AE is an encryptor.

❌ Myth: "Enabling TDE is enough to protect against a rogue DBA."

✅ Reality: TDE encrypts data at rest. A DBA runs queries against a live, decrypted engine — TDE is transparent to them. Only Always Encrypted denies the DBA plaintext.

Why it's tricky: "Encrypted" sounds like "nobody but the owner can read it." In TDE's case, the owner is the SQL engine, not the human data custodian. Separating these two mental models is the core AZ-305 insight.

❌ Myth: "A private endpoint automatically blocks public internet access to the database."

✅ Reality: Deploying a private endpoint does not disable the public FQDN by default. You must explicitly set publicNetworkAccess: Disabled on the server, or the database is still reachable over the internet with valid credentials.

Why it's tricky: Many Bicep/Terraform samples show the PEP resource without the publicNetworkAccess property. The default is "Enabled" — a quiet footgun.

❌ Myth: "Deterministic encryption is the safe default for Always Encrypted."

✅ Reality: Deterministic leaks frequency information — the ciphertext distribution mirrors the plaintext distribution. For low-cardinality columns like "blood type" or "Yes/No survey answer," deterministic is actively dangerous because an attacker can infer values from the frequency table. Randomized is the safe default; use deterministic only when you need equality.

Why it's tricky: Deterministic lets queries work — it feels permissive and therefore "safe." Safety here is about what the attacker can learn, not about what the query engine can do.

Practice Exercises

Exercise 1 🟢. Contoso Manufacturing stores supplier bank-account numbers in a column used for payment batch runs (nightly SELECT to build SWIFT files). A DBA has db_owner on the database. Recommend a control.

▶💡 Hint

Who is the threat? Does the nightly job need to read plaintext, and on what tier does it run?

▶✅ Solution

Always Encrypted with randomized encryption on the column. The nightly batch job runs on an application server — it is a driver client, so it can hold the CEK. The DBA cannot read plaintext without vault access. TDE is insufficient because the DBA queries a live engine. DDM loses plaintext to anyone with UNMASK or db_owner.

Exercise 2 🟢. An AZ-305 scenario says: "a regulator requires that the organization be able to prevent Microsoft from accessing the data within one hour by performing a single administrative action." Recommend a control.

▶💡 Hint

What does "one administrative action" look like cryptographically?

▶✅ Solution

TDE with a customer-managed key in Azure Key Vault (BYOK). Revoking the key (or soft-deleting it — not purging) makes the database Inaccessible within minutes. Note: this does not protect against DBAs (use Always Encrypted for that) — these are different requirements that often appear in the same exam question as distractors for each other.

Exercise 3 🟡. Your web app authenticates via Microsoft Entra ID with a managed identity. You enable Always Encrypted on three columns. Users report Failed to decrypt column errors after deployment. What is the most likely cause?

▶💡 Hint

The driver needs to talk to Key Vault, not just to SQL.

▶✅ Solution

The managed identity of the App Service does not have get, unwrapKey, verify, and sign permissions on the CMK in Azure Key Vault. Add an access policy (or, better, an RBAC role assignment of Key Vault Crypto Service Encryption User) scoped to the CMK. Also verify the connection string includes Column Encryption Setting=Enabled.

Exercise 4 🟡. Design a multi-tenant Azure SQL Database where tenants share the same tables. Tenant A must never see tenant B's rows, even if a developer forgets to add a WHERE TenantId = @id to a query. Recommend the control(s).

▶💡 Hint

What enforces the filter at the engine layer, not the app layer?

▶✅ Solution

Row-Level Security with a SECURITY POLICY that binds a predicate function to each tenant-scoped table. The predicate reads SESSION_CONTEXT(N'TenantId') which the app middleware sets on every connection. Add BLOCK predicates on AFTER INSERT/AFTER UPDATE to prevent a tenant from writing rows with another tenant's ID. Layer auditing via Defender for SQL to alert on policy bypass attempts (e.g., DISABLE SECURITY POLICY).

Exercise 5 🔴. Your architecture requires audit-event queries across both Azure SQL Database and Azure SQL Managed Instance, 365 days of retention with KQL, and security-team alerts on anomalous access patterns. Design the auditing and detection topology.

▶💡 Hint

There is one log destination that gives you KQL plus long retention; there is one detection service that gives you anomaly alerts.

▶✅ Solution

Configure SQL auditing on each server/instance to a single Log Analytics workspace. Set retention on the workspace to 90 days (ingestion tier) plus a 275-day archive tier, totalling 365 days. Enable Microsoft Defender for SQL on each server/instance (Standard tier). Create an Azure Monitor alert rule on the SQLSecurityAuditEvents table for anomalous login patterns; Defender surfaces its own anomaly alerts through Microsoft Defender for Cloud, which integrates with Microsoft Sentinel for the SIEM case.

Exercise 6 🔴. Synthetic scenario (AZ-305 style). Acme Bank requires: (a) no public internet exposure on its Azure SQL Managed Instance; (b) cryptographic revocation of Microsoft's access on demand; (c) DBA separation of duties — DBAs can tune queries but must not see customer names or account balances; (d) detection of SQL injection attempts. Design the data-protection stack.

▶💡 Hint

Four requirements, four controls — but note (a) is a specific Managed Instance pattern.

▶✅ Solution

(a) Managed Instance does not use a traditional private endpoint; it is deployed into a customer VNet subnet via VNet injection. Set publicDataEndpointEnabled: false and restrict inbound NSG to on-prem/hub ranges only. (b) TDE with customer-managed key in Azure Key Vault (BYOK); enable auto-rotation. (c) Always Encrypted on CustomerName (deterministic if name-lookups needed, otherwise randomized) and on AccountBalance (randomized — balances should not leak via frequency analysis). The DBA's SQL login holds no vault permission, so plaintext is inaccessible to them. (d) Microsoft Defender for SQL Standard tier. Optionally layer Log Analytics + Sentinel for the SOC. This is a four-control stack — losing any one breaks a requirement.

Exercise 7 🟡. Choose between deterministic and randomized for each column of dbo.Patient: (1) PatientId (used as an FK from 40 tables); (2) FirstName (used in partial-match search); (3) SSN (used for equality lookups only); (4) DiagnosisNotes (free text, never filtered on).

▶💡 Hint

Equality → deterministic. No server-side access → randomized. Partial match → needs enclaves.

▶✅ Solution

(1) PatientId: deterministic (FK joins need equality). (2) FirstName: randomized + enclaves if partial-match is a hard requirement; otherwise, do not encrypt FirstName at all and rely on DDM + RLS. (3) SSN: deterministic (equality lookups). (4) DiagnosisNotes: randomized (no server-side query, maximum protection, no frequency leak on long free text).

Summary & Concept Map

• Relational data protection on Azure is a stack of composable controls, not a single feature — design decisions are per-column, per-server, and per-threat-model.
• TDE protects against media theft; the BYOK variant adds cryptographic provider-revocation; Always Encrypted protects against DBAs; enclaves extend AE to rich queries.
• Deterministic vs. randomized is a per-column choice driven by which operations the server must perform on the ciphertext — deterministic enables equality at the cost of a frequency leak.
• DDM is a cheap presentation-layer redactor, not encryption — never pick it against a privileged-insider threat.
• RLS is the shared-schema SaaS pattern — bind to SESSION_CONTEXT and add BLOCK predicates to prevent cross-tenant writes.
• Auditing belongs in Log Analytics for KQL + retention; Microsoft Defender for SQL adds anomaly detection and vulnerability assessment.
• Network isolation requires both private endpoint (or VNet injection for MI) and publicNetworkAccess: Disabled — one without the other leaves a gap.