Description:

An Activity Log Alert for the Delete SQL Server Firewall Rule ensures that any attempt to delete a SQL Server Firewall Rule in your Azure environment triggers a notification. SQL Server Firewall Rules are essential for controlling access to Azure SQL databases by specifying which IP addresses can connect. Deleting these rules can inadvertently expose databases or cause disruptions, so it is critical to monitor any deletions and respond quickly.

By setting up an Activity Log Alert for Delete SQL Server Firewall Rule events, you can track such critical actions and ensure that any unauthorized or accidental deletions are immediately addressed.

Rationale:

By creating an Activity Log Alert for Delete SQL Server Firewall Rule actions, you can:

  • Monitor deletions of firewall rules that control access to SQL Servers.

  • Ensure that any changes to firewall rules are tracked and reviewed.

  • Improve security by preventing unauthorized deletions or modifications to access control rules.

  • Ensure compliance with security policies, as accidental or unauthorized deletion of firewall rules can create vulnerabilities.

Impact:

Configuring an Activity Log Alert for Delete SQL Server Firewall Rule ensures that you are notified in real-time if any firewall rule is deleted. However, this may generate alerts in cases where rules are intentionally deleted for legitimate maintenance or updates, so it's important to have a process to review and handle the alerts appropriately.

Default Value:

By default, Activity Log Alerts for Delete SQL Server Firewall Rule events are not configured. You need to manually create and configure these alerts to track and respond to such events.

Pre-requisites:

  • Azure subscription.

  • Owner or Contributor role permissions to create Activity Log Alerts.

  • Azure Monitor enabled for logging.

  • Log Analytics workspace (optional, for storing telemetry data).

Audit:

  1. Sign in to Azure portal as an Owner, Contributor, or Monitoring Contributor.

  2. Navigate to Azure Monitor and review the Activity Log Alerts to ensure that alerts for Delete SQL Server Firewall Rule actions are configured.

  3. Verify that the alert is properly notifying users of SQL Server Firewall Rule deletions.

Implementation Steps (Automated):

  1. Sign in to Azure portal:

    • Use an account with Owner, Contributor, or Monitoring Contributor permissions.

  2. Navigate to Azure Monitor:

    • In the Azure portal, go to Azure Monitor and select Activity Log under Monitoring.

  3. Create a New Alert for Delete SQL Server Firewall Rule:

    • In Activity Log Alerts, click on + New alert rule.

    • Under Scope, select your subscription or resource group.

    • Under Condition, choose Activity Log as the signal type.

      • Set the Event Category to Delete and the Resource Type to Microsoft.Sql/servers/firewallRules.

      • Filter further by Operation Name: Delete SQL Server Firewall Rule.

  4. Example of criteria:

    • Event Category: Delete

    • Operation Name: Delete Firewall Rule

    • Resource Type: Microsoft.Sql/servers/firewallRules

  5. Set Up Action Group:

    • Under Action Group, either select an existing action group or create a new one to define how the alert will notify you (e.g., via Email, SMS, Webhook).

    • You can send notifications to security teams or database administrators whenever a SQL Server Firewall Rule is deleted.

  6. Review and Create:

    • Review the configuration and ensure the alert is set to notify you when a SQL Server Firewall Rule is deleted.

    • Click Create to save the alert rule.

Automate Alert Creation Using Azure CLI: To automate the creation of an Activity Log Alert for Delete SQL Server Firewall Rule using Azure CLI, run the following command:

az monitor activity-log alert create \

    --name "DeleteSQLServerFirewallRuleAlert" \

    --resource-group <Resource-Group-Name> \

    --condition "operationName == 'Delete SQL Server Firewall Rule'" \

    --action-group <Action-Group-ID> \

    --description "Alert for deletion of SQL Server Firewall Rule" \

    --enabled true

  1.  Replace <Resource-Group-Name> and <Action-Group-ID> with the appropriate values for your environment.

  2. Test the Alert:

    • After configuring the Activity Log Alert, test it by deleting a SQL Server Firewall Rule and ensuring that the alert is triggered and the notification is sent to the designated recipients.

  3. Monitor and Review Alerts:

    • Use Azure Monitor to track the alert history and ensure that it is functioning as expected.

    • Review the alert history to verify that SQL Server Firewall Rule deletions are being logged.

Backout Plan (Automated):

  1. Sign in to Azure portal:

    • Use an account with Owner, Contributor, or Monitoring Contributor permissions.

  2. Navigate to Azure Monitor:

    • Go to Azure Monitor in the Azure portal.

  3. Delete or Modify the Alert:

    • In Activity Log Alerts, locate the Delete SQL Server Firewall Rule alert you created.

    • Select the alert and either delete it or modify its parameters as needed (e.g., change the notification action group, adjust alert criteria).

  4. Verify Alert Removal:

    • After removing or modifying the alert, ensure that it no longer triggers notifications for Delete SQL Server Firewall Rule events.

  5. Test the Backout:

    • Test the configuration by deleting a SQL Server Firewall Rule to ensure that the alert no longer triggers or functions as expected after the backout.

References: