Ensure that Activity Log Alert exists for Delete SQL Server Firewall Rule

From Azure Console

1. Go to Monitor
2. Select Alerts
3. Click on Create and select Alert rule
4. Under Scope, click Select scope
5. Select the appropriate subscription and click Apply
6. Under Condition click See all signals
7. Select the Delete server firewall rule (Server Firewall Rule) signal
8. Click Apply
9. Under Actions, click Select action groups to pick an existing action group or click Create action group and fill in the necessary details to create a new action group
10. Under Details, select the appropriate resource group to save the alert to, and an alert name
11. Under Review + create, check the Enable upon creation checkbox
12. Click Create

Using Azure Command Line Interface

Use the below command to create an Activity Log Alert for Delete SQL Firewall Rule

az monitor activity-log alert create --resource-group "<resourceGroup>" --condition category=Administrative and operationName=Microsoft.Sql/servers/firewallRules/delete and level=<verbose | information | warning | error | critical> --scope "/subscriptions/<subscriptionID>" --name "<activityLogRuleName>" --subscription <subscriptionID> --action-group <actionGroupID> --location global

Using Azure PowerShell

Create the Conditions object.

$conditions = @()
$conditions += New-AzActivityLogAlertAlertRuleAnyOfOrLeafConditionObject -Equal Administrative -Field category
$conditions += New-AzActivityLogAlertAlertRuleAnyOfOrLeafConditionObject -Equal Microsoft.Sql/servers/firewallRules/delete -Field operationName
$conditions += New-AzActivityLogAlertAlertRuleAnyOfOrLeafConditionObject -Equal Verbose -Field level

Retrieve the Action group information and store it in a variable, then create the Actions object.

$actionGroup = Get-AzActionGroup -ResourceGroupName <resourcegroup> -Name <actionGroup>
$actionObject = New-AzActivityLogAlertActionGroupObject -Id $actionGroup.Id

Create the Scope object.

$scope = "/subscriptions/<subscriptionID>"

Create the Activity Log Alert Rule for Microsoft.Sql/servers/firewallRules/delete

New-AzActivityLogAlert -Name "<activityLogRuleName>" -ResourceGroupName "<resourceGroup>" -Condition $conditions -Scope $scope -Location global -Action $actionObject -Subscription <subscriptionID> -Enabled $true

Default Value

By default, no monitoring alerts are created.

References

1. https://azure.microsoft.com/en-us/updates/classic-alerting-monitoring-retirement
2. https://learn.microsoft.com/en-in/azure/azure-monitor/platform/alerts-activity-log
3. https://learn.microsoft.com/en-in/rest/api/monitor/activitylogalerts/createorupdate
4. https://learn.microsoft.com/en-in/rest/api/monitor/activitylogalerts/listbysubscriptionid
5. https://learn.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-logging-threat-detection#lt-3-enable-logging-for-security-investigation

Service-wide remediation

Recommended when many resources are affected: fix the platform baseline first so new resources inherit the secure setting, then remediate the existing flagged resources in batches.

Azure

Use management group or subscription Azure Policy assignments, remediation tasks where supported, landing-zone standards, and IaC modules so drift is prevented at scale.

Operational rollout

1. Fix the baseline first at the account, subscription, project, cluster, or tenant scope that owns this control.
2. Remediate the currently affected resources in batches, starting with internet-exposed and production assets.
3. Re-scan and track approved exceptions with an owner and expiry date.