Azure Web Job SQL Connection String Server not found (On Prem SQL)?

Question

I have a web app service in Azure with web job attached. The web App is connected to my company network via VNET in Azure but the webjob wont connect to the on premise sql instance.

here is the format of connection string I am using: which works locally through visual studio.

    <add name="conn" connectionString="Server=192.0.0.0\SqlInstance;Initial Catalog=Database ;user id=xxx; password=xxx" providerName="System.Data.SqlClient" />

I am only trying to select some data at the moment to prove the concept but cant get it working.

I am getting this error.

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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I have check the sql instance and Allow Remote connections is enabled and firewall on the server is also disabled.

is my connection string wrong?

Thanks

Does your web application connect to the database successfully or do you only have a webjob in the WebApp? By "my company network" do you mean it is connecting to a VNET to a DB on a VM in Azure or connecting to a DB on premise? If on premise - how does your company's network connect to Azure? is it ExpressRoute or VPN site to site? — Jason Haley
Hi Jason - thanks for the reply - my web app inserts data to an Azure DB instance which works fine. The web job is in the web app and is then doing a sql call to the on premise sql server. The Vnet is a VPN site to site. — Hustler101
It sounds like your site-to-site doesn't know about the point-to-site addresses that the web app uses. Do you have the ability to add routes on premise? If that is the issue, towards the end of this article in the Set up VNET integration section is information on what needs done: azure.microsoft.com/en-us/blog/… — Jason Haley
I am not sure on the routes - I'm not really a network guy but I'll check with my colleague. May be worth noting that I have another app service in the same plan which is connected to the same vnet and that app service is able to to connect to an on prem web service in IIS ( a different server though to the one I am trying to connect to). I'll have a read through the article. Thanks for helping :) — Hustler101
The troubleshooting tools at the end of that article may help you too. — Jason Haley

Hustler101 Hustler101 · Accepted Answer · 2017-09-25T09:33:14

I have found the problem - turns out our SQL instances are not using the standard ports. once I found out the correct port using SQL Config manager and applied it to the connection string it started working.

connectionString="Server=192.0.0.0\SqlInstance,5000;

Thanks to @Jason Haley for pointing me towards Kudu which helped me find the issue.

Azure Web Job SQL Connection String Server not found (On Prem SQL)?

1 Answers