ינו19

Written by: ronen ariely
19/01/2018 06:36 

Off-Topic

Hi all, 

I hope you have a great day. 

We are in the middle of what the news in Israel call “one of the strongest storm of the decade”! It is really cold here and the wind is so hard that some furnitures were flying from the wind in our garden. It is 05:09 AM at the time I started to write first draft of this post, and I woke up because of the wind noise which is so loud…

You probably heard about the hurricanes Katia, Irma, and Jose, and I bet some of you are thinking now that it is sound familiar from other places in the world that suffer from hurricanes and tornados. You might think about reports in the news that speak on places with temperature of minus 40 degrees and so on…

OK, so let me put it into perspective :-)

According to google, we have “Light Thunderstorms and Rain” (I did not notice any rain in the last hour), this is the coldest time of the day and the temperature is only 12°C (54 °F), the wind speed is only 21 mph, and the furniture that was flying in our garden was a plastic chair which was not connected to anything… 

Well the weather in Israel is always great, and even when you hear about a storm it is probably better weather then most “normal” days in other places in the world, so you are always welcome to visit Israel, and you can use the opportunity to join us to the next SQLSATURDAY in Israel :-)

 

Azure Lock resources - introduction

Azure lock enable us to lock a subscription, resource group, or specific resource in order to prevent users from accidentally deleting or modifying critical resources. We can set the lock level to CanNotDelete or ReadOnly.

* Read more in the official documentation here.

Several minutes ago I notice this thread in the MSDN Azure SQL Database Forum.

Basically the OP, is locking for a way to prevent DB Deletion from SQL Server management studio (SSMS), and the first recommendation he got was to use Azure Lock on the database.

Unfortunately this solution does not fit the requested needs, and once the user found that this does not prevent DB Deletion a nice discussion stated pointing us to the feeling that this is a bug in the Azure Lock. In this short post I want to explain the behavior and why in fact I think that this is the expected behavior at this time.

 

When Everything work as expected

Let's start which the documentation and what seems to be the expected behavior in first glance. According to the documentation “ReadOnly lock on a SQL Database prevents you from deleting or modifying the database”, but in fact this is not equate!

* Everything that I write about was tested at the time I write the post, and might be changed in the future!

Let’s test it!

Create new Lock on New database

Step 1: Create New database named “test01”

Step 2: navigate to the database in the Azure Portal

Step 3: In the main menu of the database options (where you see the “Overview” option)  scroll down to the option “Lock”

Step 4: Add new lock type Read-Only

DONE!

Testing our Lock using the Azure portal

Step 1: Navigate back to the database in the Azure portal

Step 2: Delete the database (Using the portal!)

As expected the database is locked and attempt to delete it using the portal rise an error!

Is this mean that lock works well and the database is safe from deletion by mistake?!?

 

When Stuff do not behave as expected

Let’s examin the behavior when we are using queries to drop the same database.

Step 1: Open the SQL Server management Studio and connect to your Azure Server

Step 2: Execute simple DROP DATABASE query

What the hell! We get inform that the “Commands completed successfully”

Lest check if the Database exists using simple query

THE DATABASE WAS DELETED!

You can confirm it using the Azure portal as well :-)

So what happens here?!?

 

My 2 Cents – this is actually the expected behavior

If we will go back to the documentation which I mentioned above you can notice this statement:

“Resource Manager locks apply only to operations that happen in the management plane, which consists of operations sent to https://management.azure.com. The locks do not restrict how resources perform their own functions.”

When we use Azure portal or PowerShell for example then the lock works as expected, but by executing a query on the database we actually used the internal server behavior which is the equivalent of the resource own functions. We do not use “operations sent to https://management.azure.com”. Therefore, the Azure traces does not follow these actions and the database was not safe from deletion.

The lock on Database level will not help us to prevent dropping the database using queries!

We can notice that this bevavior is noy limited only to Azure Locks but to any element based on "operations sent to https://management.azure.com" and the events which Azure can monitor.

Another "issue" which was mentioned in the same forum's thread is directly related to this behavior. Marcin Policht noticed that he don't see any entries in the Activity Log that would indicate that the database has been dropped. The Activity Log which collect the information regarding Azure Events do not have any indication how resources perform internally using their own functions. It does not have any indication that we dropped the database using a query. But as expected if you delete a database using the Azure portal, then you will notice the information in the Activity Log as expected :-)

It's IMPORTANT opportunity to mention that the same behavior implemented in the Activity log alert from the same reason. Therefore, if you created an Activity log alert to inform you  when a database is changes then you should know that you should not count on this alert to work in cases that the user execute a remote query on the database.

 

conclusion

Operations sent to Azure (https://management.azure.com) can trigger events which we can use to lock subscription, resource group, or specific resource including Azure SQL databases, using the Azure Lock. We can monitor these requests using the Azure Activity log, and we can create Azure Activity Log Alerts in order to receive notifications when specific changes occur on resources or a service health event occurs. 

But seems like commands sent by queries are equivalent of the resource own functions and will not trigger any activity log, will not be locked and will not be trigger alerts.

I cannot say here if this is a bug or by design but I can say that this is the way it's seem that these Azure's element behaves, at least at this time.

 

I hope this short post was useful, and you understand Not to count on locks to prevent database deletion (at least not as it is behave at this time).