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

Multiple Remediation Paths

Azure

SERVICE-WIDE (RECOMMENDED when many resources are affected): Assign Azure Policy initiatives at management group/subscription scope and trigger remediation tasks.

az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>

ASSET-LEVEL: Apply the resource-specific remediation steps above to the listed non-compliant resources.

PREVENTIVE: Embed Azure Policy checks into landing zones and IaC workflows to block or auto-remediate drift.

References for Service-Wide Patterns

• Azure Policy overview: https://learn.microsoft.com/en-us/azure/governance/policy/overview
• Azure Policy remediation: https://learn.microsoft.com/en-us/azure/governance/policy/how-to/remediate-resources
• Azure Policy initiative structure: https://learn.microsoft.com/en-us/azure/governance/policy/concepts/initiative-definition-structure

Operational Rollout Workflow

Use this sequence to reduce risk and avoid repeated drift.

1. Contain at Service-Wide Scope First (Recommended)

• Azure: assign policy initiatives at management group/subscription scope and run remediation tasks.

az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>

2. Remediate Existing Affected Assets

• Execute the control-specific Console/CLI steps documented above for each flagged resource.
• Prioritize internet-exposed and production assets first.

3. Validate and Prevent Recurrence

• Re-scan after each remediation batch.
• Track exceptions with owner and expiry date.
• Add preventive checks in IaC/CI pipelines.