Azure Introduction
Azure Pricing
Azure Threats
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
Security, Operational Maturity
Compliance Standards
ISO27001, HIPAA
Triage and Remediation
Remediation
To remediate the misconfiguration “Ensure Activity Log Alert exists for Delete SQL Server Firewall Rule” in Azure using Azure console, follow the below steps:
-
Login to Azure portal (https://portal.azure.com/).
-
Navigate to the SQL server for which you want to create the activity log alert.
-
Under the Security section, click on “Firewalls and virtual networks”.
-
Click on the “Activity Log Alerts” tab.
-
Click on the “Add Activity Log Alert” button.
-
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.
-
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.
-
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.
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.
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:
-
Install the Azure SDK for Python using the following command:
pip install azure-mgmt-monitor -
Import the necessary modules in your Python script:
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 -
Create a Service Principal and assign the required role to it. You can follow the instructions mentioned in this link to create a Service Principal.
-
Authenticate using the Service Principal credentials:
credentials = ServicePrincipalCredentials( client_id='<your-client-id>', secret='<your-client-secret>', tenant='<your-tenant-id>' ) -
Create a Resource Management client object to get the resource group and SQL Server details:
resource_client = ResourceManagementClient(credentials, subscription_id) resource_group = '<your-resource-group>' server_name = '<your-sql-server-name>' -
Create a Monitor Management client object to create the Activity Log alert:
monitor_client = MonitorManagementClient(credentials, subscription_id) -
Create a data source for the alert:
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" ) -
Create a metric data source for the alert:
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" ) -
Create a condition for the alert:
condition = ThresholdRuleCondition( metric_data=metric_data_source, threshold=1, operator="GreaterThan", time_aggregation="Total", time_window="PT5M" ) -
Create the Activity Log alert:
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
)
- 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.