Azure App Service with On-premises Connection

Imagine you get a new project with the ability to use whatever cloud services you want. You jump at the change and dream up an amazing architecture using Azure. Next, you present your plan to the rest of your team and discover a new requirement of having to use an existing on-premises database.

Does this mean your grand plans are shot? Thankfully not, as Azure has multiple solutions that allow you to connect to your existing on-premises resources to enable hybrid cloud strategies.

In this post, we are going to use one of the options, Hybrid Connections, to connect from a web site hosted in an App Service to an on-premises database.

Sample Application

The base sample application we will be using for this post is a new Razor Pages App targeting .NET Core 3. We will walk through actually connecting to the database later in this post. To get started you need the new app created and running in an Azure App Service.

I have multiple walkthroughs of creating a new application and publishing them to Azure so I’m not going to rehash that here. If you need help getting your app created and published to Azure you can check out my Deploying an ASP.NET Core Application to Microsoft Azure post and instead of using the Razor template use the Web App template like the following.

dotnet new webapp

Also, note that Hybrid Connections aren’t available on the free or shared hosting plans so when you are setting up your publish profile avoid those options.

Add a Hybrid Connection to an App Service

From the Azure Portal open the App Serice you created above and under the Settings section of the menu click Networking.

In the networking details, we want to click Configure your hybrid connection endpoints.

I’m going to point out again that Hybrid Connections aren’t available at free and shared scales levels. If your App Service is on a free or shared scale use the Scale up menu option to switch to a scale level that will support Hybrid Connections.

From the Hybrid connections detail page click Download connection manager. When done this will need to be installed on the machine that is running the on-premises database you want to connect to.

Next, click Refresh and then click Add hybrid connection.

Now on the Add hybrid connection page click Create new hybrid connection.

In order to create a new hybrid connection, Azure will require some information. The key parts here are the Endpoint Host which is the name of the machine that is hosting the database you wish to communicate with and the Endpoint Port which will need to be the port that your database is configured to communicate over.

Hybrid Connection Manager on the host machine

Now that the Azure side is configured we need to use the Hybrid Connection Manager application that we installed on the target machine above to allow talk to our App Service.

After opening the Hybrid Connection Manager on the target machine click Add a new Hybrid Connection.

Now Select the Subscription the App Service is a part of. After the list of available connections, loads select the one created above and finally click Save.

After making the above changes my hybrid connection continued to show offline in Azure. After some searching, I found a blog post that suggested restating the Azure Hybrid Connection Manager Service which cleared the problem up for me.

Sample Application Changes to Connect to On-Premises Database

This is a very raw test, but it gets the point across. First, add a reference to the System.Data.SqlClient NuGet package. Next in the Index.cshtml.cs delete the OnGet function and replace it with the following.

public async Task OnGetAsync()
{
    Tables.Clear();
    var connectionString = "data source=Server;initial catalog=master; User Id=User;Password=Password"";
    await using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();
    await using var command = 
                    new SqlCommand("SELECT name FROM sys.tables", connection);
    await using var reader = command.ExecuteReader();

    while (await reader.ReadAsync())
    {
        Tables.Add(reader["name"].ToString());
    }
}

The above connects to the master database on the specified server and pulls a list of table. Note that you will need to modify the connection string to something valid for your system. It is also important to know that you can’t use integrated security with this setup so you will need to specify a user and password that exists on your SQL Server. Add the following property in the same file.

public List<string> Tables { get; set; } = new List<string>();

Add the following to the bottom of the Index.cshtml which will output the table list we pulled above to the page.

@foreach (var table in Model.Tables)
{
    @table <br/>
}

After the changes are done republish the application to your Azure App Service. After the publish is done your site should show with a list of tables that exist in the master database of your SQL Server.

Wrapping Up

Hybrid connections is a great way to take a workload to the cloud without having to actually move all your data. It is also one of those features of Azure that I had no idea that existed before a week ago.

If you need a lot of hybrid connections look closely at the pricing as the number you can use is tied to what App Service scale you are using. The number of available starts at 5 and can go up to 200 with the more expensive App Service scales.

---

Also published on Medium.