Azure Audit Monitor Sqlserver Firewall Rule Alert Delete Fix

Triage and Remediation

Remediation

Remediation

Using Console

To remediate the misconfiguration "Ensure Activity Log Alert exists for Delete SQL Server Firewall Rule" in Azure using Azure console, follow the below steps:

Login to Azure portal (https://portal.azure.com/).
Navigate to the SQL server for which you want to create the activity log alert.
Under the Security section, click on "Firewalls and virtual networks".
Click on the "Activity Log Alerts" tab.
Click on the "Add Activity Log Alert" button.
In the "Create Activity Log Alert" window, provide the following details:

a. Name: Provide a name for the alert.

b. Description: Provide a description for the alert.

c. Subscription: Select the subscription in which you want to create the alert.

d. Resource Group: Select the resource group in which the SQL server is located.

e. Resource Type: Select "Microsoft.Sql/servers/firewallRules" from the dropdown.

f. Resource Name: Select the name of the SQL server for which you want to create the alert.

g. Alert criteria: Under the "Alert criteria" section, select "Delete" from the "Operation Name" dropdown.
Under the "Actions" section, select the action you want to perform when the alert is triggered. You can choose to send an email or a webhook notification.
Click on the "Create Alert" button to create the activity log alert.

Once the activity log alert is created, you will receive a notification whenever a firewall rule is deleted from the SQL server.

Using CLI

To remediate the misconfiguration "Ensure Activity Log Alert exists for Delete SQL Server Firewall Rule" for AZURE using AZURE CLI, you can follow the below steps:

Step 1: Login to AZURE CLI

az login

Step 2: Check if an activity log alert exists for delete SQL server firewall rule

az monitor activity-log alert list --query "[?contains(name, 'Delete SQL Server Firewall Rule')]"

Step 3: If the output of the above command is empty, create an activity log alert for delete SQL server firewall rule

az monitor activity-log alert create --name "Delete SQL Server Firewall Rule Alert" --description "Alert for Delete SQL Server Firewall Rule" --condition "category=Administrative and operationName=Microsoft.Sql/servers/firewallRules/delete" --action-group "/subscriptions/<subscription-id>/resourceGroups/<resource-group-name>/providers/Microsoft.Insights/actionGroups/<action-group-name>"

Note: Replace the <subscription-id>, <resource-group-name>, and <action-group-name> with your actual values.

Step 4: Verify the activity log alert has been created successfully

az monitor activity-log alert list --query "[?contains(name, 'Delete SQL Server Firewall Rule')]"

This should remediate the misconfiguration "Ensure Activity Log Alert exists for Delete SQL Server Firewall Rule" for AZURE using AZURE CLI.

Using Python

To remediate this misconfiguration in Azure, we need to create an Activity Log alert for Delete SQL Server Firewall Rule. Here are the step-by-step instructions to do this using Python:

Install the Azure SDK for Python using the following command:
```
pip install azure-mgmt-monitor
```

Import the necessary modules in your Python script:

from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.monitor import MonitorManagementClient
from azure.mgmt.monitor.models import AlertRuleResource, RuleCondition, \
                                        RuleDataSource, RuleMetricDataSource, \
                                        ThresholdRuleCondition
from azure.mgmt.resource import ResourceManagementClient

Create a Service Principal and assign the required role to it. You can follow the instructions mentioned in this link to create a Service Principal.

Authenticate using the Service Principal credentials:

credentials = ServicePrincipalCredentials(
     client_id='<your-client-id>',
     secret='<your-client-secret>',
     tenant='<your-tenant-id>'
 )

Create a Resource Management client object to get the resource group and SQL Server details:

resource_client = ResourceManagementClient(credentials, subscription_id)
resource_group = '<your-resource-group>'
server_name = '<your-sql-server-name>'

Create a Monitor Management client object to create the Activity Log alert:
```
monitor_client = MonitorManagementClient(credentials, subscription_id)
```

Create a data source for the alert:

data_source = RuleDataSource(
     resource_uri=f"/subscriptions/{subscription_id}/resourceGroups/{resource_group}/providers/Microsoft.Sql/servers/{server_name}/firewallRules",
     resource_provider_name="Microsoft.Sql",
     resource_type="firewallRules"
 )

Create a metric data source for the alert:

metric_data_source = RuleMetricDataSource(
     metric_name="DeleteFirewallRule",
     resource_uri=f"/subscriptions/{subscription_id}/resourceGroups/{resource_group}/providers/Microsoft.Sql/servers/{server_name}/firewallRules",
     resource_provider_name="Microsoft.Sql",
     resource_type="firewallRules",
     time_grain="PT1M"
 )

Create a condition for the alert:

condition = ThresholdRuleCondition(
     metric_data=metric_data_source,
     threshold=1,
     operator="GreaterThan",
     time_aggregation="Total",
     time_window="PT5M"
 )

Create the Activity Log alert:

alert_rule_resource = AlertRuleResource(
     name="<your-alert-rule-name>",
     description="<your-alert-rule-description>",
     is_enabled=True,
     condition=condition,
     data_source=data_source
 )

alert_rule = monitor_client.alert_rules.create_or_update(
     resource_group,
     "<your-alert-rule-name>",
     alert_rule_resource
 )

Verify that the alert has been created successfully by checking the Azure portal.

That's it! You have successfully remediated the misconfiguration by creating an Activity Log alert for Delete SQL Server Firewall Rule in Azure using Python.

Using Terraform

resource "azurerm_monitor_activity_log_alert" "sql_firewall_rule_delete" {
  name                = "ACTIVITY-LOG-ALERT-Delete-SQL-Server-Firewall-Rule"
  resource_group_name = "RESOURCE_GROUP_NAME"          # replace with the RG where you want the alert resource
  scopes              = ["/subscriptions/SUBSCRIPTION_ID"]  # replace with your subscription or specific SQL server scope
  description         = "Alert when a SQL Server firewall rule is deleted"
  enabled             = true

  criteria {
    category       = "Administrative"
    operation_name = "Microsoft.Sql/servers/firewallRules/delete"
  }

  action {
    action_group_id = azurerm_monitor_action_group.alerts.id
  }

  tags = {
    environment = "ENVIRONMENT_TAG"  # replace or remove as needed
  }
}

resource "azurerm_monitor_action_group" "alerts" {
  name                = "ACTION-GROUP-SQL-FW-RULE-DELETE"
  resource_group_name = "RESOURCE_GROUP_NAME"  # same or another RG, as desired
  short_name          = "sqlfwdel"

  email_receiver {
    name          = "email-admin"
    email_address = "ALERT_EMAIL@example.com"  # replace with your email
  }
}

This change does not force replacement of existing SQL Servers; it only adds an alerting resource.

To verify, terraform plan should show creation of azurerm_monitor_activity_log_alert.sql_firewall_rule_delete (and azurerm_monitor_action_group.alerts if new) with operation_name = "Microsoft.Sql/servers/firewallRules/delete" under criteria.

Triage and Remediation​

Remediation​

Triage and Remediation

Remediation