Azure SQL server audit log retention is less than 91 days

Description

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

We recommend you configure SQL server audit retention to be greater than 90 days.

Fix - Runtime

Azure Portal

To change the policy using the Azure Portal, follow these steps:

1. Log in to the Azure Portal at https://portal.azure.com.
   2 Navigate to SQL servers.
2. For each server instance:
   a) Click Auditing.
   b) Select Storage Details.
   c) Set Retention (days )to greater than 90 days.
   d) Click OK.
   e) Click Save.

CLI Command

To set the retention policy for more than or equal to 90 days, for each server, use the following command:

set-AzureRmSqlServerAuditing 
-ResourceGroupName <resource group name> 
-ServerName <server name> 
-RetentionInDays <Number of Days to retain the audit logs, should be 90days minimum>

Fix - Buildtime

Terraform

• Resource: azurerm_sql_server, azurerm_mssql_server
• Argument: retention_in_days*

resource "azurerm_sql_server" "example" {
    ...
    extended_auditing_policy {
      storage_endpoint            = azurerm_storage_account.example.primary_blob_endpoint
      storage_account_access_key  = azurerm_storage_account.example.primary_access_key
      storage_account_access_key_is_secondary = true
 +       retention_in_days                       = <90 or greater>
    }
}