Control: 5.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."

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.

Remediation

From Azure Portal

Navigate to the Monitor blade.
Select Alerts.
Select Create.
Select Alert rule.
Choose a subscription.
Select Apply.
Select the Condition tab.
Click See all signals.
Select Delete server firewall rule (Server Firewall Rule).
Click Apply.
Select the Actions tab.
Click Select action groups to select an existing action group, or Create action group to create a new action group.
Follow the prompts to choose or create an action group.
Select the Details tab.
Select a Resource group, provide an Alert rule name and an optional Alert rule description.
Click Review + create.
Click Create.

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> --location global

From 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 in a variable, then create the Actions object.

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

Create the Scope object

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

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

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 or active.

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v210_5_2_8

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_compliance.control.cis_v210_5_2_8 --share

SQL

This control uses a named query:

monitor_log_alert_delete_sql_servers_firewall_rule