Api-Web app (webjobs) connection to On-Premise SQL Connection

kvidhul-3447 20

Hi ,

I am having trouble establishing connection from azure webapp to on-premise sql server. we have virtual network gateway and site-site vpn to sql server.

allowed port 1433 in on-premise firewall and in azure firewall as well.

webapp have private endpoint with vnet and also vnet is integrated with web app.

i am still having connection error from azure web app (tested tcpping from kudu console).

Note: i can ping sql server from a VM in the same vnet.

Erland Sommarskog 111.1K Reputation points MVP

2024-10-02T22:24:29.0233333+00:00

And the text of the error message is?
kvidhul-3447 20 Reputation points

2024-10-02T23:03:07.8533333+00:00

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: TCP provider, error: -A connection attempt failed because the connected party did not properly respond after a period or time, or established connection failed because connected host failed to respond.)
KapilAnanth-MSFT 45,111 Reputation points Microsoft Employee

2024-10-03T07:23:58.47+00:00
@kvidhul-3447 ,

Welcome to the Microsoft Q&A Platform. Thank you for reaching out & I hope you are doing well.

From your verbatim,

You have a VNET Integrated App Service

This VNET has a S2S Connection to OnPrem

You would like to access an OnPrem SQL Server from this VNET Integrated App Service.

From a VM in the same VNET, you confirmed you able to ping the OnPrem SQL Server

There is Azure Firewall in the VNET

NOTE :

As the connection is outbound (from App Service), there is no use for Private EndPoint

It's configuration would not affect the connectivity.

As next steps,

Can you confirm if you are using the OnPrem SQL Server's IP or using FQDN ?

If you are using FQDN, is it resolvable within the Kudu Console?

What exactly is the error when you ICMP Ping and TCP Ping from the Kudu Console?

Are you sure there are no OnPrem Firewall blocking the traffic?

I see you have mentioned Azure Firewall, I assume that the VNET Integrated subnet has a UDR on it pointing traffic to the Azure Firewall - Correct me if I am wrong

Can you remove the UDR and send traffic directly to the OnPrem (Just for testing - make sure you also configure a route in GatewaySubnet for return traffic)

Do you see the traffic leaving App Service in the Azure Firewall? Refer : Enable diagnostic logging through the Azure portal

Cheers,

Kapil
kvidhul-3447 20 Reputation points

2024-10-03T10:22:14.2866667+00:00

I am using sql server ip (private ip) not fqdn,

from the kudu console - tcpping to the server is not connecting. Error: connection attempted failed: an attempt was made to access a socket in a way forbidden by its access permissions <server ip:1433>

Onprem firewall allows traffic from the gateway vnet range.

I have removed udr to test the connection and azure firewall is not blocking now. But still i see same error. No route from gateway for return traffic.

Also app service plan is B2 and it allows vnet integration. And vnet is having gateway so i have gatwybased based integration.
kvidhul-3447 20 Reputation points

2024-10-03T10:39:48.8433333+00:00

thank you
KapilAnanth-MSFT 45,111 Reputation points Microsoft Employee

2024-10-04T06:45:04.2966667+00:00
@kvidhul-3447 ,

This looks specific to the SQL Server rather than the VNET Integrated AppService or the network itself.

Can you please confirm if you are able to connect to the SQL Server from a VM in the same VNET using SSMS?

Not a TCP test, but actual login test.

Does it successfully login?

Because,

TCP timing out would indicate a network issue , the service being not reachable

However, you getting a valid response (error message) indicates the network is able to access the SQL server

From this Kudu console, are you able to access a different Server in the OnPrem network?

You can try with some other dummy server on RDP or SSH port over TCPPing

Assuming the firewall allows RDP/SSH

Cheers,

Kapil
kvidhul-3447 20 Reputation points

2024-10-04T10:07:35.9866667+00:00

Yes, i can connect from ssms in a VM and i can login to the server.

i am getting error from webapp kudu console. Also if i do a network trace i can see network connection failure to this specific server. I can connect to other instances in azure and also i can ping internet (googld.com) and SSH also works since i removed firewall and allowed direct traffic.
Erland Sommarskog 111.1K Reputation points MVP

2024-10-04T21:07:05.56+00:00

However, you getting a valid response (error message) indicates the network is able to access the SQL server

NO! This is incorrect. The error message that kvidhul is getting

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: TCP provider, error: -A connection attempt failed because the connected party did not properly respond after a period or time, or established connection failed because connected host failed to respond.)

says exactly that no SQL Server instance was found. This could be due to anything from typos in the connect string to firewalls blocking ports involved.

I note that kvidhul says that port 1433 is open. However, SQL Server may be listening to a different port. Particularly, this can be expected if it is a named instance.
KapilAnanth-MSFT 45,111 Reputation points Microsoft Employee

2024-10-07T05:39:31.47+00:00
Erland Sommarskog - thank you for your inputs, I agree.

@kvidhul-3447 ,

I see you mentioned network trace fails at this specific server (SQL)

As next steps,

Can you please collect packet captures at your OnPrem VPN/Firewall device

And also on the SQL Server?

Using this, we will get an overview of whether or not the packets from AppService reached the SQL Server or not

You should expect traffic from the entire IP Range of the delegated subnet

Cheers,

Kapil

Share via

Api-Web app (webjobs) connection to On-Premise SQL Connection

Your answer