Detections
Analytics

5.2.9 Ensure that Activity Log Alert exists for Create or Update or Delete SQL Server Firewall Rule - create/update

Warning! Audit Deprecated

This audit has been deprecated and will be removed in a future update.

View Next Audit Version

Information

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

Rationale:

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

Solution

From Azure Console

Go to Monitor

Select Alerts

Click On New Alert Rule

Under Scope, click Select resource

Select the appropriate subscription under Filter by subscription

Select SQL servers under Filter by resource type

Select All for Filter by location

Click on the subscription from the entries populated under Resource

Verify Selection preview shows SQL servers and your selected subscription name

Under Condition click Add Condition

Select All Administrative operations signal

Click Done

Under Action group, select Add action groups and complete creation process or select appropriate action group

Under Alert rule details, enter Alert rule name and Description

Select appropriate resource group to save the alert to

Check Enable alert rule upon creation checkbox

Click Create alert rule

Using Azure Command Line Interface
Use the below command to create an Activity Log Alert for Create or Update or Delete SQL Firewall Rule

az account get-access-token --query '{subscription:subscription,accessToken:accessToken}' --out tsv | xargs -L1 bash -c 'curl -X PUT -H 'Authorization: Bearer $1' -H 'Content-Type: application/json' https://management.azure.com/subscriptions/$0/resourceGroups/<Resource_Group_To Create_Alert_In>/providers/microsoft.insights/activityLogAlerts/<Unique_Alert_Name>?api-version=2017-04-01 -d@'input.json''

Where input.json contains the Request body JSON data as mentioned below.

{
 'location': 'Global',
 'tags': {},
 'properties': {
 'scopes': [
 '/subscriptions/<Subscription_ID>'
 ],
 'enabled': true,
 'condition': {
 'allOf': [
 {
 'containsAny': null,
 'equals': 'Administrative',
 'field': 'category'
 },
 {
 'containsAny': null,
 'equals': 'Microsoft.Sql/servers/firewallRules/write',
 'field': 'operationName'
 }
 ]
 },
 'actions': {
 'actionGroups': [
 {
 'actionGroupId': '/subscriptions/<Subscription_ID>/resourceGroups/<Resource_Group_For_Alert_Group>/providers/microsoft.insights/actionGroups/<Alert_Group>',
 'webhookProperties': null
 }
 ]
 },
 }
}

Configurable Parameters for command line:

<Resource_Group_To Create_Alert_In>
<Unique_Alert_Name>

Configurable Parameters for input.json:

<Subscription_ID> in scopes
<Subscription_ID> in actionGroupId
<Resource_Group_For_Alert_Group> in actionGroupId
<Alert_Group> in actionGroupId



Default Value:

By default, no monitoring alerts are created.

See Also

https://workbench.cisecurity.org/files/3459

Item Details

References: CSCv7|6.3

Plugin: microsoft_azure

Control ID: 951e73afec9745cefe992781246a08fb01e2dcc721435a5e6f95403983da58b2