en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
דצמ1

Written by: ronen ariely
01/12/2018 04:42 RssIcon

Hey you...
Did you came here in order to read about the awesome option off using Azure Lock resources?

Well you probably already familiar with as Microsoft technologies freak. I lecture in social events about Microsoft technologies and products, I usually select to use Microsoft Solutions over other solutions available in the market, and basically I am pro-Microsoft and therefore, when I tell you in the forum that something is totally useless then you can count on it! I don't get why this issue raise in the forum so many times and after I explain this point, people continue to recommend Azure Locks as a solution to prevent database deletion. This post is written following another discussion on this topic at the MSDN Azure SQL Database forum.

Let me start from the bottom line!
Azure Locks are totally useless in preventing deletion of Azure database!

Let's go and explain a bit more and demonstrate the issue

Theory & Explanation

There is a very common mistake which I saw many times in the forums. People are using (or advising others to use) Azure Resource locks in order to prevent deletion of Azure SQL database or other services.Moreover! The official document declare this:

"As an administrator, you may need to lock a subscription, resource group, or resource to prevent other users in your organization from accidentally deleting or modifying critical resources."

In fact, the Azure Lock feature only relevant in the Azure Engine level (or to the Azure scope, or to "Azure point-of-view"), and it has no impact on what we can or cannot do using direct communication with inside the service element itself.

For example, We can add Lock on Virtual Machine(VM), which means that the Virtual Machine will be locked from actions like deletion of the VM, but it will not prevent any changes within the VM like deletion of file within the VM by an internal user of the VM.

OK, the example of VM and a file within the VM seems pretty clear to most clients, which is why I start with this example, but moving to Azure SQL Database, it is more confusing people since it is more complex.

Using VM it's clear when we are talking about managing the service (managing the "Azure Virtual Machine service") and when we are using the service to manage it's content (files inside the VM). In fact the Azure Engine does not even familiar with the files inside the VM. All the files are actually part of a single file which serves as the Virtual machine Disk (this is the idea of Virtual Disks).

Using Azure SQL Database it is more complex since the service (Azure Database) is also available as "content" of another service - Azure SQL Virtual Server.

The document clearly states that "When you apply a lock at a parent scope, all resources within that scope inherit the same lock"

In one side the Azure SQL Database is a resource within the Azure Virtual Server, but at the same time it is actually separate entity within a real SQL Server instance. The Virtual Server only provide a virtual wrapper for managing purpose of the databases. For example within the same Virtual Server we can even configure databases related to totally different type of servers which we have behind the scenes like Azure SQL Database and Azure Data Warehouse.

This is the source of the un-expected behavior!

Using Azure Locks on Database will prevent the Azure users from deleting the Azure Database Service, for example using the Azure Portal. With that being said, it will not prevent deleting the database using Transact SQL by internal user of the Server.

Let's see it in action

Demo

1. I created new database named RonenSayThatResourceLocksIsUseless or shorter name ResourceLocksIsUseless

2. I add Resource locks using the portal

3. I try to delete the database using the portal and I get error

Awesome... It seems like I am safe from deletion

or am i?!?

4. Let's connect the server using SSMS and execute simple DROP DATABASE query

Oh My God...
Microsoft Document promise that My service is safe! BUT

I lost my database :-)

Conclusions

Is clear that the documentation confuses and in my opinion it is a clear mistake in the description. The Azure Lock service does not prevent deleting or modifying resources in general! It is only preventing changes made by Azure scope services.

Resource locks are TOTALLY USELESS against changes which are done within the service like deletion of files in Virtual Machine or using Transact SQL to delete a database within the Server.

Tags:
Categories: SQL