Azure SQL Server auditing policy 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.

Code Example

{
  "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
      }
    }
  ]
}

Remediation

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

. Log in to the Azure Portal at https://portal.azure.com.

. Navigate to * SQL servers*.

. 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 & lt;resource group name> -ServerName & lt;server name> -AuditType & lt;audit type> -StorageAccountName & lt;storage account name>

=== Fix - Buildtime

ARM

• Resource: Microsoft.Sql/servers/databases

Rule Details

Field	Value
ID	IAC-0534
Severity	LOW
IaC Type	arm
Frameworks	ARM, Terraform, Bicep, TerraformPlan
Checkov ID	CKV_AZURE_23

References

• Checkov Rule

---