How to add Azure SQL Database External Data Source to onpremise sql server

Althaus, Benedikt 21

Hi.

we have a Azure SQL Database running as DB for a Azure WebApp.
Now, we would like to connect a SQL Server onpremise via VPN/Expressroute with the Azure SQL Database.
This is to query from the WebApp against the Azure SQL Database but getting some data also from internals SQL server without having to change the webapp itself
(implementing second connection string and such).

We where not able to find a documentation on how to do so using vnet integration or private link.

Any idea on how to move forward here?

Migrating the onprem db to azure or syncing is not an option for now, as we are only allowed bto read a view from the onprem db and it is used elsewhere onprem also.

1 answer

Ryan Hill 28,371 Reputation points Microsoft Employee

2020-11-18T17:45:54.673+00:00
Hi @Althaus, Benedikt ,

When connecting your on-premises SQL database to your Azure Web App, you have the following in options:

Hybrid Connection - you can install an exe on one of your on-prem servers. This exe will establish a tunnel between your app service and your database. This by far is simplest and quickest way to get going

App Service VNET Integration - you can link a new VNET to your ExpressRoute and integrate your app service with that VNET. Upon doing so, your app service should use your on-prem DNS making it possible to use a traditional SQL connection string. What I'm not certain of is how you would use SSPI for security.

Regards,
Ryan
Please sign in to rate this answer.
Althaus, Benedikt 21 Reputation points

2020-11-19T19:48:25.57+00:00

Hi @Ryan Hill ,

thanks for your answer.

We are aware of the solutions on integration of Webapp into the vnet.
And installing anything on the onprem SQL server is not an option due to security and ops processes available.

Question is, how to make the connection from Azure SQL DB against the onprem SQL DB.

Regards,
benedikt

Ryan Hill 28,371 Reputation points Microsoft Employee

2020-11-20T04:31:00.627+00:00

Hi @Althaus, Benedikt ,

The hybrid connection agent doesn't have to be installed directly only the on-prem SQL Server. It can be installed on separate server that's within your network. You still have option 2 which involves using your existing ExpressRoute to configure a VNET assuming you're not affected by any of these limitations.

Regards,
Ryan

Ryan Hill 28,371 Reputation points Microsoft Employee

2020-11-20T04:31:34.913+00:00

With regards to your question on what will allow Azure SQL DB to access your on-prem SQL DB, you can look at something like SQL Data Sync, but that again involves installing an agent within your on-prem environment. Another option to avoid installing an agent would be ETL the data from your on-prem server to your Azure SQL hosted DB. If however, I'm misunderstanding

the connection from Azure SQL DB against the on-prem SQL DB

please feel free to elaborate.

Regards,
Ryan
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to add Azure SQL Database External Data Source to onpremise sql server

1 answer

Your answer