Using SMO on Azure App Service (Web Apps) with Azure SQL DB

SQL server management object SMO) is one of the widely used way to interact with SQL server due to its feature for directly able to execute mass operation as well as management operation on SQL server, instead of old school way of using queries.

However when using it , it should also need to be consider that on the environment where we are using it is compatible in various aspects or not.

If you have been using existing SMO based project and moving on to Azure App services & facing any issue post deployment in SMO calls.

Or

You are building your application from scratch and planning to deploy it on Azure App services, there here are few things which you should do :

1. Correct Way of Using SMO DLL/Objects :

You should refer to this link, and use the SMO Nuget package to get the SMO dll and create object out of it :

https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects

 

 

2. Connecting to on-Prem SQL database :

To Connect to on-Prem database , you don’t need to do anything explicit in code .By using any of the Network connection method like hybrid connection or Vnet , you will be able to connect your web application on On-Prem SQL database and should be able execute most of the SMO operations.

 

3. Connecting to Azure SQL database :

If you are making a connection with Azure SQL database , then you are not explicitly creating any Vnet or Network connection to connect to Azure SQL database , thus here are few things which you should remember :

(1) Try running same code and application from local machine (visual studio) and see if you are able to perform desired operations , if you can execute the same on local machine but having issue in App service , then move further , else try making your code to work with Azure SQL database.

(2) If your code is working fine in local but having issue on Azure App service , there here are few scenarios which can happen :

· Make sure that You have allowed Azure Service in Firewall setting of Azure SQL database:

 

· The Calls which you are making may be restricted by Azure App service due to Sandbox environment :

To test it , try changing SQL connection to On-Prem SQL database and create hybrid connection with On-Prem machine and see now if the App service is running fine and executing SQL operation.

Interesting things happening here :

The very interesting thing here can bee is that App services being a PaaS environment, runs in a security restricted mode named SandBox and thus it banes some calls for various system , graphics or security related operations.

Read more about sandbox restrictions here

https://github.com/projectkudu/kudu/wiki/Azure-Web-App-sandbox#app-service-plans-and-sandbox-limits

For example , below is a sample code which runs fine when connecting to on-Prem SQL database form Azure App service.

It also runs fine when connecting to Azure SQL database while running code locally.

So you might endup in a confusion , that why the code is not working with Azure SQL db & Azure App service combination .

 

Here is the piece of code emitting above behavior :

In above code, if you look at the line :

 

All of them , internally make calls to fetch the current running server name & IP , and this is a security related call and thus due to this 3 lines of code , the application will break as the call is being restricted by Sandbox , as per sandbox article :

Connection attempts to local addresses (e.g. localhost, 127.0.0.1) and the machine's own IP will fail, except if another process in the same sandbox has created a listening socket on the destination port.Rejected connection attempts, such as the following example which attempts to connect to 127.0.0.1:80, from .NET will result in the following exception:

 

However in case you are using , On-Prem Database , then same piece of code will work as in case of Vnet , Sandbox treats differently to  such calls and thus those calls doesn’t get banned in case of Vnet and works , as mentioned here :

 

Azure Web Apps may set up their virtual networks, or VNets in order to facilitate connectivity between Azure and on-premise intranets. This special case of network connectivity is also handled differently in the sandbox. In particular, the aforementioned restrictions on private and local addresses are ignored if the target network interface belongs to the app. Other VNet adapters on the same machine cannot be accessed, and all other network limitations are still apply.Additionally, the sandbox automatically affinitizes connect and send operations destined for VNet addresses to the correct VNet interface, in order to improve ease-of-use of the VNet feature.

 

Removing those 3 lines from code, will make your code running fine and you shall be able to create table or any such SMO operation.

So above are few most happening scenarios which you need to take care.