​
More Info:

Enable auditing for all SQL Databases.

​
Risk Level

Medium

​
Address

Security, Operational Maturity

​
Compliance Standards

HITRUST, SOC2, NISTCSF, PCIDSS

​
Triage and Remediation

​
Remediation

Using Console

To remediate the issue of auditing disabled for SQL databases in Azure, you can follow the below steps:
  1. Open the Azure portal and go to the SQL database that needs to be remediated.
  2. In the left-hand menu, select “Auditing and Threat Detection”.
  3. In the “Auditing and Threat Detection” blade, select “Audit logs”.
  4. In the “Audit logs” blade, click on the “Turn on auditing” button.
  5. In the “Audit logs” blade, select the storage account where the audit logs will be stored.
  6. Click on “Save” to enable auditing for the SQL database.
  7. In the “Auditing and Threat Detection” blade, select “Threat Detection”.
  8. In the “Threat Detection” blade, click on “Enable Threat Detection”.
  9. In the “Threat Detection” blade, select the storage account where the threat detection logs will be stored.
  10. Click on “Save” to enable threat detection for the SQL database.
Once the above steps are completed, auditing and threat detection will be enabled for the SQL database in Azure.

​

To remediate the “Auditing Disabled for SQL Databases” misconfiguration in Azure using Azure CLI, follow these steps:
  1. Open Azure CLI and login to your Azure account using the command:
az login
  1. Once you are logged in, set the default subscription where your SQL databases are located using the command:
az account set --subscription <subscription_name>
  1. Enable auditing for the SQL server by running the following command:
az sql server audit-policy update --state Enabled --storage-account <storage_account_name> --storage-key <storage_account_key> --storage-endpoint <storage_account_endpoint> --retention-days <retention_period> --resource-group <resource_group_name> --server <sql_server_name>
Note: Replace the placeholders with actual values for storage account name, storage account key, storage account endpoint, retention period, resource group name, and SQL server name.
  1. Once the command is executed successfully, auditing will be enabled for the SQL server and all the databases under it.
  2. Verify the status of auditing by running the following command: 
az sql server audit-policy show --resource-group <resource_group_name> --server <sql_server_name>
This command will display the current audit policy for the SQL server and its databases.
  1. Repeat the above steps for all the SQL servers in your Azure environment to ensure that auditing is enabled for all the databases.
By following the above steps, you can remediate the “Auditing Disabled for SQL Databases” misconfiguration in Azure using Azure CLI.
To remediate the issue of auditing disabled for SQL databases in Azure, you can use the following Python code:
  1. First, import the necessary libraries:
from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient
  1. Next, authenticate and create a SQL management client object:
credential = DefaultAzureCredential()
sql_client = SqlManagementClient(credential, subscription_id)
  1. Get the list of SQL servers in your subscription:
servers = sql_client.servers.list()
  1. For each server, get the list of databases and enable auditing for each database:
for server in servers:
    databases = sql_client.databases.list_by_server(resource_group_name, server.name)
    for database in databases:
        database_properties = sql_client.databases.get(resource_group_name, server.name, database.name)
        database_properties.auditing_policy.state = "Enabled"
        database_properties.auditing_policy.is_azure_monitor_target_enabled = True
        sql_client.databases.create_or_update(resource_group_name, server.name, database.name, database_properties)
This code will iterate through all the SQL servers in your subscription, and for each server, it will enable auditing for all the databases and set Azure Monitor as the target. This will remediate the issue of auditing disabled for SQL databases in Azure.