Jump to content













Photo
- - - - -

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

  Posted by Pantelis Apostolidis , in Azure 14 March 2018 · 177 views

azure azurerm appservice hybridconnector

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

Posted Image

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)

Posted Image

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”

Posted Image

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”.

Posted Image

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

Posted Image

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.

Posted Image

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

Posted Image

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.

Posted Image

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

Posted Image

Sign in to your Azure account

Posted Image

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

https://www.e-aposto...a838431ab4c.png

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

https://www.e-aposto...a83898e0f74.png

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

https://www.e-aposto...a8393ba0032.png

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

https://www.e-aposto...a83e8b94c55.png

and also sqlcmd

https://www.e-aposto...a83fdd583e6.png