Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Create Alert for "Delete Azure SQL Database" Events

Trend Micro Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 750 automated best practice checks.

Risk Level: High (not acceptable risk)
Rule ID: ActivityLog-022

Ensure that a Microsoft Azure activity log alert is fired whenever a "Delete Azure SQL Database" event is triggered within your cloud account. An Azure activity log alert fires each time the action event that matches the condition specified in the alert configuration is triggered. The alert condition that this conformity rule searches for is "Whenever the Activity Log has an event with Category='Administrative', Signal name='Delete Azure SQL Database (Microsoft.Sql/servers/databases)'".

This rule resolution is part of the Conformity Security & Compliance tool for Azure.

Security

Using Microsoft Azure Monitor service to detect "Delete Azure SQL Database" events will help you reduce the time needed to mitigate any accidental or intentional deletion of Azure SQL databases within your Azure cloud account and improve the databases security and availability.


Audit

To determine if there are any activity log alerts created for "Delete Azure SQL Database" events in your Microsoft Azure cloud account, perform the following actions:

Using Azure Portal

01 Sign in to Azure Management Console.

02 Navigate to Azure Monitor blade at https://portal.azure.com/#blade/Microsoft_Azure_Monitoring/AzureMonitoringBrowseBlade/overview.

03 In the navigation panel, select Alerts to access all the alerts available in your Azure account.

04 On the Alerts page, click on the Manage alert rules button from the dashboard top menu to access the alert rules management page.

05 On the Rules page, select the subscription that you want to examine from the Subscription filter box and the Enabled option from the Status dropdown list, to return all the active alert rules created in the selected subscription.

06 Click on the name of the alert rule that you want to examine.

07 On the selected alert rule configuration page, check the condition phrase available in the Condition section. If the phrase is different than Whenever the Activity Log has an event with Category='Administrative', Signal name='Delete Azure SQL Database (Microsoft.Sql/servers/databases)', the selected alert rule is not designed and configured to fire whenever "Delete Azure SQL Database" events are triggered.

08 Repeat step no. 6 and 7 for the rest of the alert rules available within the selected account subscription. If none of the verified rules contain the right condition, there are no activity log alerts available for "Delete Azure SQL Database" events in the selected Microsoft Azure account subscription.

09 Repeat steps no. 5 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure PowerShell

01 Run monitor activity-log alert list command (Windows/macOS/Linux) using custom query filters to get the IDs of the active activity log alert rules available in the current Azure subscription:

az monitor activity-log alert list
	--query '[?(enabled==`true`)].id'

02 The command output should return the requested activity log alert rule IDs:

[
"/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/cloud-shell-storage-westeurope/providers/microsoft.insights/activityLogAlerts/cc-create-update-sql-database-alert",
"/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/cloud-shell-storage-westeurope/providers/microsoft.insights/activityLogAlerts/cc-create-update-vm-alert"
]

03 Run monitor activity-log alert show command (Windows/macOS/Linux) using the ID of the alert rule that you want to examine as identifier parameter and custom query filters to list the condition(s) defined for the selected activity log alert rule:

az monitor activity-log alert show
	--ids "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/cloud-shell-storage-westeurope/providers/microsoft.insights/activityLogAlerts/cc-create-update-sql-database-alert"
	--query 'condition'

04 The command output should return the condition metadata for the selected alert rule:

{
  "allOf": [
    {
      "containsAny": null,
      "equals": "Administrative",
      "field": "category",
      "odata.type": null
    },
    {
      "containsAny": null,
      "equals": "Microsoft.Sql/servers/databases/write",
      "field": "operationName",
      "odata.type": null
    }
  ],
  "odata.type": null
}

Check the monitor activity-log alert show command output for a JSON object with the "field" property set to "operationName". If the object's "equals" property is not set to "Microsoft.Sql/servers/databases/delete", the selected activity log alert rule is not designed to fire whenever "Delete Azure SQL Database" events get triggered.

05 Repeat step no. 3 and 4 for the rest of the alert rules available in the current subscription. If none of the verified rules contain the right condition, there are no activity log alerts available for "Delete Azure SQL Database" events within the selected Azure account subscription.

06 Repeat steps no. 1 – 5 for each subscription available in your Microsoft Azure cloud account.

Remediation / Resolution

