Connecting to On-Prem SQL from Azure Web App

Background:

When an enterprise transitions to Cloud, it may still need to leave some assets on-premises for technical or security reasons. Typically SQL DBs will be On-premises for most enterprises.

But this should not stop the enterprise from having their Web apps, APIs, services and mobile apps on cloud. The major hindrance in this scenario will be the feasibility for connecting the Cloud based services to On-Prem SQL for seamless transition.

Azure allows you to create layer on top of this On-prem assets while safely connecting to them back on your premises using Hybrid Connections.

Supported assets include MS SQL Server, MySQL or any resource that runs on static TCP Port.

Prerequisites:

1. Visual Studio 2013 or later
2. SQL Server 2008/ 2012 with SQL server authentication
3. Azure SDK
4. Microsoft Azure Subscription

Steps:

1. Create SQL Server DB and table. Cerate an SQL User to connect which will be used in the .NET application. Also create some sample data in the table.
2. Create a .NET web application which will read data from table create in Step 1. The connection string will look something like below.
3. Host the application on Local IIS and ensure it works and can connect to SQL.
4. Now host the application on Azure as web-app. You can refer the below link for steps to create Azure Web app.

   https://github.com/Microsoft/HealthClinic.biz/wiki/Create-and-deploy-an-ASP.NET-web-app-in-Azure-App-Service

5. You will notice that the application will throw error because it will not be able to connect to the On-prem SQL.
6. We will now create a Hybrid connection to the SQL DB.
7. Navigate to App Service which we created in Step 4 in Azure, and navigate to Networking. Click on Hybrid Connections > Configure your Hybrid Endpoints
8. Create New Hybrid Connection. Enter the details for Hybrid connection like below:

   Note: usually the TCP Port no for SQL is 1433. Please check for the SQL you are configuring.

9. Download the Hybrid connection manager and install on the SQL server or any server on the same network.
10. Open the installed Hybrid Connection Manager UI, and enter the connection string of the Hybrid connection we created in Azure.

    You can get the connection string of the Hybrid connection by clicking on it like below.

11. Enter the Connection String in Hybrid Connection Manager UI.
12. If everything is proper, you should see the status as Connected Like below in the tool as well as in Azure.

In Azure:

Other Notes:

1. If you are facing issues with connection, you can restart the Hybrid Connection service from Local services.

Please comment below in case of queries.