Description
Azure SQL audit retention determines how far back activity can be investigated. In a Blob Storage audit policy, retention_in_days = 0 means unlimited retention, not that logs are discarded.
Potential impact
Retention shorter than the required investigation period can make past access and changes unavailable for review.
Remediation
Set retention_in_days according to organizational investigation and audit needs. If logs must remain for more than 90 days, choose a longer period and check that storage lifecycle rules do not delete them sooner.
Examples
The examples use a current separate AzureRM audit policy to increase retention from 20 to 95 days. The 95-day value is illustrative, not a universal requirement. Passwords are illustrative.
Before
hcl
resource "azurerm_mssql_server" "example" {
name = "sqlserver"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
version = "12.0"
administrator_login = "mradministrator"
administrator_login_password = "thisIsDog11"
}
resource "azurerm_mssql_server_extended_auditing_policy" "example" {
server_id = azurerm_mssql_server.example.id
blob_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 = false
retention_in_days = 20
}
After
hcl
resource "azurerm_mssql_server" "example" {
name = "sqlserver"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
version = "12.0"
administrator_login = "mradministrator"
administrator_login_password = "thisIsDog11"
}
resource "azurerm_mssql_server_extended_auditing_policy" "example" {
server_id = azurerm_mssql_server.example.id
blob_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 = false
retention_in_days = 95
}