Rajendra Gupta
Modify DB Service tier

Safeguard your Azure SQL Database to prevent unexpected changes

July 30, 2021 by

This article explores locks to prevent unexpected changes in Azure SQL Database resources.

Introduction

Suppose you manage multiple critical Azure SQL Server resources. As an administrator, you don’t want any user to delete or update the resource accidentally. For example, you do not want anyone to modify the service tier of the Azure database. Similarly, consider the scenario where a user accidentally deletes the Azure SQL Server or Azure SQL Database. We cannot afford application downtime until you recover your database. Definitely, you want to prevent any such accidents.

This article configures the available locks and their impacts for safeguarding Azure SQL databases or Azure resources.

Azure lock types

Azure provides the ability to lock the subscription, resource group or specific resource for any accidental modifying or deleting the critical resources. It provides the following locking mechanism.

Read-only

  • The Read-Only lock specifies that an authorized user can access the resource, but they cannot update or delete activity. It is similar to assigning a reader role for users

Delete lock

  • The delete lock allows users to read, modify the resource; however, they cannot drop the resource
  • Note: These locks do not restrict users from creating, updating or deleting objects in an Azure SQL Database

You can apply locks at a parent scope or an individual resource. The child resource inherits the same locks. For example, if you place a lock on the Azure SQL Server level, all databases inherit the lock configuration.

  • Note: The users with the Owner and User Access Administrator role can create or delete the locks

Configuration of the Read-only Lock using the Azure Portal

Suppose we want to configure the locks for Azure SQL Server. To configure the locks using the Azure portal, navigate to the Settings blade and select Locks.

Currently, the resource has no locks configured, as shown below.

Lock configurations

To configure a lock, click on Add. In the Add lock box, enter the following information.

  • Lock name: Enter a suitable name for the lock
  • Lock type: Select the lock type from Read-only or Delete from the drop-down values
  • Notes: Enter the notes for reference purposes. It is an optional field

Create a new Read-only lock

Click Ok, and you have configured a read-only lock to protect Azure SQL Server resources. The scope column specifies the resource on which the lock is configured.

Configured lock

We configured the lock at Azure SQL Server. Therefore, it inherits the lock configuration of all databases in this server. To validate, click on Locks for the Azure SQL Database.

It displays the parent lock with a warning message –“ Parent resource lock can’t be edited here”. You cannot make any changes in the parent resource lock from the child resource.

warning message

Test the Read-only lock functionality

Once we have configured the read-only lock on Azure SQL Server, let’s try to delete an Azure SQL Database from the Azure portal. On the Overview page, click on Delete as highlighted in the following image.

Test the Read-only lock functionality for Azure SQL Database

Azure gives you a warning message because this operation is irreversible. Enter the database name to delete it.

Delete the database

Click on Delete. You get a dialogue box stating that the delete database operation is failed. The message states that you need to remove the lock before trying to delete the database.

Failed to delete database

Similarly, suppose a user tries to modify the service tier from Basic to Standard for Azure SQL Database. We don’t want users to change the service tier because it incurs higher costs for you.

Current pricing tier: Basic

Current pricing tier

To modify the service tier, click on Basic and select your required pricing tier. Here, we choose the Standard (S2) service tier.

Modify DB Service tier

Click on Apply. You get the scale database error because Azure failed to scale Azure SQL Database from Basic 2 GB storage to Standard S2: 50 DTUs,2 GB storage for database [AzureDemoDatabase]. Since we have placed a read-only lock on Azure SQL Server, it did not allow database scaling.

scale database error

As specified earlier, these lock does not restrict users from performing create, alter, delete statements in the SQL Server database. For example, as shown below, the user with appropriate permission can successfully drop the table inside the database.

Drop tables

To delete a specific lock, click on the Delete button in front of the lock. It will delete the read-only lock that was assigned to the resource.

delete a specific lock

Configuration of the delete Lock using the Azure Portal

In this part, create a new Delete lock in the Azure portal similar to the read-only lock.

Configuration of the delete Lock using the Azure Portal

Once we have configured a lock, it shows up in the console, as shown below.

View lock properties

Test the delete lock functionality

Now, the user cannot delete the database or the Azure SQL Server resources.

Delete Azure resources

We cannot delete the Azure SQL Database because it is locked, as shown in the following notification message.

Failure message

However, with this delete lock, the user can modify the resource. For example, let’s try to change the pricing tier from Basic to Standard.

Successfully scales database

Lock resources using the Azure PowerShell

Previously, we used the Azure portal to specify the lock resource configuration in the read-only or delete mode. This section configures the lock configuration for the Azure SQL Server or Azure SQL Database using the Azure PowerShell. You use the Azure cloud shell from the Azure portal for running the PowerShell, or CLI commands.

Lock resources using the Azure PowerShell

In the Azure portal, click on the cloud shell icon, configure the storage account, and launch the PowerShell terminal as shown below.

Azure CloudShell

In the Azure PowerShell, we can use the New-AzResourceLock cmdlet for the configuration.

To get all locks information in Azure subscription, use the Get-AzResourceLock cmdlet.

Previously, we configured the “DBDeleteLock” lock using the Azure portal. Therefore, the Get-AzResourceLock returns the name, locked resource name, its properties. As shown below, it uses Microsoft.Authorization/locks Azure resource extension.

Microsoft.Authorization

Suppose we want to place a delete lock at the Azure resource group [MyAzureLabSQL].

  • Note: In the Azure portal, you see Delete lock type, but the Azure PowerShell accepts a value as CanNotDelete

Run the above command in the PowerShell, and it prompts you for confirmation. In the query output, it returns the lock group name, resource type, and its properties.

Specify CannotDelete parameter value

Re-run the Get-AzResourceLock command, and you have two resource locks – LockGroup and DBDeleteLock, as shown below.

Re-run the Get-AzResourceLock command

You can verify it from the Azure portal as well.

Verify locks

Suppose we want to remove the lock named LockGroup using the Azure PowerShell. To do so, first, retrieve the lock id and then remove it using the Remove-AzResourceLock cmdlet.

Similarly, to remove the lock from a resource, use the following PowerShell command.

Lock resources using the Azure CLI

In the Azure CLI, you can use az lock list to list out the existing resource locks in the JSON format.

Lock resources using the Azure CLI

Similarly, use the az lock create command to configure a new lock with a specific lock type- CanNotDelete.

az lock create command

Verification

The following script creates a read-only lock on the [MyAzureLabSQL] resource group.

creates a read-only lock

View read-only lock in Azure portal

  • Note: You can refer to az lock command documentation for more details

Conclusion

This article explored the concept of Azure locks for safeguarding your Azure resources against unexpected changes. Read-only locks prevent users from doing any resource modification. Similarly, the delete lock prevents the accidental removal of resources. You can use Azure portal, Azure CLI as well as Azure PowerShell for it. You can use these locks at the resource, resource group, or subscription level. The lock mechanism has no impact on the Azure SQL database DML or DDL operations. The locks at the resource level are for protecting unintentional modification or deletion of the resources.

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

276 Views