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
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)
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|
To start creating the Hybrid Connections, go to the App Service / Networking / Hybrid Connections and press the “Configure your hybrid connection endpoints”
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”.
First click the “Add hybrid connection” and then press “Create new hybrid connection”
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.
Once the connection is created it will be shown at the portal as “Not connected”
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.
Open the “Hybrid connection manager” UI and press “Add a new Hybrid Connection.
Sign in to your Azure account
Once logged in, choose your Subscription and the hybrid connection configured previously will appear. Select it and press Save.
Now at the connection manager status it will show “Connnected”
The same at the Azure Portal and your Hybrid connection is ready.
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
and also sqlcmd