In [Part 1] of this series, we looked at ways to leverage Azure Backup Soft Delete, Backup Alert Notifications and Azure Backup Policies to help protect our valuable data in Azure from malicious attacks.
In [Part 2] of this series, we looked at ways to leverage Immutable Blob Storage to protect Azure VMs by copying the VHD files into Azure Storage containers protected with time-based retention policies.
Now we’ve configured Azure Backup with Soft Delete as our first line of defence and protected our VMs with Immutable Blob retention policies, we will now look at protecting other Azure resources to contain irreplaceable data which could also be deleted by a malicious actor.
This multi-part blog series aims to explore some alternative and relatively inexpensive ways to mitigate this risk. In this blog we’ll look at a way to protect Azure SQL from malicious deletion.
AZURE SQL
Azure SQL does not leverage Azure Backup for retention and restores, rather it has its own backup and restore functionality built into the platform.
Azure SQL Restore Points
Azure SQL maintains Point in Time Restore (PIRT) recovery points from 1 to 35 days as well as Long Term Retention (LTR) recovery points for configurable periods. Azure SQL PITR recovery points cannot be deleted, however, LTR recovery points can be deleted.
As with a Recovery Service Vault, there is some level of failsafe, especially with PITR, although no airtight protection. If an Azure SQL Database and its long-term recovery points are deleted, it can still be restored based on available PITR recovery points, however there is a gap in the protection. If the parent SQL Server object is deleted, then everything will be permanently erased and unrecoverable. This is the worst-case scenario we are addressing.
Azure SQL Database Exports
Once again, we are looking to leverage Immutable Blob storage for this scenario. Azure SQL can export a database to a BACPAC file and can be used to restore the database to a new SQL server, which will serve us well for this use case. BACPAC files can be manually exported in the portal, however for this we will want to create a process which can run on a schedule as we described with Azure VM protection in Part2.
Azure SQL Database Preparation
There is another concern here before we begin. We want to make sure the database is in a consistent state when it is exported with no transactions being committed during the export process. In addition, we would rather not run the export against the active database as there could be a performance impact. To satisfy both concerns, we will make a copy of the database first, which will be transactionally intact. Then we will run our export to BACPAC against the copy. Once the export completes, the script will delete the copy. Again, this will mirror the behavior of the process we’ve developed for Azure VMs and their Managed Snapshots in Part 2.
Before we begin there is one critical setting we need in place or the job will fail. The Azure SQL Server which hosts the database(s) will have to allow the export command to run. Navigate to the SQL Server object and click ‘Firewalls and virtual networks’ and make sure ‘Allow Azure services and resources to access this server’ is set to Yes.
Creating a Process
At this point, all the parts are in place to create a script to make copies of the Azure SQL Databases, export those copies as BACPAC files to the immutable storage container and delete the database copies. You can also set this script to run using Azure Automation on a daily schedule and add functionality to purge data out of the retention range, which I haven’t included in the sample as of this writing.
One last note, the sample script stores the username and password for the SQL server directly in the script, which is not good practice. It would be better to store those sensitive secrets in Azure Key Vault.
The script sample can be found at https://github.com/n-hoffman/Azure-Immutable-Backups
I hope you found this series insightful and helpful. Although this series covered two very popular services, VMs and SQL databases, many more Azure resources can be protected in this manner. Be sure to reach out to Insentra if you would like some assistance establishing or hardening your BCDR strategy or enabling any of these processes. You can read more about Disaster Recovery and Backup in Azure from my colleague Sebastian Baszcyj or watch Hambik Matsovian in his Azure AD Security Features Update for Microsoft FastTrack.
