Azure Introduction
Azure Pricing
Azure Threats
Ensure Activity Log Alert exists for Create or Update SQL Server Firewall Rule
More Info:
Monitoring for ‘Create’ or ‘Update 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
CISAZURE, CBP, HIPAA, ISO27001
Triage and Remediation
Remediation
To remediate the misconfiguration “Ensure Activity Log Alert exists for Create or Update SQL Server Firewall Rule” for Azure using Azure console, please follow the below steps:
- Login to the Azure portal (https://portal.azure.com/)
- Navigate to the SQL Server resource for which you want to create an activity log alert for firewall rule creation or update.
- Click on the “Activity log” option under the “Monitoring” section in the left-hand menu.
- Click on the “Add alert rule” button.
- In the “Basics” tab, provide a name and description for the alert, and select the subscription and resource group where the SQL Server is located.
- In the “Condition” tab, select the “Activity log” option under the “Signal logic” section.
- In the “Signal name” dropdown, select “Create or Update SQL Server Firewall Rule”.
- In the “Alert logic” section, set the threshold for the alert based on your requirements.
- In the “Actions” tab, select the action group to which you want to send the alert notifications.
- Review and confirm the alert rule details in the “Review + create” tab.
- Click on the “Create alert rule” button to create the activity log alert for SQL Server firewall rule creation or update.
Once the activity log alert is created, you will receive notifications whenever a firewall rule is created or updated for the SQL Server resource. This will help you to ensure the security of your Azure SQL Server resource.
To remediate the misconfiguration for AZURE using AZURE CLI, please follow the below steps:
Step 1: Open the AZURE CLI and login to your account using the command:
az login
Step 2: Check if an Activity Log Alert exists for Create or Update SQL Server Firewall Rule using the command:
az monitor activity-log alert list --query "[?contains(condition.allOf[0].field, 'Microsoft.Sql/servers/firewallRules/write')]"
This command will list all the Activity Log Alerts that exist for Create or Update SQL Server Firewall Rule.
Step 3: If an Activity Log Alert does not exist, create one using the command:
az monitor activity-log alert create --name <alert_name> --description <alert_description> --condition "category='Administrative' and resourceType='Microsoft.Sql/servers/firewallRules' and operationName='Microsoft.Sql/servers/firewallRules/write'" --action email <email_address> --enable
Replace <alert_name>
with the name of the Activity Log Alert you want to create, <alert_description>
with the description of the Alert, and <email_address>
with the email address where you want to receive the alert.
Step 4: Verify that the Activity Log Alert has been created using the command:
az monitor activity-log alert list --query "[?contains(condition.allOf[0].field, 'Microsoft.Sql/servers/firewallRules/write')]"
This command will list all the Activity Log Alerts that exist for Create or Update SQL Server Firewall Rule, including the one you just created.
By following these steps, you can ensure that an Activity Log Alert exists for Create or Update SQL Server Firewall Rule in AZURE using AZURE CLI.
To remediate the misconfiguration “Ensure Activity Log Alert exists for Create or Update SQL Server Firewall Rule” for Azure using Python, you can follow the below steps:
- First, you need to import the required libraries. You can use the Azure SDK for Python to interact with Azure resources. Install the Azure SDK for Python using the command
pip install azure
.
from azure.identity import DefaultAzureCredential
from azure.mgmt.monitor import MonitorManagementClient
from azure.mgmt.resource import ResourceManagementClient
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.storage import StorageManagementClient
from azure.monitor.query import logs
- Next, you need to authenticate to Azure using the
DefaultAzureCredential
class. This class provides a way to authenticate to Azure using various credentials like Azure CLI, Managed Identity, Service Principal, etc. You can use the following code to authenticate:
credential = DefaultAzureCredential()
- Once you have authenticated, you can create instances of the required clients to interact with Azure resources. In this case, you need to create instances of
MonitorManagementClient
,ResourceManagementClient
,SqlManagementClient
, andStorageManagementClient
. You can use the following code to create instances of these clients:
monitor_client = MonitorManagementClient(credential, subscription_id)
resource_client = ResourceManagementClient(credential, subscription_id)
sql_client = SqlManagementClient(credential, subscription_id)
storage_client = StorageManagementClient(credential, subscription_id)
- Next, you need to check if an Activity Log Alert exists for Create or Update SQL Server Firewall Rule. You can use the
logs
module from theazure.monitor.query
library to query the Azure Monitor logs. You can use the following code to query the logs:
query = "AzureActivity " \
"| where OperationNameValue == 'Microsoft.Sql/servers/firewallRules/write' " \
"| where ResourceProviderValue == 'Microsoft.Sql' " \
"| where ResourceGroup == '{}' " \
"| where ResourceId == '{}' " \
"| where Level == 'Critical' " \
"| where StatusText contains 'Created' or StatusText contains 'Updated' " \
"| project ResourceId, Level, StatusText, ActivityDateTime" \
.format(resource_group_name, sql_server_id)
logs_query_result = logs.query_workspace(workspace_id, query)
- If an Activity Log Alert does not exist for Create or Update SQL Server Firewall Rule, you need to create one. You can use the
monitor_client.alert_rules
object to create an Activity Log Alert. You can use the following code to create an Activity Log Alert:
alert_rule_name = "SQL Firewall Rule Creation or Update Alert"
alert_rule_description = "Alert when a SQL Server Firewall Rule is created or updated"
alert_rule_criteria = {
"odata.type": "Microsoft.Azure.Monitor.Query.Models.RuleManagementEventRuleDataSource",
"query": "AzureActivity " \
"| where OperationNameValue == 'Microsoft.Sql/servers/firewallRules/write' " \
"| where ResourceProviderValue == 'Microsoft.Sql' " \
"| where ResourceGroup == '{}' " \
"| where ResourceId == '{}' " \
"| where Level == 'Critical' " \
"| where StatusText contains 'Created' or StatusText contains 'Updated' " \
"| project ResourceId, Level, StatusText, ActivityDateTime" \
.format(resource_group_name, sql_server_id)
}
alert_rule_action_group_id = "/subscriptions/{}/resourceGroups/{}/providers/Microsoft.Insights/actionGroups/{}".format(subscription_id, resource_group_name, action_group_name)
alert_rule = monitor_client.alert_rules.create_or_update(resource_group_name, alert_rule_name, {
"location": location,
"description": alert_rule_description,
"isEnabled": True,
"condition": {
"dataSource": {
"odata.type": "Microsoft.Azure.Monitor.Query.Models.RuleManagementEventRuleDataSource",
"query": alert_rule_criteria
},
"operator": "GreaterThan",
"threshold": 0,
"windowSize": "PT5M"
},
"actions": [
{
"actionGroupId": alert_rule_action_group_id,
"webhookProperties": {}
}
],
"tags": {}
})
In the above code, you need to replace the resource_group_name
, sql_server_id
, location
, subscription_id
, action_group_name
with the actual values.
- Finally, you can test the Activity Log Alert by creating or updating a SQL Server Firewall Rule. Once the rule is created or updated, you should receive an alert in the configured action group.
These are the steps to remediate the misconfiguration “Ensure Activity Log Alert exists for Create or Update SQL Server Firewall Rule” for Azure using Python.