|   Trend Micro Cloud One™
Open menu

Create Alert for "Create, Update or Delete SQL Server Firewall Rule" Events

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
Risk level: High (not acceptable risk)
Rule ID: ActivityLog-009

Ensure that an Azure activity log alert is fired whenever “Create”, “Update” or “Delete SQL Server Firewall Rule” events are triggered in your Microsoft Azure cloud account. Activity log alerts get activated when a new activity log event that matches the condition specified in the alert occurs. In this case, the condition required for the alert is 'Whenever the Administrative Activity Log "Create/Update server firewall rule (Microsoft.Sql/servers/firewallRules)" has "any" level, with "any" status and event is initiated by "any"'.

This rule resolution is part of the Cloud Conformity Security & Compliance tool for Azure

Security

Monitoring your Microsoft Azure account for “Create”, “Update” or “Delete SQL Server Firewall Rule” events can give you insight into the changes made at the SQL servers firewall level and can help reduce the time it takes to detect suspicious activity.

Audit

To determine if there is an activity log alert created for “Create/Update/Delete SQL Server Firewall Rule” events in your Azure cloud account, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to Azure Monitor blade at https://portal.azure.com/#blade/Microsoft_Azure_Monitoring/AzureMonitoringBrowseBlade/overview.

03 In the navigation panel, select Alerts to access all the alerts available within your Azure cloud account.

04 On the Alerts page, click on the Manage alert rules button from the dashboard top menu to access the alert rules management page.

05 On the Rules page, select the subscription that you want to examine from the Subscription filter box and the Enabled option from the Status dropdown list, to return all the active alert rules created in the selected account subscription.

06 Click on the name (link) of the alert rule that you want to examine.

07 On the selected alert rule configuration page, check the condition phrase available in the CONDITION section. If the condition phrase is different than Whenever the Administrative Activity Log "Create/Update server firewall rule (Microsoft.Sql/servers/firewallRules)" has "any" level, with "any" status and event is initiated by "any", the selected alert rule is not configured to fire whenever “Create”, “Update” or “Delete SQL Server Firewall Rule” events are triggered.

08 Repeat step no. 6 and 7 for the rest of the alert rules available within the selected account subscription. If none of the verified rules contain the right condition, there are no activity log alerts available for “Create”, “Update” or “Delete SQL Server Firewall Rule” events in the selected Azure cloud account subscription.

09 Repeat steps no. 5 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI

01 Run monitor activity-log alert list command (Windows/macOS/Linux) using custom query filters to get the identifiers of all the active activity log alert rules available in the current Azure subscription:

az monitor activity-log alert list
	--query '[?(enabled==`true`)].id'

02 The command output should return the requested alert rule IDs:

[
"/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/cloud-shell-storage-westeurope/providers/microsoft.insights/activityLogAlerts/Cloud Conformity Create Update NSG Rule Alert",
"/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/cloud-shell-storage-westeurope/providers/microsoft.insights/activityLogAlerts/Cloud Conformity Delete Azure Resource Alert",
"/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/cloud-shell-storage-westeurope/providers/microsoft.insights/activityLogAlerts/ Cloud Conformity Update Access Policy Alert"
]

03 Run monitor activity-log alert show command (Windows/macOS/Linux) using the ID of the alert rule that you want to examine as identifier parameter and custom query filters to list the condition(s) defined for the selected activity log alert rule:

az monitor activity-log alert show
	--ids "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/cloud-shell-storage-westeurope/providers/microsoft.insights/activityLogAlerts/Cloud Conformity Create Update NSG Rule Alert"
	--query 'condition'

04 The command output should return the condition metadata for the selected alert rule:

{
  "allOf": [
    {
      "containsAny": null,
      "equals": "Administrative",
      "field": "category",
      "odata.type": null
    },
    {
      "containsAny": null,
      "equals": "Microsoft.Network/networkSecurityGroups/securityRules/write",
      "field": "operationName",
      "odata.type": null
    }
  ],
  "odata.type": null
}

Check the command output for the JSON object with the "field" property set to "operationName" (highlighted). If the object's "equals" property is not set to "Microsoft.Sql/servers/firewallRules/write", the selected alert rule is not configured to fire whenever “Create”, “Update” or “Delete SQL Server Firewall Rule” events are triggered.

05 Repeat step no. 3 and 4 for the rest of the alert rules available in the current subscription. If none of the verified rules contain the right condition, there are no activity log alerts available for “Create”, “Update” or “Delete SQL Server Firewall Rule” events within the selected Azure cloud account subscription.

