Azure Audit Sqlserver Short Threat Detection Retention Fix

Triage and Remediation

Remediation

Remediation

Using Console

The short threat detection retention period for SQL Servers in Azure can leave you vulnerable to security threats. Here are the steps to remediate it using the Azure console:

1. Open the Azure portal and navigate to the SQL Server that you want to remediate.

2. In the left-hand menu, click on "Advanced Threat Protection".

3. In the "Advanced Threat Protection" blade, click on "Settings" at the top.

4. Under "Data retention", select the desired retention period. Microsoft recommends a retention period of at least 90 days.

5. Click "Save" to apply the changes.

6. Once the retention period is set, you can configure alerts and view threat detection reports to monitor your SQL Server for potential security threats.

By following these steps, you can remediate the short threat detection retention period for SQL Servers in Azure and improve the security of your environment.

Using CLI

The remediation steps for this misconfiguration in Azure using Azure CLI are as follows:

1. Open Azure CLI and login to your Azure account.

2. Run the following command to get a list of all the SQL servers in your Azure account:

az sql server list

3. Identify the SQL server for which you want to increase the threat detection retention period and note down its resource group and name.

4. Run the following command to set the threat detection retention period for the SQL server to 90 days (you can adjust the retention period as per your requirement):

az sql server threat-detection-policy update --resource-group <resource-group-name> --server <server-name> --state Enabled --retention-days 90

5. Verify that the retention period has been updated by running the following command:

az sql server threat-detection-policy show --resource-group <resource-group-name> --server <server-name>

This command will show the current threat detection policy for the SQL server, including the retention period.

By following these steps, you can increase the threat detection retention period for a SQL server in Azure using Azure CLI.

Using Python

The threat detection retention period for SQL servers in Azure is set to a default of 90 days. This means that any log data older than 90 days is automatically deleted. To remediate this, you can use the Azure Python SDK to update the retention period to a longer duration. Here are the steps to follow:

1. Install the Azure Python SDK using pip:

pip install azure-mgmt-monitor

2. Authenticate to your Azure account using the SDK. You can use the following code to authenticate using a service principal:

from azure.common.credentials import ServicePrincipalCredentials

TENANT_ID = 'your_tenant_id'
CLIENT_ID = 'your_client_id'
CLIENT_SECRET = 'your_client_secret'

credentials = ServicePrincipalCredentials(
    client_id=CLIENT_ID,
    secret=CLIENT_SECRET,
    tenant=TENANT_ID
)

3. Connect to the Azure Monitor API using the SDK:

from azure.mgmt.monitor import MonitorManagementClient
from azure.mgmt.monitor.models import DiagnosticSettingsResource

SUBSCRIPTION_ID = 'your_subscription_id'
RESOURCE_GROUP_NAME = 'your_resource_group_name'
WORKSPACE_NAME = 'your_workspace_name'
SQL_SERVER_NAME = 'your_sql_server_name'

monitor_client = MonitorManagementClient(
    credentials,
    SUBSCRIPTION_ID
)

resource_id = f"/subscriptions/{SUBSCRIPTION_ID}/resourceGroups/{RESOURCE_GROUP_NAME}/providers/Microsoft.Sql/servers/{SQL_SERVER_NAME}"
workspace_id = f"/subscriptions/{SUBSCRIPTION_ID}/resourceGroups/{RESOURCE_GROUP_NAME}/providers/Microsoft.OperationalInsights/workspaces/{WORKSPACE_NAME}"

4. Retrieve the current diagnostic settings for the SQL server:

diagnostic_settings = monitor_client.diagnostic_settings.list(resource_id)

5. Update the retention period for the SQL server:

for setting in diagnostic_settings.value:
    if setting.workspace_id == workspace_id:
        setting.logs[0].retention_policy.enabled = True
        setting.logs[0].retention_policy.days = 365
        monitor_client.diagnostic_settings.create_or_update(
            resource_id,
            setting.name,
            DiagnosticSettingsResource(
                id=setting.id,
                name=setting.name,
                type=setting.type,
                location=setting.location,
                tags=setting.tags,
                workspace_id=setting.workspace_id,
                logs=[setting.logs[0]]
            )
        )

In this code, we are updating the retention period to 365 days. You can adjust this value to your desired duration.

6. Run the Python script to update the retention period for your SQL server in Azure.

Using Terraform

resource "azurerm_mssql_server" "sql_server" {
  name                         = "SQL_SERVER_NAME"          # replace with your server name
  resource_group_name          = "RESOURCE_GROUP_NAME"      # replace with your RG
  location                     = "AZURE_REGION"             # e.g. "eastus"
  version                      = "12.0"
  administrator_login          = "ADMIN_USERNAME"
  administrator_login_password = "ADMIN_PASSWORD"

  # ...any other required settings...
}

resource "azurerm_mssql_server_security_alert_policy" "sql_server_threat_detection" {
  resource_group_name  = azurerm_mssql_server.sql_server.resource_group_name
  server_name          = azurerm_mssql_server.sql_server.name
  state                = "Enabled"
  retention_days       = 90                                # must be >= 90 to satisfy the check
  email_account_admins = true

  # optionally specify alert types and emails:
  # disabled_alerts        = []
  # email_addresses        = ["SECURITY_TEAM_EMAIL"]
  # storage_endpoint       = "STORAGE_ACCOUNT_BLOB_ENDPOINT"
  # storage_account_access_key = "STORAGE_ACCOUNT_ACCESS_KEY"
}

This change does not force replacement of the SQL server; it updates the existing threat detection policy in place.

Verification: terraform plan should show an update to azurerm_mssql_server_security_alert_policy.sql_server_threat_detection with retention_days changing from its current value to 90 (or higher, if you choose a larger value).