Description
Azure SQL server-level auditing records activity in its databases. Review server and database-specific auditing together to ensure the required events are stored.
Potential impact
Without activity records, it can be difficult to trace which accounts accessed or changed data after an incident.
Remediation
Associate and enable an audit policy on the intended server. Configure log destinations, permissions, and retention, then verify that audit events actually arrive.
Examples
The examples use the current separate AzureRM server audit policy to write to Blob Storage. Azure Monitor also requires diagnostic settings on the master database. Passwords are illustrative.
Before
hcl
resource "azurerm_mssql_server" "example" {
name = "mssqlserver"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
version = "12.0"
administrator_login = "mradministrator"
administrator_login_password = "thisIsDog11"
}
After
hcl
resource "azurerm_mssql_server" "example" {
name = "mssqlserver"
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 = 90
}