Back to Checks
azure

SQL server has auditing enabled with retention greater than 90 days

sqlserver_auditing_retention_90_days
Service: sqlserverSeverity: medium
OfficialBuilt-inVerified
Categories:loggingforensics-ready
Resource Type:microsoft.sql/servers

Azure SQL Server auditing settings are evaluated to ensure auditing is enabled and log retention is greater than 90 days. It considers the auditing policy state and the configured retention_days value.

Prowler logoProwlerprowler-provider-azure

Risk

Without adequate retention or with auditing disabled, activity trails expire too soon, limiting detection and investigation of unauthorized access, data exfiltration, and privilege abuse. This weakens confidentiality and integrity and slows incident response.

Run this check with Prowler

Run in Prowler Cloud
Run
prowler azure --checks sqlserver_auditing_retention_90_days

Recommendation

Enable server-level auditing and set retention above 90 days, aligned with policy needs. Store logs in tamper-resistant, centralized storage, restrict access with least privilege, and integrate alerting and review. Apply defense in depth with continuous monitoring.

Remediation

CLI

Set-AzSqlServerAudit -ResourceGroupName <example_resource_name> -ServerName <example_resource_name> -RetentionInDays 91 -LogAnalyticsTargetState Enabled -WorkspaceResourceId <example_resource_id>

Terraform

# Enable server-level auditing with retention > 90 days
resource "azurerm_mssql_server_extended_auditing_policy" "audit" {
  server_id          = "<example_resource_id>"
  log_monitoring_enabled = true   # Critical: enable Log Analytics target
  retention_in_days      = 91     # Critical: > 90 days
}

NativeIaC

// Enable server-level auditing with retention > 90 days
resource audit 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  name: '<example_resource_name>/default'
  properties: {
    state: 'Enabled'                         // Critical: turns auditing ON
    retentionDays: 91                        // Critical: > 90 days
    isAzureMonitorTargetEnabled: true        // Critical: send to Log Analytics
    workspaceResourceId: '<example_resource_id>' // Critical: target workspace
  }
}

Other

1In Azure Portal, go to SQL servers and select <example_resource_name>
2Under Security, click Auditing
3Set Auditing to On
4Destination: select Log Analytics workspace and choose your workspace
5Set Retention (days) to 91
6Click Save

Check Code

from prowler.lib.check.models import Check, Check_Report_Azure
from prowler.providers.azure.services.sqlserver.sqlserver_client import sqlserver_client


class sqlserver_auditing_retention_90_days(Check):
    def execute(self) -> Check_Report_Azure:
        findings = []
        for subscription, sql_servers in sqlserver_client.sql_servers.items():
            subscription_name = sqlserver_client.subscriptions.get(
                subscription, subscription
            )
            for sql_server in sql_servers:
                report = Check_Report_Azure(
                    metadata=self.metadata(), resource=sql_server
                )
                report.subscription = subscription
                has_failed = False
                has_policy = False
                for policy in sql_server.auditing_policies:
                    has_policy = True
                    if has_failed:
                        break
                    if policy.state == "Enabled":
                        if policy.retention_days <= 90:
                            report.status = "FAIL"
                            report.status_extended = f"SQL Server {sql_server.name} from subscription {subscription_name} ({subscription}) has auditing retention less than 91 days."
                            has_failed = True
                        else:
                            report.status = "PASS"
                            report.status_extended = f"SQL Server {sql_server.name} from subscription {subscription_name} ({subscription}) has auditing retention greater than 90 days."
                    else:
                        report.status = "FAIL"
                        report.status_extended = f"SQL Server {sql_server.name} from subscription {subscription_name} ({subscription}) has auditing disabled."
                        has_failed = True
                if has_policy:
                    findings.append(report)

        return findings

References