Azure SQL server auditing is disabled

Description

The Azure platform allows a SQL server to be created as a service. Auditing tracks database events and writes them to an audit log in the Azure storage account. It also helps to maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.

We recommend you enable auditing at the server level, ensuring all existing and newly created databases on the SQL server instance are audited.

📘 Note

An auditing policy applied to a SQL database does not override an auditing policy or settings applied on the SQL server where the database is hosted.

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.
3. For each server instance:
   a) Click Auditing.
   b) Set Auditing to On.

CLI Command

To get the list of all SQL Servers, use the following command:
Get-AzureRmSqlServer

To enable auditing for each Server, use the following command:

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

Fix - Buildtime

ARM

• Resource: Microsoft.Sql/servers/databases

{
  "type": "Microsoft.Sql/servers",
  "apiVersion": "2019-06-01-preview",
  "location": "[parameters('location')]",
  "name": "[parameters('sqlServerName')]",
  "identity": "[if(parameters('isStorageBehindVnet'), json('{\"type\":\"SystemAssigned\"}'), json('null'))]",
  "properties": {
    "administratorLogin": "[parameters('sqlAdministratorLogin')]",
    "administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]",
    "version": "12.0"
  },
  "tags": {
    "displayName": "[parameters('sqlServerName')]"
  },
  "resources": [
    {
      "type": "auditingSettings",
      "apiVersion": "2019-06-01-preview",
      "name": "DefaultAuditingSettings",
      "dependsOn": [
        "[parameters('sqlServerName')]",
        "[parameters('storageAccountName')]",
        "[extensionResourceId(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), 'Microsoft.Authorization/roleAssignments/', variables('uniqueRoleGuid'))]"
      ],
      "properties": {
+       "state": "Enabled",
        "storageEndpoint": "[reference(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), '2019-06-01').PrimaryEndpoints.Blob]",
        "storageAccountAccessKey": "[if(parameters('isStorageBehindVnet'), json('null'), listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), '2019-06-01').keys[0].value)]",
        "storageAccountSubscriptionId": "[subscription().subscriptionId]",
        "isStorageSecondaryKeyInUse": false
      }
    }
  ]
}

Terraform

• Resource: azurerm_sql_server, azurerm_mssql_server
• Field: extended_auditing_policy

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
    }
}