Azure Audit Sqldatabase Threat Detection Alerts Disabled Fix

Triage and Remediation

Remediation

Remediation

Using Console

To remediate the misconfiguration "Threat Detection Alerts Disabled for SQL Databases" in Azure using the Azure console, follow these steps:

Log in to the Azure portal (https://portal.azure.com/).
Navigate to the Azure SQL Database that has the misconfiguration.
Click on the "Security" tab in the left-hand menu.
Click on the "Advanced Data Security" option.
Click on the "Configure advanced data security" button.
In the "Advanced Data Security" blade, toggle the "Threat detection" option to "On".
Select the "Send alerts to" option and provide an email address to receive the alerts.
Set the "Alerts" threshold to the desired level.
Click on the "Save" button to save the changes.

Once you have completed these steps, threat detection alerts will be enabled for the Azure SQL Database and you will receive alerts when any threats are detected.

Using CLI

To remediate the misconfiguration of Threat Detection Alerts Disabled for SQL Databases in AZURE using AZURE CLI, please follow the below steps:

Step 1: Open the AZURE CLI in your system.

Step 2: Log in to your AZURE account using the below command:

az login

Step 3: After logging in, set the subscription where your SQL databases are located using the below command:

az account set --subscription <subscription_name>

Step 4: To enable Threat Detection Alerts for SQL databases, you need to enable the Advanced Threat Protection (ATP) service. You can enable this service by running the following command:

az sql server atp-policy update --resource-group <resource_group_name> --server <server_name> --storage-account <storage_account_name> --storage-key <storage_account_key> --workspace-resource-id <workspace_resource_id> --state Enabled

Note: Replace the placeholders with the actual values of your resource group name, server name, storage account name, storage account key, and workspace resource ID.

Step 5: After enabling the ATP service, you can enable Threat Detection Alerts for your SQL databases by running the following command:

az sql db threat-policy update --resource-group <resource_group_name> --server <server_name> --database <database_name> --state Enabled

Note: Replace the placeholders with the actual values of your resource group name, server name, and database name.

Step 6: Verify that the Threat Detection Alerts are enabled for your SQL databases by running the following command:

az sql db threat-policy show --resource-group <resource_group_name> --server <server_name> --database <database_name>

Note: Replace the placeholders with the actual values of your resource group name, server name, and database name.

Once you follow the above steps, Threat Detection Alerts will be enabled for your SQL databases in AZURE.

Using Python

To remediate the misconfiguration of threat detection alerts being disabled for SQL databases in Azure using Python, you can follow the below steps:

Step 1: Import the necessary libraries and authenticate to Azure using the Azure SDK for Python.

from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.resource import ResourceManagementClient, SubscriptionClient

credential = DefaultAzureCredential()
subscription_client = SubscriptionClient(credential)
subscription = next(subscription_client.subscriptions.list())

sql_client = SqlManagementClient(credential, subscription.subscription_id)
resource_client = ResourceManagementClient(credential, subscription.subscription_id)

Step 2: Get the resource group name and SQL server name where the database is located.

resource_group_name = "your_resource_group_name"
server_name = "your_sql_server_name"

Step 3: Get the list of SQL databases in the specified server.

database_list = sql_client.databases.list_by_server(resource_group_name, server_name)

Step 4: For each database in the list, check if the threat detection policy is enabled. If not, enable it.

for database in database_list:
    database_name = database.name
    database_resource_id = database.id
    
    # Get the current threat detection policy for the database
    threat_detection_policy = sql_client.databases.get_threat_detection_policy(resource_group_name, server_name, database_name)
    
    # Check if the threat detection policy is enabled
    if not threat_detection_policy.state == "Enabled":
        # Enable the threat detection policy
        threat_detection_policy.state = "Enabled"
        threat_detection_policy = sql_client.databases.create_or_update_threat_detection_policy(resource_group_name, server_name, database_name, threat_detection_policy)
        print(f"Threat detection policy enabled for database {database_name}")
    else:
        print(f"Threat detection policy already enabled for database {database_name}")

Step 5: Run the Python script to remediate the misconfiguration.

Note: Make sure to install the necessary libraries using pip before running the script.

Using Terraform

resource "azurerm_mssql_server" "EXISTING_SQL_SERVER" {
  name                         = "EXISTING_SQL_SERVER_NAME"          # replace with your server name
  resource_group_name          = "EXISTING_RESOURCE_GROUP_NAME"      # replace with your RG
  location                     = "EXISTING_LOCATION"                 # replace with region
  version                      = "12.0"
  administrator_login          = "SQL_ADMIN_LOGIN"
  administrator_login_password = "SQL_ADMIN_PASSWORD"
}

# Enable threat detection alerts (Defender for SQL alerts) for all databases on this server
resource "azurerm_mssql_server_security_alert_policy" "sql_server_threat_detection" {
  server_id = azurerm_mssql_server.EXISTING_SQL_SERVER.id

  state                = "Enabled"
  email_account_admins = "Enabled"

  # Comma-separated list of email recipients for threat detection alerts
  email_addresses = [
    "SECURITY_ALERTS_EMAIL_1@example.com",
    "SECURITY_ALERTS_EMAIL_2@example.com",
  ]

  # Optional: set to >0 only if you also configure storage logging for threat detection
  retention_days = 0

  # If you want to store threat detection logs, also set:
  # storage_endpoint           = azurerm_storage_account.LOGS.primary_blob_endpoint
  # storage_account_access_key = azurerm_storage_account.LOGS.primary_access_key

  # Optional: leave empty or specify which alerts to disable; enabling all is more secure
  # disabled_alerts = ["Sql_Injection", "Sql_Injection_Vulnerability"]
}

This change does not replace existing databases or the SQL server; it only creates/updates the server’s security alert policy, which applies to all databases on that server.

To verify, terraform plan should show a new azurerm_mssql_server_security_alert_policy.sql_server_threat_detection resource with state = "Enabled" and the configured email_account_admins / email_addresses, and no planned replacements of your azurerm_mssql_server or azurerm_mssql_database resources.

Triage and Remediation​

Remediation​

Triage and Remediation

Remediation