Data/ML Engineer Blog

Data RDS

Secure by Default

Alex 0

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

Related Story

Leave a Reply

Your email address will not be published. Required fields are marked *