Azure Audit Sqldatabase Threat Detection Alerts

Triage and Remediation

Remediation

Remediation

Using Console

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

Log in to the Azure portal and navigate to the SQL database that has the threat detection alerts disabled.
Click on the "Security" tab in the left-hand menu.
Under the "Threat detection" section, click on the "Advanced Threat Protection settings" link.
In the "Advanced Threat Protection settings" window, click on the "SQL Advanced Threat Protection" tab.
Click on the "Configure advanced threat protection" button.
In the "Configure advanced threat protection" window, select the checkbox next to "Enable SQL advanced threat protection".
Choose the storage account where you want to store the alerts. If you don't have a storage account, you can create a new one by clicking on the "Create new" button.
Click on the "Save" button to enable the threat detection alerts for the SQL database.
You can also configure the alert policy settings by clicking on the "Alert policies" tab in the "Advanced Threat Protection settings" window.
In the "Alert policies" tab, you can configure the severity level, email notification, and other settings for the threat detection alerts.
Click on the "Save" button to save the alert policy settings.

With these steps, you have successfully remediated the "Threat Detection Alerts Disabled for SQL Databases" misconfiguration in Azure.

Using CLI

The following are the step-by-step instructions to remediate the "Threat Detection Alerts Disabled for SQL Databases" misconfiguration in Azure using Azure CLI:

Open the Azure CLI on your local machine or in the Azure portal.
Login to your Azure account using the following command:
```
az login
```
Once you are logged in, set the subscription that you want to work with:
```
az account set --subscription <subscription_id>
```

Check the current status of the Threat Detection Alerts for the SQL Database using the following command:

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

If the Threat Detection Alerts are disabled, enable them using the following command:

az sql db threat-policy update --state Enabled --email-address <email_address> --retention-day 30 --resource-group <resource_group_name> --server <server_name> --database <database_name>

Note: Replace <email_address> with the email address where you want to receive the alerts.

Verify that the Threat Detection Alerts are enabled by running the following command:
```
az sql db threat-policy show --resource-group <resource_group_name> --server <server_name> --database <database_name>
```
This command should return the current status of the Threat Detection Alerts for the SQL Database.

By following the above steps, you can remediate the "Threat Detection Alerts Disabled for SQL Databases" misconfiguration in Azure using Azure CLI.

Using Python

To remediate the "Threat Detection Alerts Disabled for SQL Databases" misconfiguration in Azure using Python, you can use the Azure SDK for Python and follow these steps:

Install the Azure SDK for Python using pip:

pip install azure-mgmt-resource azure-mgmt-sql

Authenticate with Azure using your credentials:

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

credential = DefaultAzureCredential()
subscription_id = '<your-subscription-id>'

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

Get a list of all SQL servers in your subscription:

sql_servers = sql_client.servers.list_by_subscription()

for server in sql_servers:
    server_name = server.name
    server_resource_group = server.resource_group

For each SQL server, check if Threat Detection is enabled:

for server in sql_servers:
    server_name = server.name
    server_resource_group = server.resource_group

    server_details = sql_client.servers.get(server_resource_group, server_name)
    threat_detection_enabled = server_details.security_policy.security_alert_policy.state == 'Enabled'

If Threat Detection is not enabled, enable it:

from azure.mgmt.sql.models import SecurityAlertPolicy, ServerSecurityAlertPolicy

if not threat_detection_enabled:
    # Create a new security alert policy with Threat Detection enabled
    security_alert_policy = SecurityAlertPolicy(
        state='Enabled',
        disabled_alerts=[],
        email_account_admins=True,
        email_addresses=[],
        retention_days=30,
        storage_account_access_key='<your-storage-account-access-key>',
        storage_endpoint='<your-storage-account-endpoint>'
    )

    server_security_alert_policy = ServerSecurityAlertPolicy(security_alert_policy=security_alert_policy)

    # Update the server with the new security alert policy
    sql_client.servers.create_or_update_security_alert_policy(server_resource_group, server_name, server_security_alert_policy)

Run the script periodically to ensure that Threat Detection remains enabled for all SQL servers in your subscription.

Note: Replace <your-subscription-id>, <your-storage-account-access-key>, and <your-storage-account-endpoint> with your own values.

Using Terraform

resource "azurerm_mssql_database" "SQL_DATABASE" {
  name           = "SQL_DATABASE_NAME"          # replace with your database name
  server_id      = azurerm_mssql_server.SQL_SERVER.id
  collation      = "SQL_Latin1_General_CP1_CI_AS"
  sku_name       = "GP_Gen5_2"
  max_size_gb    = 32
  # ...other required arguments...
}

resource "azurerm_mssql_database_security_alert_policy" "SQL_DB_THREAT_DETECTION" {
  resource_group_name = "RESOURCE_GROUP_NAME"      # replace with your resource group
  server_name         = azurerm_mssql_server.SQL_SERVER.name
  database_name       = azurerm_mssql_database.SQL_DATABASE.name

  state = "Enabled"

  # Ensure alerts are emailed to these addresses
  email_addresses = [
    "SECURITY_TEAM_EMAIL_1@example.com",           # replace with real email(s)
    "SECURITY_TEAM_EMAIL_2@example.com",
  ]

  # Optional: also send to Azure SQL server/account admins
  email_account_admins = true

  # Optional: configure storage for alert logs (replace with your storage account details)
  storage_endpoint           = azurerm_storage_account.LOG_STORAGE.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.LOG_STORAGE.primary_access_key

  # Optional: retention for threat detection logs
  retention_days = 30
}

This change updates/creates the database security alert policy in place; it does not force replacement of the SQL database itself.

Verification: terraform plan should show an azurerm_mssql_database_security_alert_policy being created or updated with state = "Enabled" and the specified email_addresses list.

Triage and Remediation​

Remediation​

Triage and Remediation

Remediation