By: Mohammed Moinudheen | Comments (4) | Related: > Azure
Problem
I have a number of critical Azure SQL Server resources to manage and I want to ensure that users don’t accidently delete or update the resources. Is there any easy way this can be accomplished in Azure?
Solution
This tip assumes that you already have resources in your Azure environment. If you are new to Azure, you can subscribe to a trial account here which will give you access to Azure services for an year and a credit of USD $200. In this tip, we will learn about the concept of locks that can be used to prevent users from modifying or deleting the resources.
What are Azure locks?
Azure has basically two kinds of locks known as read-only and delete lock.
- Read-only lock is something similar to assigning a reader role for your users. The authorized users will not be able to modify the resource, but they can only read from the resource.
- With delete lock, authorized users will be able to read and modify the resource, but will not be allowed to delete the resource.
Demo of a Read-only Lock Using the Azure Portal
This tip assumes you already have an Azure SQL database configured. You can refer this tip which has a detailed description on how to create a SQL database in Azure. On the screenshot below, you can see that there is an Azure SQL database already configured.
There is also an option on the left-hand pane called "Locks". Click on the "Locks" option. On the "Locks" option page, you have the option to add a new lock.
Click on the option to add a new Lock
Once you click on the option to "Add" a new lock, you will see this option.
Here, you can see two lock types: Read-only and Delete
You can select the option "Read-only" and provide a suitable name as shown. Once done, click on "OK".
Once you have clicked "OK", you will be able to view the saved lock.
Test the Read-only Lock
Now, that the read-only lock is created, you can test the Azure SQL database to see if the read-only database lock is working as expected. Click on the "Tags" option that you can see on the left-hand pane. Here, you can view details of the tags that are already assigned to the SQL database resource.
Now, let us try to create a new tag name\value pair assignment and then click on save. You will notice that the operation to add the new tag assignment will fail with an error message. Here in the screenshot, you can see the error message which states that the "Resource refused tag assignment (possibly locked))" thereby confirming that the resource is indeed locked. In our case it is a read-only lock and the resource cannot be modified in anyway.
Operations within the SQL database
You will notice that the read lock was basically at the resource level. However, the operations within the SQL database wouldn’t be impacted by this read lock. You can consider an example of creating a sample table in the SQL database and the operation will succeed as shown. You can click on the option "Query Edition (preview)" and then run a command to create a table as shown below.
This command will complete successfully and has no impact on the read-only lock that was set at the SQL database resource level. Resource operations within the database will continue to perform as normal.
Demo of the Delete Lock Using the Azure Portal
You can go ahead and delete the read-only lock that was created on the Azure SQL database. For deleting the lock, click on the "Lock" option on the resource and click on the right-hand corner ellipsis and select "Delete". This will delete the read-only lock that was assigned to the resource.
Next, create a new "Delete" lock as shown. Click on "OK" once done.
With this, a new "Delete" lock is assigned to the Azure SQL database.
Test the Delete Lock by adding few tag assignments to the Azure SQL database
With the delete lock type, modifications are allowed to the resource, but the resource itself can’t be deleted by authorized users. Let us try to add a new tag assignment like how we tested for the read-only lock type.
Enter the details for the tag name\value pair and click on the "Save" option. You will see that the new tag name\value pair gets saved successfully for the Azure SQL database.
In the demo for the "Read-only" lock type, you noticed that the new tag assignment failed as changes to resources were not allowed.
Check delete operations within an Azure SQL database that has a delete lock assigned
For testing the read-only lock type within the Azure SQL database, you created a sample table named test. In this demo, let us delete the test table from the database.
Here, you can see that the test table is successfully deleted from the Azure SQL database and that the delete lock type has no impact on the delete operations within the Azure SQL database.
Test the delete lock by attempting to delete the Azure SQL database
Now, that you have tested modifications to the resource and also internal operations within the resource, in this section let us try deleting the Azure SQL database itself from the portal. Click on the "Overview" option that is visible on the left-hand pane.
Once done, click on the option to "Delete" the Azure SQL database.
Provide the name of the database and then click on the "Delete" option to remove the Azure SQL database.
You can see an error message confirming failure of the delete operation.
Next Steps
- In this tip, you learned about the concept of Azure Locks, there are two types of locks: read-only locks and delete locks
- Read-only lock is similar to assigning a user reader access which prevents any modifications to the Azure resource
- Delete lock has higher privileges as modifications are allowed on the resource, but the resources themselves cannot be deleted once delete locks are assigned
- In the demo, you learned that there will be no impact on the internal resource operations when these locks are applied, either read-only or delete
- You need owner privileges in order to assign these locks
- The locks can be assigned at the resource level, resource group level and also at the subscription level
- Try out this demo on your own test Azure environment before applying in production
- Refer other articles available on MSSQLTips related to Azure
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
