Secure by Default

Sven Mikkelsen|Sep 26, 2025|7 min read

Secure by Default: AAD Only, Private Endpoints, and Auditing for Azure SQL

A zero-trust, least-privilege checklist you can actually run

Introduction - why this matters

You don't get hacked by what you don't know; you get hacked by what you left "temporarily" open. Azure SQL is powerful, but its defaults still need hardening. If you want zero-trust and least-privilege by design, lock identity to Entra ID (AAD) only, remove public network paths with Private Endpoints, and wire full-fidelity auditing to immutable storage and your SIEM. This checklist shows the exact switches, scripts, and tests.

Architecture at a glance

Goal: eliminate passwords, eliminate public ingress, and make every action observable.

  • Identity: AAD-only (no SQL logins), managed identities for apps, role-based least privilege.
  • Network: Public network disabled, traffic over Private Endpoint in your VNet with strict NSG/UDR.
  • Data plane controls: Encryption (TDE on, CMK optional), Ledger (optional), minimal surface area.
  • Visibility: Auditing to Storage (immutable), Log Analytics for hunting, Defender for Cloud alerts.

Fast checklist (copy/paste friendly)

1) Enforce AAD-only authentication

Why: kill passwords; unify lifecycle and MFA/Conditional Access.

CLI (preview flags sometimes change; verify in your subscription):

# Set Azure SQL logical server to AAD-only (no SQL logins)
az sql server update \
  -g <rg> -n <server-name> \
  --restrict-azure-services-access false \
  --enable-ad-only-auth true

# Make an AAD group the Azure AD admin (not a single user)
az sql server ad-admin create \
  -g <rg> -s <server-name> \
  --display-name "grp-sql-admins" \
  --object-id <aad_group_object_id>

T-SQL (run as an AAD admin) - grant minimal roles:

-- Create contained AAD users at the database level
CREATE USER [app-mi] FROM EXTERNAL PROVIDER;   -- managed identity
ALTER ROLE db_datareader ADD MEMBER [app-mi];  -- example: read only
-- Avoid db_owner; prefer explicit GRANTs or least-privilege roles

Policy guardrail (Azure Policy):

  • Deny: SQL servers without AAD-only enabled.
  • Deny: SQL servers with public network access enabled.
  • Audit/Deny: databases without auditing to approved destinations.

2) Remove public ingress with Private Endpoints

Why: public endpoints are the easiest pivot for attackers.

Disable public network access on the server:

az sql server update -g <rg> -n <server-name> --public-network-access Disabled

Create Private Endpoint into your spoke VNet:

# Private DNS zone for privatelink.database.windows.net
az network private-dns zone create -g <rg-net> -n privatelink.database.windows.net

# Link DNS zone to your VNet
az network private-dns link vnet create \
  -g <rg-net> -n link-sql-privdns \
  -z privatelink.database.windows.net \
  -v <vnet-name> -e true

# Create the Private Endpoint
az network private-endpoint create \
  -g <rg-net> -n pe-sql-<server-name> \
  --vnet-name <vnet-name> --subnet <subnet-name> \
  --private-connection-resource-id $(az sql server show -g <rg> -n <server-name> --query id -o tsv) \
  --group-id sqlServer \
  --connection-name pe-sql-conn

# DNS record auto-registered if using the built-in DNS zone group;
# otherwise add A record for <server-name>.privatelink.database.windows.net

Validate path:

nslookup <server-name>.database.windows.net
# Expect CNAME -> <server-name>.privatelink.database.windows.net -> Private IP

NSG/UDR guidance:

  • No inbound from 0.0.0.0/0; allow only required subnets (app tiers, jump boxes).
  • Force egress inspection if you run a firewall (Azure Firewall/NGFW) - but don't break DNS.

3) Turn on Auditing (immutable + SIEM) and Threat Detection

Why: if you can't see it, you can't defend it (or prove it).

Destination options (pick at least 2: one immutable, one queryable):

DestinationPurposeProsCons
Storage (Blob)Immutable, cheap, retention-friendlyLegal hold, WORMHarder to query at scale
Log AnalyticsSIEM hunting, KQL, alertsFast queries, Defender integrationCost requires tuning
Event HubsStream to 3rd-party SIEMVendor-neutral pipelineMore moving parts

CLI - server-level auditing to Storage + Log Analytics:

# Storage
az sql server audit-policy update \
  -g <rg> -s <server-name> \
  --state Enabled \
  --storage-account <storage-name> \
  --retention-days 365

# Log Analytics workspace
az monitor diagnostic-settings create \
  --name "sql-diag" \
  --resource $(az sql server show -g <rg> -n <server-name> --query id -o tsv) \
  --workspace <log-analytics-id> \
  --logs '[{"category":"SQLSecurityAuditEvents","enabled":true}]'

Enable Microsoft Defender for SQL (threat detection):

# Plan on the server (or at subscription scope)
az security pricing create --name SqlServers --tier Standard
# Optional email notifications
az sql server threat-policy update \
  -g <rg> -s <server-name> \
  --state Enabled --email-account-admins Enabled \
  --email-addresses secops@example.com

KQL starter (failed logins spike):

