Database Security & Compliance
TDE, Always Encrypted, dynamic data masking, auditing, and threat detection — design a database security posture that satisfies auditors and protects data at rest and in transit.
Why database security design matters
Database security has three layers: encrypt it, mask it, audit it.
Encrypt: TDE encrypts data on disk. Always Encrypted encrypts specific columns so even DBAs can’t read them. TLS encrypts data in transit.
Mask: Dynamic data masking hides sensitive data from non-privileged users without changing the actual stored data.
Audit: SQL auditing logs who did what. Advanced Threat Protection detects suspicious activity automatically.
Encryption layers
| Feature | TDE (Transparent Data Encryption) | Always Encrypted | TLS in Transit |
|---|---|---|---|
| What it protects | Data at rest (files, backups, logs) | Specific columns (SSN, credit card, PII) | Data moving between app and database |
| Encryption happens | At the database engine level (transparent to apps) | At the client/application (data encrypted before reaching DB) | At the connection level (TLS 1.2+) |
| DBA can read data? | Yes — TDE is transparent to queries | No — DBA sees encrypted values, only app with key can decrypt | Yes — once data reaches the DB, it's decrypted |
| Key management | Service-managed (default) or customer-managed (Key Vault) | Column master key in Key Vault or cert store | Automatic (Azure-managed certificates) |
| Performance impact | Minimal (~3-5%) | Moderate — limits query operations on encrypted columns | Minimal |
| Default in Azure SQL? | Yes — enabled by default | No — must be configured per column | Yes — enforced by default |
🏦 Elena’s encryption design:
- TDE with customer-managed keys (Managed HSM) — satisfies PCI DSS requirement for customer-controlled encryption
- Always Encrypted on credit card numbers and Social Security Numbers — even Elena’s DBAs cannot read PII
- TLS 1.2 enforced on all connections (disable older versions)
Exam tip: TDE vs Always Encrypted — know the difference
The exam tests whether you understand the protection boundary:
- TDE: Protects against physical theft of disk/backup files. Does NOT protect data from DBAs or anyone with database access.
- Always Encrypted: Protects data from the database engine itself. Only the application (with the column master key) can decrypt. Choose this when “even database administrators should not see sensitive data.”
If the scenario says “protect from unauthorised physical access” → TDE. If it says “protect from privileged insiders” → Always Encrypted.
Dynamic data masking
Masks sensitive data in query results without changing the stored data:
| Mask Type | What It Shows | Example (SSN: 123-45-6789) |
|---|---|---|
| Default | XXXX for strings, 0 for numbers | XXXX |
First letter + XXX@XXXX.com | aXXX@XXXX.com | |
| Custom string | Configurable prefix/suffix/padding | XXX-XX-6789 |
| Random number | Random value in specified range | 847 |
Design note: Masking is NOT encryption. Users with UNMASK permission see original values. It’s a convenience layer for limiting exposure, not a security boundary. For real protection, use Always Encrypted.
Auditing and threat detection
SQL Auditing destinations
| Destination | Use Case | Retention |
|---|---|---|
| Storage account | Long-term compliance archive | Configurable lifecycle |
| Log Analytics | Query with KQL, create alerts | Workspace retention settings |
| Event Hubs | Stream to external SIEM (Splunk, Datadog) or custom pipeline | Consumer-controlled |
Microsoft Defender for SQL
| Feature | What It Detects |
|---|---|
| SQL injection detection | Abnormal query patterns indicating injection attempts |
| Anomalous access | Access from unusual locations, unfamiliar principals |
| Brute force | Multiple failed login attempts |
| Vulnerability assessment | Misconfigurations, excessive permissions, missing encryption |
🏛️ David’s audit design: CloudPath Advisory configures government databases with:
- Audit logs to Log Analytics (90-day interactive query for investigations, Microsoft Sentinel for SIEM correlation)
- Audit logs to Storage (7-year archive for regulatory retention)
- Defender for SQL enabled on all databases — alerts sent to the security team’s Teams channel
Knowledge check
🏦 Elena's compliance team requires that database administrators cannot read credit card numbers stored in Azure SQL Database, but the payment application must still process them. Which feature should Elena recommend?
🏛️ David's government client stores classified data in Azure SQL Database. They need an audit trail that captures all SELECT queries on sensitive tables and cannot be tampered with by database administrators. Which combination should David recommend?
🎬 Video coming soon
Next up: Relational databases are secured — now let’s design for non-relational data — Cosmos DB & Semi-Structured Data.