cis-azure-foundations-6.1.2.8

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

Description

Create an activity log alert for the "Delete SQL Server Firewall Rule."

Rationale

Monitoring for Delete SQL Server Firewall Rule events gives insight into SQL network access changes and may reduce the time it takes to detect suspicious activity.

Impact

There will be a substantial increase in log size if there are a large number of administrative actions on a server.

Audit Procedure

Using Azure Portal

1. Navigate to the Monitor blade.
2. Click on Alerts.
3. In the Alerts window, click on Alert rules.
4. Ensure an alert rule exists where the Condition column contains Operation name=Microsoft.Sql/servers/firewallRules/delete.
5. Click on the Alert Name associated with the previous step.
6. Ensure the Condition panel displays the text Whenever the Activity Log has an event with Category='Administrative', Operation name='Delete server firewall rule' and does not filter on Level, Status or Caller.
7. Ensure the Actions panel displays an Action group is assigned to notify the appropriate personnel in your organization.

Using Azure CLI

az monitor activity-log alert list --subscription <subscription ID> --query "[].{Name:name,Enabled:enabled,Condition:condition.allOf,Actions:actions}"

Look for Microsoft.Sql/servers/firewallRules/delete in the output.

Using PowerShell

Get-AzActivityLogAlert -SubscriptionId <subscription ID>|where-object {$_.ConditionAllOf.Equal -match "Microsoft.Sql/servers/firewallRules/delete"}|select-object Location,Name,Enabled,ResourceGroupName,ConditionAllOf

Expected Result

An activity log alert rule should exist with the operation name Microsoft.Sql/servers/firewallRules/delete and an action group assigned.

Remediation

Remediate from Azure Portal

1. Navigate to the Monitor blade.
2. Select Alerts.
3. Select Create.
4. Select Alert rule.
5. Choose a subscription.
6. Select Apply.
7. Select the Condition tab.
8. Click See all signals.
9. Select Delete server firewall rule (Server Firewall Rule).
10. Click Apply.
11. Select the Actions tab.
12. Click Select action groups to select an existing action group, or Create action group to create a new action group.
13. Follow the prompts to choose or create an action group.
14. Select the Details tab.
15. Select a Resource group, provide an Alert rule name and an optional Alert rule description.
16. Click Review + create.
17. Click Create.

Remediate from Azure CLI

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

Remediate from PowerShell

$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

$actionGroup = Get-AzActionGroup -ResourceGroupName <resource group name> -Name <action group name>
$actionObject = New-AzActivityLogAlertActionGroupObject -Id $actionGroup.Id

$scope = "/subscriptions/<subscription ID>"

New-AzActivityLogAlert -Name "<activity log alert rule name>" -ResourceGroupName "<resource group name>" -Condition $conditions -Scope $scope -Location global -Action $actionObject -Subscription <subscription ID> -Enabled $true

Default Value

By default, no monitoring alerts are created.

References

1. https://azure.microsoft.com/en-us/updates?id=classic-alerting-monitoring-retirement
2. https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-create-activity-log-alert-rule
3. https://learn.microsoft.com/en-us/rest/api/monitor/activity-log-alerts/create-or-update
4. https://learn.microsoft.com/en-us/rest/api/monitor/activity-log-alerts/list-by-subscription-id
5. https://learn.microsoft.com/en-us/security/benchmark/azure/mcsb-logging-threat-detection#lt-3-enable-logging-for-security-investigation

CIS Controls

Profile

Level 1 | Automated