Accessing on Premise SQL Server Database From Azure Web App Using Azure Hybrid Connection

Problem statement

Let's assume there is a company that wants to host their application on Azure but it cannot migrate to a SQL Server database to authenticate users. They are using SQL Server 2012 to store user information and their web application is an ASP.NET MVC application.

Solution

There are two ways to access on-premise resources:

1. Using Service Bus Relay.
2. Using Azure Hybrid Connection (can only be used for Web Apps and Mobile Apps).

This article shows how to use Azure Hybrid Connection to do Hybrid connectivity. The following is a step-by-step approach to accomplish on-premise connectivity for our hypothetical problem statement.

Prerequisites

• SQL Server 2012/2008 configured with SQL Server Authentication
• Visual Studio 2013 with Update 4
• Azure SDK 2.6
• Microsoft Azure Subscription

Summary of procedure

1. Create a sample database using a SQL Server database.
2. Create a web application using ASP.NET MVC application.
3. Publish the web application to an Azure Web App.
4. Create an Azure Hybrid Connection using Azure Preview Portal. (New Portal).
5. Complete the connection by installing a Listener Setup in an on-premise server (where SQL Server is installed).
6. Access an on-premise database from the web app.

Create a sample database using SQL Server database.

1. Open SQL Server Management Studio and login using SQL Server Authentication.
   
2. Create a new database and give it a name, let's say HybridUserStore, as in the following screenshots:
   
   
   
   
   
   
3. Note down the connection string. We will use the connection string in our web application.
   
   
4. Be sure TCP port is enabled on your SQL Server database. To check follow procedure described here.

Create a web application using ASP.NET MVC application.

Now we will create a web application that will use SQL Server as authentication store.

1. Open Visual Studio 2013 as an Administrator and create a new ASP.NET Web Application. Give it a name. For for example HybridWebApp. Snapshot below:
   
   
   
   
2. Select template as MVC, select Authentication as Individual User Accounts, and uncheck Host in the cloud option. Screenshot below:
   
   
   
   
3. Once the application is created, build the application and run it. The default template already has a Sign In and Register functionality created. Test register and Sign In functionality:
   
   
   
   
   
   
4. Once you have verified that the default template is working, now change the connection string in web.config to the connection string of the database created previously. Screenshot below:
   
   
   
   
5. Build and run the application again and verify that the Register and Sign In is still working (now using our HybridUserStore).
   
   Until now we have created an application that uses SQL Server to authenticate users, but both of the applications are running on the same computer so they are not issues until now. Now we will publish our web application to Azure and see that now the Register and Sign In does not work since it is not able to find the database.

Publish the web application to Azure Web App

1. Login to Azure Preview Portal.
   
   
2. Create a placeholder for the web application by clicking New -> Web + Mobile -> Web App and give it a name, for example HybridWebAppDemo as in the following screenshot:
   
   
   
   
   
   
3. When the web app is created, open the Visual Studio project, right-click and say publish. Choose Microsoft Azure Web Apps and select the web app that we just created from the Azure Portal as in the following screenshot:
   
   
   
   
   
   
   
   
   
   
4. When the application is published, run it and try to register a user. You will not be able to register a user since the database is not reachable. You will receive an error as in the following:
   
   
   
   
5. Now in the next steps we will create a Hybrid connection to the on-premise database so that we are able to access the on-premise database and register user in user store successfully.

Create Azure Hybrid Connection using Azure Preview Portal

1. Open Azure Preview Portal.
   
2. Click Browse All -> Web Apps -> HybridWebAppDemo (the app that we created) as in the following screenshot:
   
   
   
   
   
   
   
   
3. Scroll down in the blade of the web app and click on Hybrid Connection, in Hybrid Connection Blade click Add as in the following screenshot:
   
   
   
   
   
   
4. In the Add a hybrid connection blade, click New Hybrid Connection. In Create hybrid connection blade:
   
   
   1. In Name give the name of your hybrid connection. For example MyHybridConn.
      
   2. In Hostname give the hostname of the server on which the database is created. For example DeepakALM.
      
   3. In Port give the port on which the database if running. For example 1433 (Note, 1433 is the default port for the SQL Server default instance).
      
   4. Click on Biztalk service
      
      Screenshots below:
      
      
      
      
5. In the BizTalk service Blade, select New BizTalk service and give it a name. For example bizservforhybriddemo and click OK as in the following screenshot:
   
   
   
   
6. It will take some time to create this BizTalk Service and complete the operation. When it is created, you will see the status as in the following:
   
   

Complete the connection by installing Listener Setup

1. In the Hybrid connections Blade, click the Hybrid connection that you created. Click Listener Setup that will open up the Hybrid connection properties blade. Note the primary on-premises gateway connection string and click Install and Configure now as in the following screenshot:
   
   
   
   
2. A security prompt will come. Select Continue/ Keep and once the Hybrid Connection Manager is downloaded, launch it and click Run as in the following screenshot:
   
   
   
   
3. A security prompt be shown again, select Run. This will take some time to configure the connection manager. Once ready enter the copied connection string from Step 1 in the connection string and click OK as in the following screenshot:
   
   
   
   
4. The connection should be configured successfully and you should see a completion message as in the following:
   
   
   
   
5. Also check the connection status in Azure Portal. The Status should be connected as in the following screenshot:
   
   

Access on-premise database from web app

Now that the connection to our on-premise database is successfully established, we will test to register a new user in our web app. You should be able to Register a user and sign-in successfully. If you encounter any issues then double-check if you have enabled TCP protocol for your database. Use Step 4 of "Create sample database using SQL Server database".



Summary

In the preceding article I explained how to use an Azure Hybrid Connection to access on-premise resources like a SQL Server 2012 database from Web Apps and/or Mobile Apps hosted on Azure. In my next article I will explain another approach for using a Service Bus Relay to accomplish connectivity between on-premise resources and applications hosted in Azure.

Thanks for reading the article. Please provide your valuable feedback.