Microsoft AzureOverview
SQL Server Audit Retention should be configured to be greater than 90 days.
Rationale
Audit Logs can check for anomalies and give insight into suspected breaches or misuse of information and access.
Default Value
By default, SQL Server audit storage is disabled.
References
- https://learn.microsoft.com/en-us/azure/sql-database/sql-database-auditing
- https://learn.microsoft.com/en-us/powershell/module/azurerm.sql/get-azurermsqlserverauditing?view=azurermps-5.2.0
- https://learn.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverauditing?view=azurermps-5.2.0
- https://learn.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-logging-threat-detection#lt-6-configure-log-storage-retention
Remediation guidance
From the Azure Portal
- Go to
SQL servers - For each server instance, under
Security, selectAuditing - Under the
Azure SQL Auditingheading, forStorage, clickAdvanced properties - Set
Retention (days)to 90 or more, or 0 for infinite - Select
Save
Using Azure PowerShell
For each Server, set the retention policy to more than or equal to 90 days.
Log Analytics Example:
Set-AzSqlServerAudit -ResourceGroupName <resource_group_name> `
-ServerName `
-RetentionInDays <number_greater_than_90> `
-LogAnalyticsTargetState Enabled `
-WorkspaceResourceId "/subscriptions/<subscription_ID>/resourceGroups/insightsintegration/providers/Microsoft.OperationalInsights/workspaces/<workspace_name>
Event Hub Example:
Set-AzSqlServerAudit -ResourceGroupName <resource_group_name> `
-ServerName `
-EventHubTargetState Enabled
-EventHubName <event_Hub name>
-EventHubAuthorizationRuleResourceId ""
Blob Storage Example:
Set-AzSqlServerAudit -ResourceGroupName <resource_group_name> `
-ServerName `
-RetentionInDays <number_greater_than_90> `
-BlobStorageTargetState Enabled
-StorageAccountResourceId "/subscriptions/<subscription_ID>/resourceGroups//providers/Microsoft.Storage/storageAccounts/"
Service-wide remediation
Recommended when many resources are affected: fix the platform baseline first so new resources inherit the secure setting, then remediate the existing flagged resources in batches.
Azure
Use management group or subscription Azure Policy assignments, remediation tasks where supported, landing-zone standards, and IaC modules so drift is prevented at scale.
Operational rollout
- Fix the baseline first at the account, subscription, project, cluster, or tenant scope that owns this control.
- Remediate the currently affected resources in batches, starting with internet-exposed and production assets.
- Re-scan and track approved exceptions with an owner and expiry date.
Query logic
These are the stored checks tied to this control.
Azure SQL Servers with audit retention lesser than 90 days
Connectors
Covered asset types
Expected check: eq []
{
sqlServers(
where: {
blobAuditingPolicies_NONE: {
state: "Enabled"
OR: [{ retentionDays: 0 }, { retentionDays_GT: 90 }]
}
}
) {
...AssetFragment
}
}
