Enable SQL MI logs related database drop/delete activity

Question

We have an Azure SQL MI having number of databases on it . We have to enable audit logs of SQL MI from diagnostic settings and also enabled logs by executing queries on MI and sending those logs to event hub. We are getting up logs (select, Execute) but not able to see the logs related to database drop/delete activity

the blog (https://docs.imperva.com/en-US/bundle/cloud-data-security/page/78051.htm) I am using the for enabling audit log in Azure SQL MI, what I am missing any step plus we need to see/visualize the data/request that are coming to eventHub.

Answer 1

Create a new alert using portal or Azurecli

• Navigate to [Monitor Alerts]>>Select New alert rule>> Under Scope, click Select Resource>> subscription and click Done>> Under Condition, click Select Condition>>“Create/Update server firewall rule” >> Select Done >> Under Action group, click Select action group >> Select the desired action group to attach to the alert rule, or create one if needed, and click Select >> Enter an alert rule name and description >>Select a resource group>>Click Create alert rule.

• Reference taken from Fugue.

  az monitor activity-log alert create --name createUpdateSQLServerFirewallRule \ --resource-group <resource_group_name> \ --condition category="Administrative" and \ operationName="Microsoft.Sql/servers/firewallRules/write" \ --scope "/subscriptions/<subscription_id>" \ --action-group <action_group>

• For database, deletion refer this.

  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-abcd1234abcd1234abcd/resourcegroups/defaultactivitylogalerts/providers/microsoft.insights/actiongroups/cloudconformity%20action%20group" --condition category=Administrative andoperationName=Microsoft.Sql/servers/databases/delete

• For SQL server Deletion: