Azure App Service, get data from on-premises databases securely

Azure App Service, get data from on-premises databases securely

There are many scenarios where we want to have the Web Application on the Cloud but on the other hand, due to various limitations, the database stays on-premises. Azure has a service, called Azure Hybrid Connections, that allows the Web App to connect to on-premises databases, using internal IP address or the database server host name, without a complex VPN setup.

The Connection diagram

hybrid connections

I have tested the connection with Microsoft SQL, PostgreSQL, MySQL, mongodb and Oracle.  The databse requirements is to have a static port. So the first step in case of a Microsoft SQL instance is to assign a static port. In my test environment I have a Microsoft SQL 2016 and I assigned the default port 1433, using the Sql Server Configuration Manager / SQL Server Network Configuration / Protocols for INSTANCENAME (MSSQLSERVER)

hybrid connections

All paid service plans supports hybrid connections. The limits are on how many hybrid connections can be used per plan, as the below table shows.

Pricing plan Number of Hybrid Connections usable in the plan
Basic 5
Standard 25
Premium 200
Isolated 200

To start creating the Hybrid Connections, go to the App Service / Networking / Hybrid Connections and press the “Configure your hybrid connection endpoints”

hybrid connections

At the Hybrid connections blade there are two steps, the first is to “Add hybrid connection” and the second is to “Download the connection manager”.

hybrid connections

First click the “Add hybrid connection” and then press “Create new hybrid connection”

hybrid connections

The “Create new hybrid connection” blade will open. Add a Hybrid connection name, this must be at least 6 characters and it is the display name of the connection. At the Endpoint host add the hostname of the database server and at the Endpoint port, the port of the database. At my case I added 1433, as this is the port I assign to my SQL instance before.

Finally you will need to specify a name for a Servicebus namespace. As you realize, the hybrid connection uses Azure Servicebus for the communication, and press OK.

hybrid connections

Once the connection is created it will be shown at the portal as “Not connected”

hybrid connections

Now we need to download and install the hybrid connection manager by clicking the “Download connection manager”. For this test I will install the hybrid connection manager at the same server as the SQL database, but for a production environment it is recommended to install the hybrid connection manager to a different server that will have access to the database servers only to the required ports. For the best security install it to a DMZ server and open only the required ports to the database servers.

Run the downloaded msi and just click Install.

hybrid connections

Open the “Hybrid connection manager” UI and press “Add a new Hybrid Connection.

hybrid connections

Sign in to your Azure account

hybrid connections

Once logged in, choose your Subscription and the hybrid connection configured previously will appear. Select it and press Save.

hybrid connections

Now at the connection manager status it will show “Connnected”

hybrid connections

The same at the Azure Portal and your Hybrid connection is ready.

hybrid connections

Test, test, test and proof of concept. Open the Console, form the Wep App Blade, and tcpping the SQL server’s hostname atthe port 1433

hybrid connections

and also sqlcmd

hybrid connections

Share

Leave a Reply