0

I'm trying to build a webservice that talks to a SQL database hosted on a server in our internal network. The service is hosted by Azure as a Web App. Is there a good way of doing this? Do I have to use Azure Sql databases, and if I do, is there a way to have the Azure database act as a proxy for our internal database?

There are already rules permitting connections to the ports on our database server, so I don't think that's the problem. I see a lot of questions regarding connecting to Azure hosted sql databases, but nothing about connecting Azure web apps to other kinds of databases.

The error occurs when I try to call a stored procedure (via generated entity framework code) and is as follows:

Error occurred: System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

Our database is configured to allow remote connections, so what I'm guessing the Web App is having difficulty connecting to our vpn.

Please let me know if you need any additional information.

Thanks,

Josh

Josh Bowdish
  • 91
  • 1
  • 15
  • Checkout https://learn.microsoft.com/en-us/azure/app-service/app-service-hybrid-connections . There can be other optimal solutions too but this is one of the Microsoft's recommended one. – shmit May 15 '19 at 20:18

3 Answers3

0

You can leverage Azure Hybrid Connections which is a feature of App service. Within App Service, Hybrid Connections can be used to access application resources in other networks. It provides access from your app to an application endpoint and uses Azure Relay service to connect to on-premise. Check out the below link for more details :

https://learn.microsoft.com/en-us/azure/app-service/app-service-hybrid-connections

Kathiravan
  • 81
  • 1
  • 3
0

First option is to look on azure app service hybrid connection but for you to do that you should have Windows server 2012 or above.

Azure App Service Hybrid

Azure App Service hybrid connection is good if you are pulling small amount of data.

If you are pulling large amount of data or your SQL server version is below server 2012 you have two options:

  1. Azure Site to Site VPN

  2. Azure SQL Data Sync

Azure SQL DB Sync is a feature that available on Azure SQL database. You can create a Azure SQL database on azure and sync your on-premise SQL database or SQL database table to Azure SQL database and you can connect your application to Azure SQL database instead of connecting to on-premise database server. This will increase your performance of your application.

Rusiru Tharaka
  • 31
  • 4
0

We ended up adding the application to an Azure Virtual network that allowed connections to our on-prem servers. The remaining difficulties were due the wrong port numbers being open.

What was very helpful in debugging this was the Kudu console in Azure, under Advanced tools -> console. There you can run commands from the machine hosting your application like ping, or the below:

sqlcmd -S tcp:servername,1433 -U Username -d databasename -P password -q "SELECT * FROM tablename"

Josh Bowdish
  • 91
  • 1
  • 15