Back to Checks
azure

Subscription has an Activity Log Alert for SQL Server firewall rule deletions

monitor_alert_delete_sqlserver_fr
Service: monitorSeverity: medium
OfficialBuilt-inVerified
Categories:logging
Resource Type:microsoft.insights/activitylogalerts

Azure Monitor Activity log alerts watch the admin operation Microsoft.Sql/servers/firewallRules/delete, indicating when an Azure SQL firewall rule is removed across a subscription.

Prowler logoProwlerprowler-provider-azure

Risk

Without alerting on firewall rule deletions, unexpected changes to SQL network allowlists can go unnoticed, causing availability loss for apps and masking unauthorized tampering. A compromised admin could remove rules to disrupt service, erode control integrity, and delay response.

Run this check with Prowler

Run in Prowler Cloud
Run
prowler azure --checks monitor_alert_delete_sqlserver_fr

Recommendation

Create an activity log alert for Microsoft.Sql/servers/firewallRules/delete and route it via an action group for rapid triage.

Enforce least privilege and separation of duties on SQL admins, add alerts for related create/update operations, integrate with SIEM, and require change approval to strengthen defense in depth.

Remediation

CLI

az monitor activity-log alert create --resource-group <resource_group_name> --name <alert_name> --scopes /subscriptions/<subscription_id> --condition "category=Administrative and operationName=Microsoft.Sql/servers/firewallRules/delete" --location global

Terraform

# Activity Log Alert for SQL Server firewall rule deletions
resource "azurerm_monitor_activity_log_alert" "<example_resource_name>" {
  name                = "<example_resource_name>"
  resource_group_name = "<example_resource_name>"
  scopes              = ["/subscriptions/<example_subscription_id>"]

  criteria {
    category       = "Administrative" # Critical: filter Activity Log category
    operation_name = "Microsoft.Sql/servers/firewallRules/delete" # Critical: match deletion of SQL Server firewall rules
  }
}

NativeIaC

// Activity Log Alert for SQL Server firewall rule deletions
resource activityLogAlert 'Microsoft.Insights/activityLogAlerts@2020-10-01' = {
  name: '<example_resource_name>'
  location: 'Global'
  properties: {
    scopes: [
      '/subscriptions/<example_subscription_id>'
    ]
    enabled: true
    condition: {
      allOf: [
        {
          field: 'category' // Critical: filter Activity Log category
          equals: 'Administrative' // Ensures Administrative events are matched
        }
        {
          field: 'operationName' // Critical: target deletion of SQL Server firewall rules
          equals: 'Microsoft.Sql/servers/firewallRules/delete' // This makes the check PASS
        }
      ]
    }
  }
}

Other

1In the Azure Portal, go to Monitor > Alerts > + Create > Alert rule
2Scope: Select the target Subscription and click Done
3Condition: Click Add condition, choose Signal type = Activity log, search for and select the operation with type "Microsoft.Sql/servers/firewallRules/delete" (display name like "Delete Server Firewall Rule"), then Click Apply
4Actions: Skip (optional)
5Details: Enter an Alert rule name and ensure Enable upon creation is selected
6Click Create

Check Code

from prowler.lib.check.models import Check, Check_Report_Azure
from prowler.providers.azure.services.monitor.lib.monitor_alerts import check_alert_rule
from prowler.providers.azure.services.monitor.monitor_client import monitor_client


class monitor_alert_delete_sqlserver_fr(Check):
    def execute(self) -> Check_Report_Azure:
        findings = []

        for (
            subscription_id,
            activity_log_alerts,
        ) in monitor_client.alert_rules.items():
            subscription_name = monitor_client.subscriptions.get(
                subscription_id, subscription_id
            )
            for alert_rule in activity_log_alerts:
                if check_alert_rule(
                    alert_rule, "Microsoft.Sql/servers/firewallRules/delete"
                ):
                    report = Check_Report_Azure(
                        metadata=self.metadata(), resource=alert_rule
                    )
                    report.subscription = subscription_id
                    report.status = "PASS"
                    report.status_extended = f"There is an alert configured for deleting SQL Server firewall rule in subscription {subscription_name} ({subscription_id})."
                    break
            else:
                report = Check_Report_Azure(metadata=self.metadata(), resource={})
                report.subscription = subscription_id
                report.resource_name = subscription_id
                report.resource_id = f"/subscriptions/{subscription_id}"
                report.status = "FAIL"
                report.status_extended = f"There is not an alert for deleting SQL Server firewall rule in subscription {subscription_name} ({subscription_id})."

            findings.append(report)

        return findings

References