Protecting deletions of Azure SQL Resources


In Azure, there is a feature called Azure Resource Management Locks which can help prevent accidental deletion resources such as a SQL Server by adding a level of protection. While it may not happen all the time, sometimes we make mistakes and delete the wrong server. Be it that there was a typo in an API call or you thought you were deleting the server that was in a different region. It happens. With the mentioned lock, it can prevent deletions on locked resources unless explicitly removed.

These locks can be configured within the properties of most Azure services. While available at multiple levels such as a given Resource group or even Subscription, we are going to focus on an Azure SQL Server specifically.

Under the different options for your SQL Server, you will see the ‘Locks’ setting to create locks. There, you can select the Add option along the top.

You will be prompted with the ability to name the lock, provide the type (currently you can set it to Delete or Read-Only) and then a note for the lock. In this case I am going to create a lock that will prevent the server from being deleted and provide a note that it should never be deleted.

After creation, we can now see it listed for this server.

If submitting a delete request with this lock in place on this server, you are now hit with an error indicating it is locked.

At this point, you will need to remove the lock in order to be able to delete the server which will give the opportunity to review the reason the lock is in place and second check that this server is wanting to be deleted.

To remove the lock, from the portal we select the locks for the server and by clicking on the ellipsis to the right of the lock, an option to Delete is there. Select and you should see the lock removed.

This server is now able to be deleted again.

All of these steps and behaviors are the same when using other methods such as Azure Powershell cmdlets. You can View, Create, Update and Delete Locks using the below cmdlets if you prefer that over the portal.

Get-AzureRmResourceLock

Remove-AzureRmResourceLock

New-AzureRmResourceLock

Set-AzureRmResourceLock

If you submitted a delete request for the server via the Powershell cmdlets you will be faced with the same error that was seen above when trying to delete the server with this lock through the portal.

It is important to note that the locks placed on resources will cover their child resources as well. So in this case, adding to a SQL server will also place a delete block on all the databases for that server. However, you are not required to apply this at the Azure SQL Server level and can be set on Azure SQL Databases specifically. It is very important to note that this does not prevent T-SQL deletions of the database. While Azure delete operations such as through the portal will be blocked, with this lock in place, a simple DROP DATABASE [DATABASENAME] by a user with proper permissions on the SQL Server will not be. Fortunately in those situations, the ability to restore the deleted database should be available from the server it was on.


Comments (0)

Skip to main content