Learning from Expertise #2: Who Dropped my Azure SQL DB?

by Contributed | Feb 22, 2021 | Technology

This article is contributed. See the original author and article here.

Overview:

Azure SQL Database can be dropped through Azure SQL database API call using “Microsoft.Sql/servers/databases/delete” operation via Azure Portal, CLI, Powershell commands or Rest API call one side or at the database level another side using SQL Server Management Studio or T-SQL command: DROP DATABASE database04;

In this article, we will provide a guideline on Azure solutions to secure, protect, recover, audit and monitor Azure SQL DB against unintended deletion.

Solution:

In this section, we are listing Azure solutions to secure Azure SQL DB against unintended deletion, know who the caller for the database deletion, when this happened and how get alerted in such case. Moreover, how to recover your database.

1- Enable Azure SQL Auditing

The best resort to answer the blog main question, who dropped my database? is to enable Azure SQL Auditing, enabling auditing tracks database events and write them to audit log which can be stored into Azure storage account, Log Analytics workspace or Event Hubs.

Leverage the Log Analytics to retrieve and filter the Audit records, the following example is a Kusto Query to get audit data for the dropped database:

let ServerName = "XXXXXXXXXX"; # Change the Server name to Azure SQL Server name 
AzureDiagnostics
| where LogicalServerName_s =~ ServerName
| where Category =~ "SQLSecurityAuditEvents"
| where statement_s contains "DROP"
| project TimeGenerated, event_time_t, LogicalServerName_s, database_name_s, succeeded_s, session_id_d, action_name_s,client_ip_s, session_server_principal_name_s , database_principal_name_s, statement_s, additional_information_s, application_name_s
| top 1000 by TimeGenerated desc

TIP: change the time range to the roughly incident time or you can add clause | where TimeGenerated >= ago(5d)

Query Output:-

You can find more details by expanding the audit data record:-

For more examples, I would recommend review blog for colleague @FonsecaSergio: 

AZURE SQL DB AND LOG ANALYTICS BETTER TOGETHER – PART #3 – Query AUDIT data or Who dropped my TABLE? – Microsoft Tech Community

Additional information regarding Azure SQL audit and Log Analytics:-

Azure SQL Auditing for Azure SQL Database and Azure Synapse Analytics – Azure SQL Database | Microsoft Docs

Overview of Log Analytics in Azure Monitor – Azure Monitor | Microsoft Docs

Log Analytics tutorial – Azure Monitor | Microsoft Docs

2- Create Alerts

You can create an alert at different resource levels from subscription down to Azure SQL resource to get notified in case a database was deleted, you will need to enable alert for activity log “Delete Azure SQL Database (Microsoft.Sql/servers/databases)” at the preference resource level. As shown in below example:-

Choose the activity log Delete Azure SQL Database (Microsoft.Sql/servers/databases) :-

Once the activity log event triggered, you will be able to find more details by open the alert data entry:-

You can learn more on Azure Alerts: Setup alerts and notifications in the Azure portal – Azure SQL Database | Microsoft Docs

3-  Review Activity log

The Activity log is a platform log in Azure that provides insight into subscription-level events. This includes such information as when Azure SQL DB is deleted, You can view the Activity log in the Azure portal or retrieve entries with PowerShell and CLI, to review the activity log from Azure portal, hit the bell icon as shown below:

You can change the time range and add operation filter: “Microsoft.SQL/servers/databases/delete” to get all the deleted SQL databases in certain period, as shown in below figure:-

In case you want to store Activity log entries for longer than 90 days you can send to Log Analytics workspace to enable the features of Azure Monitor Logs, archive to a storage account or stream to an event hub.

Here is a sample Kusto script to retrieve activity log for deleted SQL DB from Log Analytics :-

AzureActivity
| where Resource == "XXXXXXX"  # Change to the database name
| where OperationName == "Delete SQL database"

TIP: change the time range to the roughly incident time or you can add clause | where TimeGenerated >= ago(10d)

You can find more details by expanding the Activity log entry, as shown in below figure:-

Learn more on Azure Activity in Azure Activity log – Azure Monitor | Microsoft Docs

4- Enable Resource Lock

In order to protect your Azure SQL Database from unintended deletion, you can enable Resource lock, it can prevent deletions on the locked resources unless the lock is explicitly removed. It is very important to note that this does not prevent T-SQL deletions of the database.

More information can be found in blog: Protecting deletions of Azure SQL Resources – Microsoft Tech Community

5- Restore the deleted database

Lastly and most importantly, Azure will allow you to recover the deleted database to the deletion time.

By using the Azure portal, open Azure SQL server overview page, and select Deleted databases. Select a deleted database that you want to restore, and type the name for the new database that will be created with data restored from the backup, and click OK. As shown in below figure:-

I hope you find this article helpful. If you have any feedback please do not hesitate to provide it in the comment section below.

Ahmed S. Mazrouh