Encryption & Network Security
For a quick comparison of all security features (TDE vs Always Encrypted vs RLS vs DDM vs Auditing), see the Security Controls Comparison.
Encryption Layers — The Complete Picture
Memorize the three states: At rest → TDE, In transit → TLS, In use → Always Encrypted. This is the most tested encryption framework on DP-300.
Transparent Data Encryption (TDE)
Encrypts data at rest — database files, backups, and transaction logs.
| Aspect | Details |
|---|---|
| Algorithm | AES-256 |
| Enabled by default | ✅ Yes, on Azure SQL Database |
| Performance impact | ~3-5% (minimal) |
| What's encrypted | .mdf, .ndf, .ldf files, and backup .bak files |
| Key management | Service-managed key (default) or BYOK via Key Vault |
| Granularity | Entire database (you can't encrypt selectively) |
TDE Key Hierarchy
Oracle DBA parallel: Azure TDE = Oracle TDE, but simpler. Oracle TDE requires wallet creation, ALTER TABLESPACE ENCRYPT, per-tablespace setup. Azure TDE is always on and encrypts the entire database automatically. The BYOK option maps to Oracle's external keystore.
TDE encrypts backups too! If you restore a TDE-encrypted backup to another server, you need access to the same TDE protector key. With service-managed keys, Azure handles this. With BYOK (Key Vault), the target server must have access to the same Key Vault key.
Always Encrypted
Encrypts sensitive columns — data is encrypted by the client application. The SQL engine never sees plaintext.
Architecture
Encryption Types
| Type | Same input → | Can query with = ? | Can JOIN? | Security Level |
|---|---|---|---|---|
| Deterministic | Same ciphertext | ✅ | ✅ | Good |
| Randomized | Different ciphertext each time | ❌ | ❌ | Best |
Key Hierarchy
| Component | Stored Where | Purpose |
|---|---|---|
| Column Master Key (CMK) | Key Vault / Windows Cert Store | Protects the CEK |
| Column Encryption Key (CEK) | Inside the database (encrypted form) | Actually encrypts column data |
Always Encrypted limitations: No LIKE queries, no range comparisons (>, <), no computed columns referencing encrypted columns, no server-side operations on encrypted data. The client driver handles all crypto — the server only stores/retrieves ciphertext.
When to choose Always Encrypted vs TDE: TDE protects against stolen disk/backups (physical theft). Always Encrypted protects against even DBAs seeing sensitive data. If the question says "DBAs should not see SSN/credit cards" → Always Encrypted. If it says "encrypt data at rest" → TDE.
Network Security — Defense in Depth
Connectivity Options (Most → Least Secure)
| Option | Traffic Path | Exam Scenario |
|---|---|---|
| Private Endpoint | Private IP in your VNet → Azure backbone | "Most secure" / "no public internet" |
| VNet Service Endpoint | Subnet → Azure backbone (but IP is still public) | "Restrict to specific subnet" |
| Firewall Rules | Public IP allowlist | "Allow specific client IPs" |
| Allow Azure Services (0.0.0.0) | Any Azure service can connect | ⚠️ "Allow Azure services" toggle — overly broad for production |
If the exam says "no data should traverse the public internet" → the answer is Private Endpoint. VNet Service Endpoints still use a public IP (traffic goes over the Azure backbone, but the endpoint is technically public). Private Endpoint assigns a truly private IP inside your VNet.
Private Endpoint Architecture
When using Private Endpoints, also disable public access on the SQL Server. Otherwise, the database is accessible via both private AND public endpoints. Disable public access via the "Public network access" setting = Disabled.
Auditing — Where Do Logs Go?
| Destination | Use Case | Retention |
|---|---|---|
| Log Analytics | KQL queries, Azure Monitor integration, dashboards | Configurable (30-730 days) |
| Storage Account | Long-term archival, compliance | Unlimited |
| Event Hub | Real-time streaming to SIEM (Sentinel, Splunk) | Streaming (no built-in retention) |
Best practice: Send audit logs to Log Analytics for querying + Storage Account for compliance archival. Use Event Hub only if you have a SIEM that needs real-time feed. This mirrors Oracle Unified Audit → Oracle Audit Vault + AVDF pattern.
Microsoft Defender for SQL
| Feature | What It Does |
|---|---|
| Vulnerability Assessment | Scans for misconfigurations (weak passwords, excessive permissions) |
| Advanced Threat Protection | Detects SQL injection, anomalous access, brute force attacks |
| Data Classification | Discovers and labels sensitive data (PII, financial, health) |
Defender for SQL combines all three features. If the exam asks about "detecting SQL injection attempts" → Advanced Threat Protection. "Finding unencrypted sensitive columns" → Data Classification. "Checking for security misconfigurations" → Vulnerability Assessment.