SQLSecurityAuditEvents
| where action_name == "DATABASE AUTHENTICATION FAILED"
| summarize failures = count() by bin(TimeGenerated, 5m), client_ip
| order by failures desc

Hardening details & code you'll actually use

Identity & access (least privilege)

  • Prefer group-based AAD admins over individuals.
  • Use contained AAD users in databases, with explicit GRANTs.
  • Managed Identities for apps; never store connection strings with secrets.
  • Kill legacy: DROP LOGIN for leftover SQL logins after AAD-only is on.

T-SQL pattern:

-- Example app role with minimal perms
CREATE ROLE app_reader;
GRANT SELECT ON SCHEMA::dbo TO app_reader;
ALTER ROLE app_reader ADD MEMBER [app-mi];  -- AAD MI principal

Network - make "public" impossible

  • Public network access = Disabled at the server.
  • One Private Endpoint per region; avoid hairpin across regions.
  • Lock down Approved subnet list as code (Bicep/Terraform).
  • Use Private DNS zones; avoid custom host files.

Auditing & retention

  • 365-730 days in Blob with immutability policy if you have compliance needs.
  • Log Analytics tables to watch: SQLSecurityAuditEvents, AzureDiagnostics.
  • Data volume controls: filter noisy categories or reduce sampling intervals thoughtfully.

Data protection

  • TDE default On; consider Customer-Managed Key (CMK) if required.
  • Always Encrypted for client-side secrets; Dynamic Data Masking for analysts.
  • Least-privileged procs/views > direct table access.

Operations as code

  • Express all of the above with ARM/Bicep/Terraform + Azure Policy.
  • Add Pester/pytest checks that resolve DNS to privatelink.* and verify --enable-ad-only-auth.

Verification tests (don't skip)

  1. Password attempts should fail: try sqlcmd with SQL auth - expect failure.
  2. Public egress blocked: from a random VM on the internet, telnet <server>.database.windows.net 1433 - no route.
  3. Private DNS correct: CNAME -> privatelink.database.windows.net, A -> 10.x address.
  4. Audit events present: run a failed login; confirm event in Storage and Log Analytics within minutes.
  5. Role scoping works: the app MI can read what it should - and nothing else.

Common pitfalls (blunt truths)

  • "Temporary" public access becomes permanent. Don't do it - use a jump host in the VNet.
  • Admin by person, not group: you will lock yourself out when that person leaves.
  • Forgetting DNS: Private Endpoint without Private DNS = random timeouts and "works on my machine."
  • Over-granting db_owner: laziness today, breach path tomorrow. Create roles.
  • Auditing to SIEM only: keep immutable Storage for investigations.
  • Secrets in app settings: use Managed Identity + AAD auth, period.

Example Bicep (snippet)

param serverName string
param rgLocation string
param logAnalyticsId string
param storageId string

resource sql 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: serverName
  location: rgLocation
  properties: {
    publicNetworkAccess: 'Disabled'
    administratorLogin: ''              // none; AAD-only
    restrictOutboundNetworkAccess: 'Enabled'
    azureADOnlyAuthentication: { azureADOnlyAuthentication: true }
  }
}

resource diag 'Microsoft.Insights/diagnosticSettings@2021-05-01-preview' = {
  name: 'sql-diag'
  scope: sql
  properties: {
    workspaceId: logAnalyticsId
    logs: [
      { category: 'SQLSecurityAuditEvents', enabled: true }
    ]
  }
}

resource aud 'Microsoft.Sql/servers/auditingSettings@2021-11-01' = {
  name: '${serverName}/default'
  properties: {
    state: 'Enabled'
    isAzureMonitorTargetEnabled: true
    storageEndpoint: reference(storageId, '2021-09-01', 'Full').primaryEndpoints.blob
    retentionDays: 365
  }
}

Internal link ideas (for your site)

  • "Managed Identities with Azure SQL: End-to-End App Auth"
  • "Designing Private DNS for Azure PaaS: Gotchas and Patterns"
  • "KQL Playbook: Hunting SQL Auth Anomalies in Log Analytics"
  • "Terraform Module: AAD-Only Azure SQL with Private Endpoints"

Conclusion & Takeaways

Zero trust isn't a slogan; it's a set of defaults you refuse to compromise: AAD-only, no public ingress, full auditing. Put these controls in code, verify them continuously, and don't hand out permissions you can't justify. If something breaks because of these settings, fix the app - don't weaken the guardrails.

Bottom line checklist

  • AAD-only enabled; no SQL logins exist
  • Public network access disabled
  • Private Endpoint + Private DNS configured and tested
  • Auditing to Storage (immutable) + Log Analytics
  • Defender for SQL alerts on and routed to SecOps
  • Least-privilege roles; no db_owner for apps
  • IaC + Policy enforcing all of the above

Image prompt

"A clean, modern diagram of an Azure SQL hardening architecture: AAD-only authentication, disabled public endpoint, Private Endpoint to a VNet with Private DNS zone, auditing flowing to Blob Storage (immutable) and Log Analytics workspace, with a zero-trust lock icon - minimalistic, high contrast, isometric style."

Tags

#AzureSQL #ZeroTrust #LeastPrivilege #PrivateEndpoint #AzureAD #Auditing #DefenderForCloud #DataSecurity #CloudArchitecture #DevSecOps

Leave a Comment