Description:

An Activity Log Alert for Create or Update SQL Server Firewall Rule ensures that any action to create or update a SQL Server Firewall Rule in your Azure environment triggers a notification. SQL Server Firewall Rules are used to control which IP addresses can connect to your Azure SQL databases. Changes to these rules, especially updates or additions, can significantly impact the security and accessibility of your database services. Monitoring these changes is essential to prevent unauthorized access and maintain control over who can connect to your databases.

Rationale:

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

  • Monitor the creation and updates to firewall rules that control access to your SQL Servers.

  • Track changes to firewall settings and ensure that any updates are authorized and intentional.

  • Improve security by notifying administrators when critical firewall rules are modified, reducing the risk of exposing SQL databases to unauthorized users.

  • Support compliance by maintaining an audit trail of firewall rule modifications for security review.

Impact:

Configuring an Activity Log Alert for Create or Update SQL Server Firewall Rule ensures you are notified in real-time when firewall rules are modified, helping you respond quickly to any unauthorized or accidental changes. However, this might increase the volume of alerts in dynamic environments where firewall rules are frequently updated. Proper alert filtering and review procedures are necessary to avoid alert fatigue.

Default Value:

By default, there are no Activity Log Alerts configured for Create or Update SQL Server Firewall Rule events. 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 Create or Update SQL Server Firewall Rule actions are configured.

  3. Verify that the configured alert is properly notifying users of SQL Server Firewall Rule creation or updates.

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 Create or Update 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 Write and the Resource Type to Microsoft.Sql/servers/firewallRules.

    • Filter further by Operation Name: Create SQL Server Firewall Rule or Update SQL Server Firewall Rule.

  4. Example of criteria:

    • Event Category: Write

    • Operation Name: Create Firewall Rule, Update 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 network administrators whenever a SQL Server Firewall Rule is created or updated.

  6. Review and Create:

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

    • Click Create to save the alert rule.

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

az monitor activity-log alert create \

    --name "CreateOrUpdateSQLServerFirewallRuleAlert" \

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

    --condition "operationName in ['Create SQL Server Firewall Rule', 'Update SQL Server Firewall Rule']" \

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

    --description "Alert for creation or update 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 creating or updating 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 creation or update events 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 Create or Update 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 Create or Update SQL Server Firewall Rule events.

  5. Test the Backout:

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

References: