Skip to main content

lacework-global-565

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

Profile Applicability

• Level 1

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

From Azure Portal

  1. Navigate to the Monitor blade
  2. Click on Alerts
  3. In the Alerts window, click on Alert rules
  4. Hover mouse over the values in the Condition column to find an alert where Operation name=Microsoft.Sql/servers/firewallRules/delete
  5. Click on the Alert Name associated with the previous step
  6. Click on the Condition name of Whenever the Activity Log has an event with Category='Administrative', Signal name='Delete server firewall rule (servers/firewallRules)'
  7. In the Configure signal logic window, ensure the following is configured:
    • Event level: All selected
    • Status: All selected
    • Event initiated by: * (All services an users)
  8. Click Done
  9. Back in the <Alert Name> window, review Actions to ensure that an Action group is assigned to notify the appropriate personnel in your organization.

From Azure CLI

[Azure CLI has been temporarily removed from the Activity Log Alerts section in version 1.5 and will be added back in the next release]

Remediation

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.

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>

References

https://azure.microsoft.com/en-us/updates/classic-alerting-monitoring-retirement
https://docs.microsoft.com/en-in/azure/azure-monitor/platform/alerts-activity-log
https://docs.microsoft.com/en-in/rest/api/monitor/activitylogalerts/createorupdate
https://docs.microsoft.com/en-in/rest/api/monitor/activitylogalerts/listbysubscriptionid
https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-logging-threat-detection#lt-3-enable-logging-for-security-investigation