5.4 Relational Data Protection Design

Why This Topic Matters

The final relational-data bullet, "Recommend a solution for data protection," closes out this functional group by testing encryption, auditing, and data-recovery design for relational stores. This section focuses on protecting data at rest, in use, and from unauthorized access — recovery procedures for restoring a failed database are covered in depth in the Business Continuity chapter (8.4), so here the emphasis is on choosing the right protection mechanism for a given compliance or security requirement.

Encryption at Rest: Transparent Data Encryption

Transparent Data Encryption (TDE) encrypts the entire database, its backups, and its transaction log files at rest, transparently to the application — no application code changes are required. TDE is on by default for new Azure SQL databases. Two key modes matter for design decisions:

• Service-managed TDE: Microsoft manages the encryption key entirely; simplest option, no customer action required.
• Customer-managed TDE (bring your own key / BYOK): the organization supplies and controls the TDE Protector, an asymmetric key stored in a customer-owned Azure Key Vault. The key never leaves the vault; Azure SQL wraps the database encryption key with it. This gives the customer control over key rotation, revocation, and audit trail of key usage — the correct recommendation whenever a scenario requires the customer, not Microsoft, to control encryption keys for compliance (common in healthcare, finance, and government requirements).

Protecting Specific Columns: Always Encrypted

Always Encrypted protects sensitive data at the column level (e.g., national ID numbers, payment card numbers) so that the data remains encrypted in memory, in transit, and in query plans — even database administrators with full permissions cannot see the plaintext, because encryption and decryption happen at the client driver, not on the server. The Always Encrypted with secure enclaves variant additionally allows richer server-side operations (pattern matching, range comparisons) on encrypted columns inside a hardware-protected enclave, without exposing plaintext outside it. Use Always Encrypted whenever a scenario says specific columns must be hidden even from privileged database roles — TDE alone does not satisfy that requirement, since TDE only protects data at rest and DBAs can still query plaintext through a normal connection.

Masking and Auditing

• Dynamic Data Masking: obfuscates sensitive data in query results for non-privileged users (e.g., showing only the last four digits of a credit card) without changing the underlying stored data. It is a UX/least-privilege control, not a strong security boundary — it does not stop a determined attacker with query access, so it complements rather than replaces encryption.
• Auditing: tracks database events (logins, schema changes, data access) and writes them to a chosen destination — an Azure Storage account, a Log Analytics workspace, or an Event Hub — configurable at the server level (applies to every database) or database level (granular, per-database policy). Choose Log Analytics or Event Hub when a scenario requires near-real-time analysis or SIEM integration; choose Storage when the requirement is long-term, low-cost retention of audit logs for compliance evidence.
• Microsoft Defender for SQL bundles vulnerability assessment (scans for misconfigurations and missing patches) with advanced threat protection (behavioral analytics that alert on SQL injection, brute-force attempts, and anomalous access patterns). Recommend it whenever a scenario asks for proactive threat detection on top of passive auditing.

Backup Retention as a Protection Control

Backups are also a data-protection design lever, distinct from the disaster-recovery mechanics covered in Chapter 8:

• Point-in-time restore (PITR): automatic full/differential/log backups retained for a configurable 1 to 35 days (1-7 days for Basic tier), letting you restore to any point within that window; new databases default to a 7-day retention.
• Geo-restore: because PITR backups are stored on geo-redundant storage by default, you can restore a database into a different Azure region if the primary region is unavailable.
• Long-Term Retention (LTR): for compliance mandates beyond 35 days, LTR stores full backups on a configurable weekly/monthly/yearly policy for up to 10 years, independent of the live database's retention window.

Geo-Replication as a Protection Mechanism

Active geo-replication and auto-failover groups create readable secondary replicas in a different Azure region, protecting relational data against a regional outage. From a data-protection lens (as opposed to the availability-architecture lens covered in Chapter 9.3), the design takeaway is: geo-replication gives you a secondary copy you control the failover of manually (active geo-replication) or automatically behind a stable listener endpoint (auto-failover groups), and either can also serve read-only reporting traffic. Recommend geo-replication/failover groups whenever a compliance or business-continuity requirement calls for a regionally separated, near-real-time copy of relational data, in addition to (not instead of) backups.

Exam Scenarios

Scenario A: A healthcare provider must ensure the organization — not Microsoft — controls and can revoke the encryption keys protecting patient records at rest, for HIPAA compliance. Answer: Customer-managed TDE (BYOK) with the TDE Protector stored in an organization-owned Azure Key Vault.

Scenario B: A payment processor must ensure that even database administrators cannot view plaintext credit card numbers stored in a specific column. Answer: Always Encrypted (with secure enclaves if server-side computation on those columns is also required).

Scenario C: An auditor requires a 7-year retained record of every login and schema change on a production database, plus near-real-time SIEM ingestion. Answer: Auditing configured at the server level, streaming to both an Event Hub (for SIEM) and a Storage account with an LTR-aligned retention policy.

Common Traps

• Assuming TDE protects against a privileged database administrator viewing data — it only protects data at rest (files on disk); a logged-in DBA still sees plaintext through a query. Use Always Encrypted for that requirement instead.
• Recommending Dynamic Data Masking as a compliance-grade security control — it is a display-layer convenience, not encryption, and does not satisfy "must not be able to access the underlying sensitive data" requirements.
• Confusing PITR (short-term, 1-35 days, for operational recovery) with Long-Term Retention (up to 10 years, for compliance archiving) — a scenario asking for multi-year retention needs LTR, not a longer PITR window (which is capped at 35 days).