Microsoft AzureOverview
Enable auditing on SQL Servers.
Rationale
The Azure platform allows an SQL server to be created as a service. Enabling auditing at the server level ensures that all existing and newly created databases on the SQL server instance are audited. The auditing policy on the SQL database does not override the auditing policy and settings applied on the particular SQL server hosted by the database.
Auditing tracks database events and writes them to an audit log in the Azure storage account. It also helps to maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
Default Value
By default, Auditing is set to Off.
Additional Information
- A server policy applies to all existing and newly created databases on the server.
- If server blob auditing is enabled, it always applies to the database. The database will be audited, regardless of the database auditing settings. Auditing of type table is already deprecated, leaving only type blob available.
- Enabling blob auditing on the database and enabling it on the server does not override or change any of the server blob auditing settings. Both audits will exist side by side. In other words, the database is audited twice in parallel, once by the server policy and once by the database policy.
References
- https://docs.microsoft.com/en-us/azure/security-center/security-center-enable-auditing-on-sql-servers
- https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/get-azurermsqlserverauditing?view=azurermps-5.2.0
- https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverauditingpolicy?view=azurermps-5.2.0
- https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing
- https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-logging-threat-detection#lt-3-enable-logging-for-security-investigation
Remediation guidance
Azure Console
- Open the SQL server using the
Open in Azurebutton - Under
Security, clickAuditing - Click the toggle for
Enable Azure SQL Auditing - Select an audit log destination and select
Save
Azure PowerShell
For each Server, enable auditing and set the retention to at least 90 days.
Log Analystics
Set-AzSqlServerAudit -ResourceGroupName <resourceGroupName> -ServerName -RetentionInDays <numberOfDays> -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/<subscriptionID>/resourceGroups/insightsintegration/providers/Microsoft.OperationalInsights/workspaces/<workspaceName>"
Event Hub
Set-AzSqlServerAudit -ResourceGroupName "<resourceGroupName>" -ServerName "" -EventHubTargetState Enabled -EventHubName "" -EventHubAuthorizationRuleResourceId "<eventHubAuthorizationRuleResourceID>"
Blog Storage
Set-AzSqlServerAudit -ResourceGroupName "<resourceGroupName>" -ServerName "" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/<subscriptionID>/resourceGroups/<resourceGroupName>/providers/Microsoft.Storage/storageAccounts/<storageAccountName>"
Multiple Remediation Paths
Azure
SERVICE-WIDE (RECOMMENDED when many resources are affected): Assign Azure Policy initiatives at management group/subscription scope and trigger remediation tasks.
az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>
ASSET-LEVEL: Apply the resource-specific remediation steps above to the listed non-compliant resources.
PREVENTIVE: Embed Azure Policy checks into landing zones and IaC workflows to block or auto-remediate drift.
References for Service-Wide Patterns
- Azure Policy overview: https://learn.microsoft.com/en-us/azure/governance/policy/overview
- Azure Policy remediation: https://learn.microsoft.com/en-us/azure/governance/policy/how-to/remediate-resources
- Azure Policy initiative structure: https://learn.microsoft.com/en-us/azure/governance/policy/concepts/initiative-definition-structure
Operational Rollout Workflow
Use this sequence to reduce risk and avoid repeated drift.
1. Contain at Service-Wide Scope First (Recommended)
- Azure: assign policy initiatives at management group/subscription scope and run remediation tasks.
az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>
2. Remediate Existing Affected Assets
- Execute the control-specific Console/CLI steps documented above for each flagged resource.
- Prioritize internet-exposed and production assets first.
3. Validate and Prevent Recurrence
- Re-scan after each remediation batch.
- Track exceptions with owner and expiry date.
- Add preventive checks in IaC/CI pipelines.
Query logic
These are the stored checks tied to this control.
Azure SQL Servers without auditing
Connectors
Covered asset types
Expected check: eq []
{
sqlServers(where: { blobAuditingPolicies_NONE: { state: "Enabled" } }) {
...AssetFragment
}
}
