Detections
Analytics

6.2.7 Ensure that Activity Log Alert exists for Create or Update SQL Server Firewall Rule

Information

Create an activity log alert for the Create or Update SQL Server Firewall Rule event.

Rationale:

Monitoring for Create or Update SQL Server Firewall Rule events gives insight into 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.

Solution

Remediate 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 Create/Update 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.

Remediate from Azure CLI

az monitor activity-log alert create --resource-group '<resource group name>' --condition category=Administrative and operationName=Microsoft.Sql/servers/firewallRules/write 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
Create the Conditions object.

$conditions = @()
$conditions += New-AzActivityLogAlertAlertRuleAnyOfOrLeafConditionObject -Equal Administrative -Field category
$conditions += New-AzActivityLogAlertAlertRuleAnyOfOrLeafConditionObject -Equal Microsoft.Sql/servers/firewallRules/write -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/write

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.

See Also

https://workbench.cisecurity.org/benchmarks/16820

Item Details

Category: AUDIT AND ACCOUNTABILITY

References: 800-53|AU-3, 800-53|AU-3(1), 800-53|AU-7, 800-53|AU-12, CSCv7|6.3

Plugin: microsoft_azure

Control ID: e41923a971d57704a4a7ac9eca04d39668836a212e92bedb5e8077bd71746c92