06 Repeat steps no. 1 – 5 for each subscription available in your Microsoft Azure cloud account.

Remediation / Resolution

To create a Microsoft Azure activity log alert for “Create/Update/Delete SQL Server Firewall Rule” events, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to Azure Monitor blade at https://portal.azure.com/#blade/Microsoft_Azure_Monitoring/AzureMonitoringBrowseBlade/overview.

03 In the navigation panel, select Alerts to access all the alerts available in your Azure cloud account.

04 On the Alerts page, click on the Manage alert rules button from the dashboard top menu to access the alert rules management page.

05 On the Rules page, select the Azure account subscription where you want to create the new alert rule from the Subscription filter box.

06 Click New alert rule button from the dashboard top menu to start the rule setup process.

07 On the Create rule page, perform the following actions:

  1. In the RESOURCE section, click Select and configure the target subscription that you wish to monitor. Once the appropriate Azure subscription is selected, click Done.
  2. In the CONDITION section, click Add to configure the alert rule condition. On Configure signal logic panel, find and select the signal with the name Create/Update server firewall rule (Microsoft.Sql/servers/firewallRules). To obtain the right configuration for the condition (i.e. Whenever the Administrative Activity Log "Create/Update server firewall rule (Microsoft.Sql/servers/firewallRules)" has "any" level, with "any" status and event is initiated by "any"), leave the default settings available for the condition logic unchanged, then click Done to generate the condition.
  3. In the ACTIONS section, click Select action group to choose an existing action group to attach to the new alert rule or click Create action group button to create a new one. An action group is a collection of alert notification preferences defined for the selected Azure subscription. Azure Monitor service alerts use action groups to notify users that an alert has been triggered.
  4. In the ALERT DETAILS section, provide a unique name for the new rule in the Alert rule name box, type a short description in the Description box, and choose the resource group in which the alert will be created from the Save alert to resource group dropdown list.
  5. Make sure that Enable rule upon creation option is set to Yes, then click Create alert rule to finish the rule setup process. It can take up to 5 minutes for an alert rule to become active.

08 Repeat steps no. 5 – 7 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI

01 Run monitor activity-log alert create command (Windows/macOS/Linux) to create a new Azure activity log alert that fires whenever a “Create”, “Update” or “Delete SQL Server Firewall Rule” event is triggered within the current Microsoft Azure cloud subscription:

az monitor activity-log alert create
	--name cc-create-update-delete-sql-firewall-rule-alert
	--description "Alert triggered by Create, Update or Delete SQL Server Firewall Rule events"
	--resource-group Default-ActivityLogAlerts
	--action-group "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourcegroups/default-activitylogalerts/providers/microsoft.insights/actiongroups/cloudconformity%20action%20group"
	--condition category=Administrative and operationName=Microsoft.Sql/servers/firewallRules/write

02 The command output should return the configuration metadata for the new activity log alert:

{
  "actions": {
    "actionGroups": [
      {
        "actionGroupId": "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourcegroups/default-activitylogalerts/providers/microsoft.insights/actiongroups/cloudconformity%20action%20group",
        "webhookProperties": null
      }
    ]
  },
  "condition": {
    "allOf": [
      {
        "containsAny": null,
        "equals": "Administrative",
        "field": "category",
        "odata.type": null
      },
      {
        "containsAny": null,
        "equals": "Microsoft.Sql/servers/firewallRules/write",
        "field": "operationName",
        "odata.type": null
      }
    ],
    "odata.type": null
  },
  "description": "Alert triggered by Create, Update or Delete SQL Server Firewall Rule events",
  "enabled": true,
  "id": "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/Default-ActivityLogAlerts/providers/microsoft.insights/activityLogAlerts/cc-create-update-delete-firewall-rule-alert",
  "identity": null,
  "kind": null,
  "location": "Global",
  "name": "cc-create-update-delete-firewall-rule-alert",
  "resourceGroup": "Default-ActivityLogAlerts",
  "scopes": [
    "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/Default-ActivityLogAlerts"
  ],
  "tags": {},
  "type": "Microsoft.Insights/ActivityLogAlerts"
}

03 Repeat step no. 1 and 2 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Apr 2, 2020

Unlock the Remediation Steps

Gain free unlimited access to our full Knowledge Base


Over 600 rules & best practices for and

Get started for FREE

A verification email will be sent to this address
We keep your information private. Learn more.

Thank you!

Please click the link in the confirmation email sent to

You are auditing:

Create Alert for "Create, Update or Delete SQL Server Firewall Rule" Events

Risk level: High