Azure SQL Server Audit Log Retention Is Less Than 91 Days

Audit Logs can help you find suspicious events, unusual activity, and trends. Auditing the SQL server, at the server-level, allows you to track all existing and newly created databases on the instance. This policy identifies SQL servers which do not retain audit logs for more than 90 days. As a best practice, configure the audit logs retention time period to be greater than 90 days.

Policy Details

Policy Subtype
Run, Build
Severity
High
Template Type
Terraform

Build Rules

Azure SQL Server audit log retention is less than 91 days.
JSON Query:
$.resource.*.azurerm_sql_database size greater than 0 and $.resource.*.azurerm_sql_database[*].*[*].threat_detection_policy size greater than 0 and ($.resource.*.azurerm_sql_database[*].*[*].threat_detection_policy[*].retention_days anyNull or $.resource.*.azurerm_sql_database[*].*[*].threat_detection_policy[?( @.retention_days<91 )] size greater than 0)
Recommendation:
Recommended solution having the audit log retention for more than 90 days.
It is recommended to have Azure SQL server audit logs retention to be more than 90 days. Please make sure if your template have "retention_days" set to anything greater than 90 under "threat_detection_policy" block.
For example:
"threat_detection_policy": [ { "email_addresses": [ "dbgrl93@gmail.com" ], "retention_days": 91, "state": "Enabled" } ]

Run Rule Recommendation

  1. Log in to the Azure Portal.
  2. Select 'SQL servers'.
  3. Select the SQL server instance you want to modify.
  4. Select 'Auditing', and verify that 'Auditing' is 'On'.
  5. Select 'Storage Details' and select the 'Storage account' in which to save the logs.
  6. Set the 'Retention (days)' to 0 (indefinite) or greater than 90 days.
  7. Select 'OK' and 'Save' your changes.

Compliance

There are 3 standards that are applicable to this policy:
  • CIS v1.1 (Azure)
  • PIPEDA
  • CCPA 2018

Recommended For You