SQL Server auditing should be enabled

Description

The Azure platform allows a SQL server to be created as a service. Enabling auditing at the server level ensures that all existing and newly created databases on the SQL server instance are audited. Auditing policy applied on the SQL database does not override auditing policy and settings applied on the particular SQL server where the database is hosted.

Remediation Steps

Azure Portal

  • Navigate to SQL Servers.

  • Select the SQL server.

  • In the left navigation in the Security section, select Auditing.

  • Set Auditing to On.

PowerShell

  • To enable auditing for SQL Server, get a list of all SQL servers:

Get-AzureRmSqlServer
  • Enable auditing for each server:

Set-AzureRmSqlServerAuditingPolicy -ResourceGroupName <resource group name> -ServerName <server name> -AuditType <audit type> -StorageAccountName <storage account name>

Azure Resource Manager

{
  "properties": {
    "state": "Enabled"
  }
}

Example Configuration

  • Example of a Microsoft.Sql servers:

{
"type": "Microsoft.Sql/servers",
"apiVersion": "2014-04-01",
"properties": {
  "state": "Enabled"
  }
# other required fields here
}
  • Example of Microsoft.Sql servers/auditingSettings

{
"type": "Microsoft.Sql/servers/auditingSettings",
"apiVersion": "2021-05-01-preview",
"properties":{
  "state": "Enabled"
  }
# other required fields here
}

Terraform

Example Configuration

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
  }
  # other required fields here
}