Profile Applicability:

  • Level 1

Description:
 Audit retention should be configured to retain logs for more than 90 days. This setting ensures that audit logs are available for security investigations, compliance auditing, and troubleshooting for an adequate period.

Rationale:
 Audit logs are essential for identifying anomalies, understanding user activity, and responding to potential security breaches. By retaining audit logs for a sufficient period (greater than 90 days), organizations can comply with various regulatory requirements and have sufficient history to support forensic investigations.

Impact:

  • Pros:

    • Helps in compliance with auditing and regulatory requirements.

    • Provides sufficient data for investigating security incidents or unauthorized activities.

  • Cons:

    • Retaining logs for a longer period increases storage requirements.

    • High-traffic databases may generate a large volume of logs, requiring effective management.

Default Value:

By default, audit log retention is set to 90 days.

Pre-requisites:
 Ensure that your SQL server auditing is properly configured, and storage resources are in place to handle the retention of audit logs.

Remediation

Test Plan:

Using Azure Portal:

  1. Log in to the Azure Portal: https://portal.azure.com.

  2. Navigate to SQL servers.

  3. For each server, click on Auditing.

  4. If storage is selected, expand Advanced properties.

  5. Ensure that the Retention (days) setting is configured to greater than 90 days.

Using PowerShell:
 1. Run the following command to verify the retention setting:

Get-AzSqlServerAudit -ResourceGroupName <resource-group-name> -ServerName <server-name>

  • Ensure that RetentionInDays is set to more than 90.

Implementation Plan:

Using Azure Portal:

  1. Navigate to SQL servers in the Azure Portal.

  2. Select the SQL server and click on Auditing.

  3. Expand Advanced properties under storage settings.

  4. Set Retention (days) to greater than 90.

  5. Click Save.

Using PowerShell:
 1. To configure retention to greater than 90 days, run:

Set-AzSqlServerAudit -ResourceGroupName <resource-group-name> -ServerName <server-name> -RetentionInDays <desired-number-of-days> -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/<subscription-id>/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/<workspace-name>"


Backout Plan:

Using Azure Portal:

  1. Go to SQL servers.

  2. For each server, navigate to Auditing and modify the Retention (days) to the desired value, or revert to the default 90 days if needed.

Using PowerShell:
1.  To revert to 90 days, run:

Set-AzSqlServerAudit -ResourceGroupName <resource-group-name> -ServerName <server-name> -RetentionInDays 90


References: