What's changed: Deepened DP-300 Chapter 2 (ja figures; comparison tables/scenarios/FAQ/traps/deep paragraphs in all sections)
2.2Data Encryption and Auditing
Understand Azure SQL encryption at rest (TDE), Always Encrypted for client-side column protection, dynamic data masking, auditing, and Microsoft Defender for SQL.
To protect the data itself use encryption, to limit exposure use masking, and to detect misuse combine auditing and threat detection.
2.2.1Encryption, masking, auditing
- TDE: encrypt the database at rest (on by default); protects against disk/backup theft.
- Always Encrypted: encrypt sensitive columns client-side so the server never sees plaintext.
- Dynamic data masking: mask sensitive values in query results (stored values unchanged); minimizes exposure.
- Auditing / Defender for SQL: log operations to an audit log; detect anomalies via vulnerability assessment and threat detection.
Common on DP-300: encryption at rest = TDE (default), hide columns even from the server = Always Encrypted, mask in results = dynamic data masking, log operations = auditing, vulnerability assessment/threat detection = Defender for SQL. TDE protects at rest; Always Encrypted also protects in use.
Dynamic data masking limits display, not encryption; privileged users or direct value access may still see real values.
Layer the protections. TDE encrypts data at rest (data files, logs, backups) and is on by default; the protector can switch from a service-managed key to a customer-managed key (CMK/BYOK) stored in Key Vault. Always Encrypted encrypts client-side using a column master key (CMK) and a column encryption key (CEK), so the server never sees plaintext (typically limited to equality; range needs Secure Enclaves). Dynamic data masking limits display, not encryption—users with UNMASK or direct value access still see real values. Row-level security (RLS) narrows rows and column-level permissions narrow columns. For detection/governance, use SQL auditing (log operations to Log Analytics/Storage/Event Hub) and Microsoft Defender for SQL (vulnerability assessment recommendations, threat alerts for SQL injection/anomalous logins), plus data discovery & classification (Information Protection) for sensitivity and TLS enforcement in transit. Split roles: “at rest = TDE,” “in use = Always Encrypted,” “display limit = masking,” “record = auditing,” “detect = Defender.”
| Feature | Protects | Server sees plaintext? |
|---|---|---|
| TDE | At rest (files/backups) | Yes (decrypted in processing) |
| Always Encrypted | Sensitive columns end-to-end | No (decrypted client-side) |
| Dynamic data masking | Result display only (stored unchanged) | Yes (real value with UNMASK) |
| TLS | Data in transit | N/A (encrypts the path) |
Scenario: hide credit card numbers even from DBAs and mask all but the last 4 digits on a support screen. → Encrypt the sensitive column client-side with Always Encrypted (CMK/CEK) so DBAs can’t see plaintext, and mask for display with dynamic data masking. Cover everything with TDE at rest, log via auditing, and detect anomalies with Defender for SQL.
FAQ: Q. Is dynamic data masking a substitute for encryption? → A. No—it only hides display; stored values remain plaintext and UNMASK reveals real values. To truly hide secrets, use Always Encrypted. Q. Can TDE and Always Encrypted coexist? → A. Yes—TDE for everything at rest and Always Encrypted for specific sensitive columns, layered.
Trap: “dynamic data masking encrypts sensitive data” is wrong—masking limits display; stored values are plaintext and UNMASK reveals them. Encryption is TDE (at rest)/Always Encrypted (columns, in use). Also “TDE hides sensitive columns even from DBAs” is wrong—DBAs handle plaintext; you need Always Encrypted.
2.2.2Section summary
- Encryption = TDE (at rest) / Always Encrypted (columns, in use)
- Masking = dynamic data masking, detection = auditing + Defender for SQL
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. Which feature encrypts an Azure SQL database at rest and is on by default?
Q2. You want to mask sensitive values (e.g., card numbers) in query results without changing stored values. What do you use?
Q3. You want vulnerability assessment and anomalous-access threat detection for SQL. What do you use?