Description:
SQL Server Audit Retention should be configured to be greater than 90 days.SQL Server Audit Retention should be configured to be greater than 90 days to allow to collect the necessary amount of audit data useful to check for anomalies and potential security breaches, or misuse of information and access to your SQL database.
Rationale:
Audit Logs can be used to check for anomalies and give insight into suspected breaches or misuse of information and access.
Impact:
A log data retention period of 90 days or more, should allow you to collect the necessary amount of audit data useful to check for anomalies and potential security breaches
Default Value:
By default, SQL Server audit storage is disabled.
Audit:
From Azure Console
Go to SQL servers
For each server instance
Click on Auditing
Select Storage Details
Ensure Retention (days) setting greater than 90 days
Using Azure PowerShell
Get the list of all SQL Servers
1Get-AzureRmSqlServerFor each Server
1Get-AzureRmSqlServerAuditing -ResourceGroupName <resource group name> - ServerName <server name>Ensure that RetentionInDays is set to more than or equal to 90
Remediation:
From Azure Console
Go to SQL servers
For each server instance
Click on Auditing
Select Storage Details
Set Retention (days) setting greater than 90 days
Select OK
Select Save
Using Azure PowerShell
For each Server, set retention policy for more than or equal to 90 days
1set-AzureRmSqlServerAuditing -ResourceGroupName <resource group name> - ServerName <server name> -RetentionInDays <Number of Days to retain the audit logs, should be 90days minimum>Backout Plan:
Go to SQL servers
For each server instance
Click on Auditing
Ensure that SQL Server audit storage is disabled
Using Azure PowerShell
For each Server, set retention policy for more than or equal to 90 days
1set-AzureRmSqlServerAuditing -ResourceGroupName <resource group name> - ServerName <server name> -Re