To create a Microsoft Azure activity log alert for "Delete Azure SQL Database" events, perform the following actions:

Using Azure Portal

01 Sign in to Azure Management Console.

02 Navigate to Azure Monitor blade at https://portal.azure.com/#blade/Microsoft_Azure_Monitoring/AzureMonitoringBrowseBlade/overview.

03 In the navigation panel, select Alerts to access the alerts available in your Azure cloud account.

04 On the Alerts page, click on the Manage alert rules button from the dashboard top menu to access the alert rules management page.

05 On the Rules page, select the Azure account subscription where you want to create the new alert rule, from the Subscription filter box.

06 Click New alert rule button from the dashboard top menu to start the alert rule setup process.

07 On the Create alert rule page, perform the following actions:

  1. In the Scope section, click Select resource and configure the target subscription that you want to monitor. Once the appropriate Azure subscription is selected, click Done.
  2. In the Condition section, click Select condition to configure the alert rule condition. On Configure signal logic panel, find and select the signal with the name Delete Azure SQL Database (Microsoft.Sql/servers/databases). To obtain the right configuration for the condition (i.e. Whenever the Activity Log has an event with Category='Administrative', Signal name='Delete Azure SQL Database (Microsoft.Sql/servers/databases)'), leave the signal default settings unchanged, then click Done to generate the rule condition.
  3. In the Action group section, click Select action group to choose an existing action group to attach to the new alert rule or click Create action group button to create a new one. An action group is a collection of alert notification settings defined for the selected Azure subscription. Microsoft Azure Monitor service uses action groups to send notifications to privileged users when specific alerts are triggered.
  4. In the Alert rule details section, provide a name for the new rule in the Alert rule name box, type a short description in the Description box, and choose the resource group in which the new alert will be created, from the Save alert to resource group dropdown list.
  5. Make sure that Enable alert rule upon creation option is set to checked, then click Create alert rule to finish the alert rule setup process.

08 Repeat steps no. 5 – 7 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI and PowerShell

01 Run monitor activity-log alert create command (Windows/macOS/Linux) to create a new Azure activity log alert that fires whenever a "Delete Azure SQL Database" event is triggered in the current Microsoft Azure cloud subscription:

az monitor activity-log alert create
	--name cc-delete-sql-database-alert
	--description "Alert triggered by Delete Azure SQL Database events"
	--resource-group Default-ActivityLogAlerts
	--action-group "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourcegroups/default-activitylogalerts/providers/microsoft.insights/actiongroups/cloudconformity%20action%20group"
	--condition category=Administrative and operationName=Microsoft.Sql/servers/databases/delete

02 The command output should return the configuration metadata for the new activity log alert:

{
  "actions": {
    "actionGroups": [
      {
        "actionGroupId": "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourcegroups/default-activitylogalerts/providers/microsoft.insights/actiongroups/cloudconformity%20action%20group",
        "webhookProperties": null
      }
    ]
  },
  "condition": {
    "allOf": [
      {
        "containsAny": null,
        "equals": "Administrative",
        "field": "category",
        "odata.type": null
      },
      {
        "containsAny": null,
        "equals": "Microsoft.Sql/servers/databases/delete",
        "field": "operationName",
        "odata.type": null
      }
    ],
    "odata.type": null
  },
  "description": "Alert triggered by Delete Azure SQL Database events",
  "enabled": true,
  "id": "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/Default-ActivityLogAlerts/providers/microsoft.insights/activityLogAlerts/cc-delete-sql-database-alert",
  "identity": null,
  "kind": null,
  "location": "Global",
  "name": "cc-delete-sql-database-alert",
  "resourceGroup": "Default-ActivityLogAlerts",
  "scopes": [
    "/subscriptions/1234abcd-1234-abcd-1234-abcd1234abcd/resourceGroups/Default-ActivityLogAlerts"
  ],
  "tags": {},
  "type": "Microsoft.Insights/ActivityLogAlerts"
}

03 Repeat step no. 1 and 2 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Feb 3, 2020

Unlock the Remediation Steps


Free 30-day Trial

Automatically audit your configurations with Conformity
and gain access to our cloud security platform.

Confirmity Cloud Platform

No thanks, back to article

You are auditing:

Create Alert for "Delete Azure SQL Database" Events

Risk Level: High