Privacy for My Azure SQL Database

Running a web app and a database in the cloud.

Tagged with: azure, infrastructure

Published on and last updated on

I’m a bit of an Microsoft Azure novice. Recent tasks lead to me learning some of the possibilities offered by Microsofts cloud computing service. Diving into this world feels like exploring a deep dungeon filled with great treasures but also with great dangers. One who ventures into this world has to learn new terminologies, access long-forgotten networking knowledge, and have a keen mind to avoid paying for things that aren’t required.

First, let me tell you what I want to run in the cloud. I have an ASP.NET web application that uses a Microsoft SQL database as data storage. The classic approach to this setup would be to either run the application and the database server on one machine or to separate the application and the database by using two machines. It doesn’t matter if the machines are physical or virtual as long as they are running Microsoft Windows. As intriguing as it seems to be running and administrating two virtual machines in Azure it didn’t seem like this is the cloud way to go.

After some research, I found the two services I wanted to use. My application should run inside of an Azure App Service which is more or less a container that only runs my application and makes it accessible to the web. To avoid having to manage a Microsoft SQL Server I opted in to use an Azure SQL Database. This database is mostly compatible with Microsoft SQL Server and completely managed.

The initial setup in which I tried to just make it work was fairly simple. I created an app service and a SQL database via the Azure web interface. After that, I published my application to the app service with the help of Visual Studio. Configuring the app service to use the Azure SQL database worked by configuring a connection string in the settings of the service. I opened the web app in the browser and everything worked fine.

Now that everything worked I wanted to secure the setup. In the current state, the database could be accessed from everywhere by everyone who has the credentials. But I only want my application to be able to access the database. In a classical scenario, I would put the database and the application together in a private network so they could communicate without using the public internet. As expected Azure provides a service called Virtual Network which provides exactly what I need. It allows the creation of private networks that can be used with other Azure services.

So I created a Virtual Network in the web interface. Navigated to the database server firewall settings disabled all public access and added the newly created network. Then I navigated to the App Service and added the virtual network in the networking tab. I opened the application in the browser and was greeted with an error. The application could not reach the database. After some cursing and applying the old “Have you tried turning it off and on again?” fix nothing changed.

Beaten down I did the one thing only a desperate person does: I read the documentation. There is a page about integrating app services with virtual networks which mentions a configuration key called WEBSITE_VNET_ROUTE_ALL. I skimped over it a couple of times but then I finally understood my problem. Azure SQL Databases get a hostname that is resolved to a public IP. An app service with a virtual network by default only sends RFC1918 traffic through the virtual network. Meaning that it only sends traffic that is intended for an IP in one of the private IP ranges. All other traffic is routed over the public internet. This means that requests to the database came from a public IP and not from within the virtual network. The firewall didn’t allow those requests and the access failed. After I enabled the WEBSITE_VNET_ROUTE_ALL configuration key for the app service by setting it to 1 I could access the database again. I also tried to access the database from my local machine which didn’t work. I finally reached my goal.

Cloud services like Microsoft Azure make it very easy to get an application up and running fast. But to keep your programs and data safe it is important to read the documentation carefully and to make yourself comfortable with the platform by trying things out.