6.1 Azure SQL Platform Security
Key Takeaways
- Azure SQL Database is fully managed PaaS; Managed Instance is near-100% SQL Server compatibility in a VNet; SQL on VM is IaaS—you own OS and SQL Server hardening.
- Prefer Microsoft Entra authentication and Entra-only mode so database identities follow Conditional Access, MFA, PIM, and centralized lifecycle instead of local SQL passwords.
- Network isolation for Azure SQL Database uses server firewall rules, virtual network rules/service endpoints, and private endpoints with public access disabled; Managed Instance is VNet-native by design.
- TDE encrypts data at rest (on by default for new databases); Always Encrypted protects columns from DBAs in use; Dynamic Data Masking hides results; Row-Level Security filters rows by principal.
- Minimize the admin surface: least-privilege Azure RBAC on the server resource, limited Microsoft Entra admins, role-based database permissions, and no standing db_owner for applications.
Why Database Platform Security Matters on SC-500
Under Secure storage, databases, and networking, the SC-500 blueprint measures Implement security for databases: platform-level security in Azure SQL, auditing, and Defender for Databases. This section is the platform baseline—what you configure before you rely on audit trails or threat detection.
Defense-in-depth for Azure SQL moves from the outside in: network → authentication → authorization → threat protection → information protection (encryption, masking, classification). SC-500 expects you to pick the control that matches the threat, not stack buzzwords randomly.
Azure SQL Deployment Options (High Level)
| Aspect | Azure SQL Database | Azure SQL Managed Instance | SQL Server on Azure VM |
|---|---|---|---|
| Model | Fully managed PaaS (logical server + databases) | Near-full SQL Server instance as PaaS in your VNet | IaaS: you manage OS, SQL, patching, high availability |
| Networking | Public endpoint + firewall/VNet rules; private endpoints common | Deployed into a VNet; private connectivity by design (optional public endpoint can be disabled) | NSGs, private IPs, bastion/JIT—classic VM network model |
| Compatibility | Modern cloud database features; some SQL Server surface limited | Highest lift-and-shift compatibility (linked servers, SQL Agent, cross-db, etc.) | Full SQL Server on Windows/Linux VM |
| Patching / HA | Microsoft-managed | Microsoft-managed instance patching | Customer-managed (unless automation/Arc assists) |
| Security ownership | Platform configs + Entra + data-plane controls | Same + instance-level SQL features | Full stack: disk encryption, OS hardening, SQL config, Defender for Servers/SQL on machines |
Exam trap: Firewall IP rules as documented for Azure SQL Database do not apply the same way to Managed Instance. MI connectivity is primarily VNet-based; do not answer “add a server firewall rule” for MI scenarios that need subnet/private-endpoint design.
SQL on VM is still “database security,” but many SC-500 VM controls live under Secure compute (disk encryption, Bastion, JIT, Defender for Servers). For this chapter, remember: platform encryption features like TDE/Always Encrypted can apply on SQL Server, but network isolation and identity differ because you own the host.
Authentication: Microsoft Entra vs SQL Auth
Authentication proves identity. Azure SQL Database and Managed Instance support:
| Method | How it works | SC-500 preference |
|---|---|---|
| Microsoft Entra authentication | Connect with Entra users, groups, managed identities, service principals | Preferred—central lifecycle, MFA, Conditional Access, PIM |
| SQL authentication | Username/password logins local to the SQL engine | Limit; avoid for humans; apps should prefer managed identity |
| Entra-only authentication | Disables SQL (local) authentication for the logical server or MI | Strong hardening goal for new deployments |
| Windows auth for Entra principals (MI) | Kerberos path for Managed Instance modernizations | MI-specific lift-and-shift scenario |
Setting up Entra access (conceptual steps)
- Assign a Microsoft Entra administrator on the logical server or Managed Instance (user or group).
- Create database users that map to Entra principals (
CREATE USER [user@contoso.com] FROM EXTERNAL PROVIDERor group equivalents). - Optionally enable Microsoft Entra-only authentication so local SQL logins cannot be used.
- For applications, use managed identity → create Entra user for that identity → grant least-privilege database roles.
Policy alignment: Azure Policy can require Entra-only at create and/or block re-enabling local auth after create. Exam language often rewards Entra-only + managed identity over “shared SQL password in Key Vault” when the goal is eliminate password auth entirely—though Key Vault remains correct for residual secrets elsewhere.
Failed Entra logins: For Azure SQL Database, failed Microsoft Entra sign-ins may not appear in SQL audit logs the same way SQL-auth failures do, because credentials can be rejected before the database session is established. Investigate Entra sign-in logs as well as SQL audits.
Network Controls: Firewall, Service Endpoints, Private Endpoints
Azure SQL Database / Synapse logical server
| Control | Behavior |
|---|---|
| Server-level IP firewall rules | Allow listed public client IPs across databases on the server |
| Database-level IP firewall rules | Scoped to a single database |
| Virtual network rules | Allow subnets with the Microsoft.Sql service endpoint |
| Private endpoints (Private Link) | Private IP in your VNet for the SQL data plane; can disable public network access |
| Allow Azure services | Broad toggle—use carefully; often too permissive for production |
Most secure common pattern for PaaS SQL: private endpoint + public network access disabled, consumers on peered/hub VNets with private DNS (privatelink.database.windows.net), apps using managed identity over that private path.
Service endpoints vs private endpoints: Service endpoints keep the public data-plane VIP but restrict source subnets on the Azure backbone. Private endpoints bring SQL into private address space and support full public disablement. “Isolate from the public internet” answers almost always prefer private endpoint + public access off.
Managed Instance
Managed Instance sits in a dedicated subnet with strict networking requirements. Harden by keeping public endpoint disabled when not required, controlling NSG/route tables per Microsoft guidance, and using private endpoints where multi-VNet access is needed. Do not assume Database-style IP firewall blades solve MI access.
Encryption and Information Protection Controls
| Control | Protects against | Where encryption happens | App changes? |
|---|---|---|---|
| TLS (in transit) | Network eavesdropping | Client ↔ service | Drivers must encrypt; prefer modern TLS |
| TDE (at rest) | Offline disk/backup theft | Service encrypts database files/backups | No |
| TDE with CMK (BYOK) | Same + customer key control/revocation | DEK protected by Key Vault key | Key Vault access for service identity |
| Always Encrypted | DBA/sysadmin reading plaintext columns | Client driver encrypts/decrypts; keys never exposed to engine (classic mode) | Yes—driver + column master keys |
| Dynamic Data Masking | Casual overexposure in query results | Result-set masking for non-privileged users | Usually minimal |
| Row-Level Security | Horizontal over-read of rows | Predicate functions filter rows | App must respect security context |
Transparent Data Encryption (TDE)
- On by default for new Azure SQL databases; AES protects data at rest.
- Service-managed keys: Microsoft rotates the protector certificate.
- Customer-managed keys in Azure Key Vault (server-level and, where supported, database-level CMK) for compliance that demands customer key lifecycle, audit, and revocation.
- TDE does not stop a privileged SQL principal who can query the database from reading plaintext—use Always Encrypted, masking, or RLS for that threat model.
Always Encrypted
Use when the requirement is: application can read SSN/PAN; database administrators must not. Column encryption keys protect data in use inside the engine’s visibility. Keys live in Windows cert store or Key Vault. Secure enclaves extend what computations can run on encrypted data—know the feature family name; do not confuse with TDE.
Dynamic Data Masking (DDM)
Masks designated columns in query results for users without UNMASK privilege. Underlying stored values remain plaintext (still protected at rest by TDE). Ideal for support/reporting accounts that should see partial emails or card numbers.
Row-Level Security (RLS)
Security predicates (inline table-valued functions) restrict which rows a principal can SELECT/UPDATE/DELETE. Classic multi-tenant SaaS pattern: TenantId = SESSION_CONTEXT or Entra user mapping.
Authorization and Minimal Admin Surface
Think in two planes:
| Plane | Examples | Least privilege |
|---|---|---|
| Azure control plane | Create server, set firewall, enable Defender, assign Entra admin | Contributor/SQL Server Contributor only for platform operators; prefer custom roles |
| SQL data plane | Logins, users, db_datareader, custom roles, RLS | Apps get execute/select on needed objects only; humans via Entra groups + PIM for elevation |
Minimal admin surface checklist:
- One Entra admin group (not personal standing accounts) gated by PIM.
- Entra-only authentication in production where applications support it.
- No shared SQL
sa-style passwords; no app usingdb_owner. - Separate duties: security ops manage Defender/auditing destinations; DBAs manage schema; apps use dedicated identities.
- Resource locks on production logical servers to slow accidental delete.
- Private networking so even stolen credentials fail without network path.
Relationship to Microsoft Defender for SQL
Platform hardening reduces risk; Defender for SQL (part of Defender for Databases) adds vulnerability assessment and advanced threat protection alerts. Enabling Defender does not replace firewall rules, Entra-only auth, TDE, or least privilege—it detects when those controls fail or are bypassed. Section 6.3 covers plans and alert types in depth.
Scenario: A healthcare app stores national IDs. Requirements: (1) encryption at rest with customer-managed keys, (2) DBAs must not read IDs in SSMS, (3) only hospital VNet access, (4) no SQL passwords. Correct stack: TDE with CMK in Key Vault (purge protection), Always Encrypted on ID columns, private endpoint + public access disabled, Entra-only with app managed identity, least-privilege roles, then Defender for SQL for injection/anomaly detection.
SC-500 Exam Traps (Platform)
- TDE ≠ Always Encrypted ≠ DDM. TDE = at rest; Always Encrypted = columns hidden from DBA; DDM = result masking, not encryption.
- Entra-only is stronger than “also allow SQL auth for break-glass forever.”
- Private endpoint answers beat “open 0.0.0.0/0 with strong password.”
- Managed Instance is not configured like Database IP firewall-first.
- SQL on VM still needs host security—not only TDE.
Compliance requires that database administrators cannot read Social Security numbers stored in Azure SQL Database, while the application must process full values. Which feature best meets this requirement?
A security engineer is hardening a new Azure SQL Database logical server for production. Which authentication configuration best aligns with SC-500 least-privilege identity guidance?
Which statement correctly contrasts Azure SQL Database networking with Azure SQL Managed Instance?
TDE is enabled on an Azure SQL database. A user with db_owner runs SELECT on a customers table and sees cleartext names. Why did TDE not hide the data?