> ## Documentation Index > Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # Auditing Disabled for SQL Databases ### More Info: Enable auditing for all SQL Databases. ### Risk Level Medium ### Address Operational Maturity, Security ### Compliance Standards * APRA CPS 234 (Australia) * BSI C5 (Germany) * Brazil LGPD * CCPA / CPRA (California) * CIS Critical Security Controls v8 * CMMC 2.0 * CSA Cloud Controls Matrix v4 * DPDPA * Digital Operational Resilience Act (EU) * Essential 8 * HITRUST CSF * ISO/IEC 27017 * ISO/IEC 27018 * ISO/IEC 27701 * KSA PDPL * MAS Technology Risk Management (Singapore) * MITRE ATT\&CK (Cloud) * NIS2 Directive * NIST CSF * NIST SP 800-171 * NYDFS 23 NYCRR 500 * PCI * SOC2 * SWIFT Customer Security Controls Framework * Sarbanes-Oxley IT General Controls * UK NCSC Cyber Assessment Framework ### Triage and Remediation ### Remediation 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 ``` 2. Once you are logged in, set the default subscription where your SQL databases are located using the command: ``` az account set --subscription ``` 3. Enable auditing for the SQL server by running the following command: ``` az sql server audit-policy update --state Enabled --storage-account --storage-key --storage-endpoint --retention-days --resource-group --server ``` 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. 4. Once the command is executed successfully, auditing will be enabled for the SQL server and all the databases under it. 5. Verify the status of auditing by running the following command: ``` az sql server audit-policy show --resource-group --server ``` This command will display the current audit policy for the SQL server and its databases. 6. 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: ```python theme={null} from azure.identity import DefaultAzureCredential from azure.mgmt.sql import SqlManagementClient ``` 2. Next, authenticate and create a SQL management client object: ```python theme={null} credential = DefaultAzureCredential() sql_client = SqlManagementClient(credential, subscription_id) ``` 3. Get the list of SQL servers in your subscription: ```python theme={null} servers = sql_client.servers.list() ``` 4. For each server, get the list of databases and enable auditing for each database: ```python theme={null} 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. ```hcl theme={null} resource "azurerm_mssql_database" "SQL_DATABASE" { name = "SQL_DATABASE_NAME" # replace with your database name server_id = azurerm_mssql_server.SQL_SERVER.id sku_name = "GP_S_Gen5_2" # replace with your desired SKU collation = "SQL_LATIN1_GENERAL_CP1_CI_AS" max_size_gb = 32 zone_redundant = false } # Storage account used as the audit log target resource "azurerm_storage_account" "AUDIT_STORAGE" { name = "AUDITSTORAGEACCOUNT" # globally-unique name resource_group_name = "RESOURCE_GROUP_NAME" # replace location = "REGION_NAME" # replace account_tier = "Standard" account_replication_type = "LRS" } # Enable auditing for the SQL Database resource "azurerm_mssql_database_extended_auditing_policy" "SQL_DB_AUDIT" { database_id = azurerm_mssql_database.SQL_DATABASE.id storage_endpoint = azurerm_storage_account.AUDIT_STORAGE.primary_blob_endpoint storage_account_access_key = azurerm_storage_account.AUDIT_STORAGE.primary_access_key storage_account_access_key_is_secondary = false # Optional fine‑tuning; adjust as needed retention_in_days = 90 } # If you want auditing at the server level for all current and future DBs: resource "azurerm_mssql_server_extended_auditing_policy" "SQL_SERVER_AUDIT" { server_id = azurerm_mssql_server.SQL_SERVER.id storage_endpoint = azurerm_storage_account.AUDIT_STORAGE.primary_blob_endpoint storage_account_access_key = azurerm_storage_account.AUDIT_STORAGE.primary_access_key storage_account_access_key_is_secondary = false retention_in_days = 90 } ``` Enabling these auditing policies does not force replacement of the SQL database or server; it’s a safe in‑place change. For verification, `terraform plan` should show creation (or update) of `azurerm_mssql_database_extended_auditing_policy.SQL_DB_AUDIT` (and optionally `azurerm_mssql_server_extended_auditing_policy.SQL_SERVER_AUDIT`) with the desired storage endpoint and retention settings, and no `destroy` of the database itself.