4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days'

Information

SQL Server Audit Retention should be configured to be greater than 90 days.

Rationale:

Audit Logs can be used to check for anomalies and give insight into suspected breaches or misuse of information and access.

Solution

From Azure Portal

Go to SQL servers

For each server instance

Click on Auditing

If storage is selected, expand Advanced properties

Set the Retention (days) setting greater than 90 days or 0 for unlimited retention.

Select Save

From Powershell
For each Server, set retention policy for more than or equal to 90 days
Log Analytics Example

Set-AzSqlServerAudit -ResourceGroupName <resource group name> -ServerName <SQL Server name> -RetentionInDays <Number of Days to retain the audit logs, should be 90days minimum> -LogAnalyticsTargetState Enabled -WorkspaceResourceId '/subscriptions/<subscription ID>/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/<workspace name>

Event Hub Example

Set-AzSqlServerAudit -ResourceGroupName '<resource group name>' -ServerName '<SQL Server name>' -EventHubTargetState Enabled -EventHubName
'<Event Hub name>' -EventHubAuthorizationRuleResourceId '<Event Hub Authorization Rule Resource ID>'

Blob Storage Example*

Set-AzSqlServerAudit -ResourceGroupName '<resource group name>' -ServerName '<SQL Server name>' -BlobStorageTargetState Enabled
-StorageAccountResourceId '/subscriptions/<subscription_ID>/resourceGroups/<Resource_Group>/providers/Microsoft.Stora
ge/storageAccounts/<Storage Account name>'

Default Value:

By default, SQL Server audit storage is disabled.

See Also

https://workbench.cisecurity.org/files/4052