SQL Server Auditing Not Enabled on Azure: Why It Matters and How to Fix It

Auditing is one of those settings that nobody misses until they need it. When a database is compromised or someone runs a query they should not have, the first question is always "what happened, and when?" Without an audit trail, that question has no answer. The Lensix sql_noauditing check looks for Azure SQL Servers where blob auditing has not been enabled, so you can close the gap before an incident forces the issue.

---

What this check detects

The check inspects each Azure SQL logical server in your subscription and reports any server where the auditing setting is in a disabled state. In Azure terms, this is the server blob auditing policy, which controls whether database events are captured and written to a destination such as an Azure Storage account, a Log Analytics workspace, or an Event Hub.

Auditing can be configured at two levels: the server and the individual database. Server-level auditing applies to every database hosted on that server, including new ones created later. This check focuses on the server policy because that is the setting that gives you blanket coverage. A server with auditing off is the riskiest configuration because nothing is being recorded by default.

---

Why it matters

An audit log is the record of who did what to your data. Auditing captures events like successful and failed logins, schema changes, permission grants, and the actual queries hitting your tables. Without it, you lose visibility in three areas that tend to matter most.

Incident response falls apart

Imagine a credential leaks and an attacker connects to your database. With auditing enabled, you can reconstruct exactly which queries ran, whether data was exfiltrated, and which rows were touched. With auditing off, you are left guessing. You cannot prove the scope of a breach, and in many cases you cannot even prove a breach happened at all. That uncertainty turns a contained incident into a worst-case disclosure obligation.

Compliance failures

Frameworks like PCI DSS, HIPAA, SOC 2, and ISO 27001 expect database access to be logged and reviewable. PCI DSS Requirement 10, for example, calls for tracking all access to cardholder data. An auditor who finds auditing disabled on a production SQL Server will flag it, and you will be writing a remediation plan instead of passing the control.

Insider threats go undetected

Not every threat is external. A privileged user dumping customer records or quietly altering financial data leaves no trace if auditing is off. The audit log is often the only thing that lets you spot abuse by someone who already has legitimate access.

---

How to fix it

You have a few ways to enable server-level auditing. Pick whichever fits your workflow, but treat the IaC approach as the goal so the fix sticks.

Option 1: Azure Portal

1. Open the SQL server resource (not an individual database) in the Azure Portal.
2. Under Security, select Auditing.
3. Toggle Enable Azure SQL Auditing to on.
4. Choose at least one destination: Storage, Log Analytics, or Event Hub.
5. For a storage destination, select the account and set a retention period so logs are not kept forever (or never expire if you need that).
6. Click Save.

Option 2: Azure CLI

This enables auditing on the server and ships logs to both a storage account and a Log Analytics workspace.

# Variables
RG="my-resource-group"
SERVER="my-sql-server"
STORAGE="myauditstorageacct"
LAW_ID="/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.OperationalInsights/workspaces/<workspace>"

# Enable server blob auditing to a storage account
az sql server audit-policy update \
  --resource-group "$RG" \
  --name "$SERVER" \
  --state Enabled \
  --blob-storage-target-state Enabled \
  --storage-account "$STORAGE" \
  --retention-days 90

# Add Log Analytics as a destination as well
az sql server audit-policy update \
  --resource-group "$RG" \
  --name "$SERVER" \
  --state Enabled \
  --log-analytics-target-state Enabled \
  --log-analytics-workspace-resource-id "$LAW_ID"

Option 3: PowerShell

Set-AzSqlServerAudit `
  -ResourceGroupName "my-resource-group" `
  -ServerName "my-sql-server" `
  -BlobStorageTargetState Enabled `
  -StorageAccountResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Storage/storageAccounts/myauditstorageacct" `
  -RetentionInDays 90

Option 4: Terraform

For storage-based auditing on a server, the relevant resource is azurerm_mssql_server_extended_auditing_policy.

resource "azurerm_mssql_server_extended_auditing_policy" "audit" {
  server_id                               = azurerm_mssql_server.example.id
  storage_endpoint                        = azurerm_storage_account.audit.primary_blob_endpoint
  storage_account_access_key              = azurerm_storage_account.audit.primary_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 90
}

If you prefer destination-based auditing through a diagnostic setting (Log Analytics or Event Hub), use azurerm_monitor_diagnostic_setting with the SQLSecurityAuditEvents log category instead, and omit the storage key from your config.

Verify the fix

az sql server audit-policy show \
  --resource-group "$RG" \
  --name "$SERVER" \
  --query "{state:state, blobTarget:blobStorageTargetState, lawTarget:isAzureMonitorTargetEnabled}"

You want state to read Enabled and at least one target showing as active.

---

How to prevent it from happening again

Fixing one server is easy. Keeping every server compliant as your estate grows is the real work. Bake the requirement into policy and pipelines so a non-audited server cannot quietly appear.

Azure Policy

Azure ships built-in policy definitions for exactly this. Assign the definition named "Auditing on SQL server should be enabled" to your management group or subscription. For stronger enforcement, use a DeployIfNotExists policy that automatically configures auditing on any server that lacks it.

# Assign the built-in audit policy by its definition ID
az policy assignment create \
  --name "require-sql-auditing" \
  --display-name "Require SQL Server auditing" \
  --scope "/subscriptions/<sub-id>" \
  --policy "a6fb4358-5bf4-4ad7-ba82-2cd2f41ceae9"

CI/CD gates

Stop misconfigured infrastructure before it deploys. Run a static analysis tool against your Terraform or Bicep in the pipeline and fail the build if auditing is missing.

# Scan Terraform with Checkov in a pipeline step
checkov -d ./infra --framework terraform \
  --check CKV_AZURE_24,CKV2_AZURE_27

Continuous monitoring

Policy and CI catch what you know about. A drift can still happen through manual changes, a rolled-back deployment, or a resource imported from another team. Running the Lensix sql_noauditing check on a schedule gives you a continuous backstop that flags any server that loses auditing, regardless of how it got that way.

---

Best practices

• Enable auditing at the server level, not per database. Server-level coverage extends to every current and future database automatically, so nothing slips through when a new database is created.
• Send audit data to a centralized, write-restricted destination. Log Analytics or a dedicated logging subscription keeps logs out of reach of an attacker who compromises the database itself.
• Set a retention period that matches your compliance needs. PCI DSS expects a year of log history with at least three months immediately available. Configure retention accordingly rather than relying on defaults.
• Pair auditing with Microsoft Defender for SQL. Auditing records what happened; Defender adds threat detection that alerts on anomalous activity like SQL injection or access from unusual locations.
• Review the logs. Audit data nobody looks at is just storage cost. Build alerts on failed logins and privilege changes, and query the SQLSecurityAuditEvents table periodically.
• Restrict who can change the audit policy. Disabling auditing should require elevated privileges and ideally trigger an alert of its own.

SQLSecurityAuditEvents
| where TimeGenerated > ago(1d)
| where Action_Id == "LGIF"   // failed login
| summarize count() by ServerName_s, client_ip_s, bin(TimeGenerated, 1h)
| order by count_ desc

Auditing is cheap to turn on and expensive to be without. Enable it everywhere, send the logs somewhere safe, and let policy and monitoring keep it that way.