Ensure 'Auditing' Retention is greater than 90 days for SQL Servers

Overview

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

Remediation guidance

From the Azure Portal

Go to SQL servers
For each server instance, under Security, select Auditing
Under the Azure SQL Auditing heading, for Storage, click Advanced 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/"

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 with audit retention lesser than 90 days

Connectors

Microsoft Azure

Covered asset types

SQLServer

Expected check: eq []

{
  sqlServers(
    where: {
      blobAuditingPolicies_NONE: {
        state: "Enabled"
        OR: [{ retentionDays: 0 }, { retentionDays_GT: 90 }]
      }
    }
  ) {
    ...AssetFragment
  }
}