> ## Documentation Index
> Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Ensure Activity Log Alert exists for Delete SQL Server Firewall Rule

### More Info:

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

### Risk Level

Low

### Address

Operational Maturity, Security

### Compliance Standards

* APRA CPS 234 (Australia)
* BSI C5 (Germany)
* Brazil LGPD
* CCPA / CPRA (California)
* CIS Critical Security Controls v8
* CMMC 2.0
* CSA Cloud Controls Matrix v4
* DPDPA
* Digital Operational Resilience Act (EU)
* HIPAA
* ISO 27001
* ISO/IEC 27017
* ISO/IEC 27018
* ISO/IEC 27701
* KSA PDPL
* MAS Technology Risk Management (Singapore)
* MITRE ATT\&CK (Cloud)
* NIS2 Directive
* NIST SP 800-171
* NYDFS 23 NYCRR 500
* SWIFT Customer Security Controls Framework
* Sarbanes-Oxley IT General Controls
* UK NCSC Cyber Assessment Framework

### Triage and Remediation

<Tabs>
  <Tab title="Remediation">
    ### Remediation

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        To remediate the misconfiguration "Ensure Activity Log Alert exists for Delete SQL Server Firewall Rule" in Azure using Azure console, follow the below steps:

        1. Login to Azure portal ([https://portal.azure.com/](https://portal.azure.com/)).

        2. Navigate to the SQL server for which you want to create the activity log alert.

        3. Under the Security section, click on "Firewalls and virtual networks".

        4. Click on the "Activity Log Alerts" tab.

        5. Click on the "Add Activity Log Alert" button.

        6. In the "Create Activity Log Alert" window, provide the following details:

           a. Name: Provide a name for the alert.

           b. Description: Provide a description for the alert.

           c. Subscription: Select the subscription in which you want to create the alert.

           d. Resource Group: Select the resource group in which the SQL server is located.

           e. Resource Type: Select "Microsoft.Sql/servers/firewallRules" from the dropdown.

           f. Resource Name: Select the name of the SQL server for which you want to create the alert.

           g. Alert criteria: Under the "Alert criteria" section, select "Delete" from the "Operation Name" dropdown.

        7. Under the "Actions" section, select the action you want to perform when the alert is triggered. You can choose to send an email or a webhook notification.

        8. Click on the "Create Alert" button to create the activity log alert.

        Once the activity log alert is created, you will receive a notification whenever a firewall rule is deleted from the SQL server.

        #
      </Accordion>

      <Accordion title="Using CLI">
        To remediate the misconfiguration "Ensure Activity Log Alert exists for Delete SQL Server Firewall Rule" for AZURE using AZURE CLI, you can follow the below steps:

        Step 1: Login to AZURE CLI

        ```
        az login
        ```

        Step 2: Check if an activity log alert exists for delete SQL server firewall rule

        ```
        az monitor activity-log alert list --query "[?contains(name, 'Delete SQL Server Firewall Rule')]"
        ```

        Step 3: If the output of the above command is empty, create an activity log alert for delete SQL server firewall rule

        ```
        az monitor activity-log alert create --name "Delete SQL Server Firewall Rule Alert" --description "Alert for Delete SQL Server Firewall Rule" --condition "category=Administrative and operationName=Microsoft.Sql/servers/firewallRules/delete" --action-group "/subscriptions/<subscription-id>/resourceGroups/<resource-group-name>/providers/Microsoft.Insights/actionGroups/<action-group-name>"
        ```

        Note: Replace the `<subscription-id>`, `<resource-group-name>`, and `<action-group-name>` with your actual values.

        Step 4: Verify the activity log alert has been created successfully

        ```
        az monitor activity-log alert list --query "[?contains(name, 'Delete SQL Server Firewall Rule')]"
        ```

        This should remediate the misconfiguration "Ensure Activity Log Alert exists for Delete SQL Server Firewall Rule" for AZURE using AZURE CLI.
      </Accordion>

      <Accordion title="Using Python">
        To remediate this misconfiguration in Azure, we need to create an Activity Log alert for Delete SQL Server Firewall Rule. Here are the step-by-step instructions to do this using Python:

        1. Install the Azure SDK for Python using the following command:

           ```
           pip install azure-mgmt-monitor
           ```

        2. Import the necessary modules in your Python script:

           ```python theme={null}
           from azure.common.credentials import ServicePrincipalCredentials
           from azure.mgmt.monitor import MonitorManagementClient
           from azure.mgmt.monitor.models import AlertRuleResource, RuleCondition, \
                                                   RuleDataSource, RuleMetricDataSource, \
                                                   ThresholdRuleCondition
           from azure.mgmt.resource import ResourceManagementClient
           ```

        3. Create a Service Principal and assign the required role to it. You can follow the instructions mentioned in this [link](https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal) to create a Service Principal.

        4. Authenticate using the Service Principal credentials:

           ```python theme={null}
           credentials = ServicePrincipalCredentials(
                client_id='<your-client-id>',
                secret='<your-client-secret>',
                tenant='<your-tenant-id>'
            )
           ```

        5. Create a Resource Management client object to get the resource group and SQL Server details:

           ```python theme={null}
           resource_client = ResourceManagementClient(credentials, subscription_id)
           resource_group = '<your-resource-group>'
           server_name = '<your-sql-server-name>'
           ```

        6. Create a Monitor Management client object to create the Activity Log alert:

           ```python theme={null}
           monitor_client = MonitorManagementClient(credentials, subscription_id)
           ```

        7. Create a data source for the alert:

           ```python theme={null}
           data_source = RuleDataSource(
                resource_uri=f"/subscriptions/{subscription_id}/resourceGroups/{resource_group}/providers/Microsoft.Sql/servers/{server_name}/firewallRules",
                resource_provider_name="Microsoft.Sql",
                resource_type="firewallRules"
            )
           ```

        8. Create a metric data source for the alert:

           ```python theme={null}
           metric_data_source = RuleMetricDataSource(
                metric_name="DeleteFirewallRule",
                resource_uri=f"/subscriptions/{subscription_id}/resourceGroups/{resource_group}/providers/Microsoft.Sql/servers/{server_name}/firewallRules",
                resource_provider_name="Microsoft.Sql",
                resource_type="firewallRules",
                time_grain="PT1M"
            )
           ```

        9. Create a condition for the alert:

           ```python theme={null}
           condition = ThresholdRuleCondition(
                metric_data=metric_data_source,
                threshold=1,
                operator="GreaterThan",
                time_aggregation="Total",
                time_window="PT5M"
            )
           ```

        10. Create the Activity Log alert:

        ```python theme={null}
        alert_rule_resource = AlertRuleResource(
             name="<your-alert-rule-name>",
             description="<your-alert-rule-description>",
             is_enabled=True,
             condition=condition,
             data_source=data_source
         )

        alert_rule = monitor_client.alert_rules.create_or_update(
             resource_group,
             "<your-alert-rule-name>",
             alert_rule_resource
         )
        ```

        11. Verify that the alert has been created successfully by checking the Azure portal.

        That's it! You have successfully remediated the misconfiguration by creating an Activity Log alert for Delete SQL Server Firewall Rule in Azure using Python.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        resource "azurerm_monitor_activity_log_alert" "sql_firewall_rule_delete" {
          name                = "ACTIVITY-LOG-ALERT-Delete-SQL-Server-Firewall-Rule"
          resource_group_name = "RESOURCE_GROUP_NAME"          # replace with the RG where you want the alert resource
          scopes              = ["/subscriptions/SUBSCRIPTION_ID"]  # replace with your subscription or specific SQL server scope
          description         = "Alert when a SQL Server firewall rule is deleted"
          enabled             = true

          criteria {
            category       = "Administrative"
            operation_name = "Microsoft.Sql/servers/firewallRules/delete"
          }

          action {
            action_group_id = azurerm_monitor_action_group.alerts.id
          }

          tags = {
            environment = "ENVIRONMENT_TAG"  # replace or remove as needed
          }
        }

        resource "azurerm_monitor_action_group" "alerts" {
          name                = "ACTION-GROUP-SQL-FW-RULE-DELETE"
          resource_group_name = "RESOURCE_GROUP_NAME"  # same or another RG, as desired
          short_name          = "sqlfwdel"

          email_receiver {
            name          = "email-admin"
            email_address = "ALERT_EMAIL@example.com"  # replace with your email
          }
        }
        ```

        This change does not force replacement of existing SQL Servers; it only adds an alerting resource.

        To verify, `terraform plan` should show creation of `azurerm_monitor_activity_log_alert.sql_firewall_rule_delete` (and `azurerm_monitor_action_group.alerts` if new) with `operation_name = "Microsoft.Sql/servers/firewallRules/delete"` under `criteria`.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>

### Additional Reading:

* [https://docs.microsoft.com/en-in/azure/azure-monitor/platform/alerts-activity-log](https://docs.microsoft.com/en-in/azure/azure-monitor/platform/alerts-activity-log)