SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,050 questions
Api-Web app (webjobs) connection to On-Premise SQL Connection
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 41%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EK3%3C/text%3E%3C/svg%3E)
kvidhul-3447
20
Reputation points
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.
{count} votes
-
Erland Sommarskog 112.8K Reputation points2024-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 47,206 Reputation points Microsoft Employee2024-10-03T07:23:58.47+00:00 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 47,206 Reputation points Microsoft Employee
2024-10-04T06:45:04.2966667+00:00 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
- Can you please confirm if you are able to connect to the SQL Server from a VM in the same VNET using SSMS?
-
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 112.8K Reputation points
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 47,206 Reputation points Microsoft Employee
2024-10-07T05:39:31.47+00:00 Erland Sommarskog - thank you for your inputs, I agree.
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
-
kvidhul-3447 20 Reputation points
2024-10-08T02:43:06.9+00:00 No, there is no packets available from webapp on on premise firewall side.
-
kvidhul-3447 20 Reputation points
2024-10-15T14:35:07.37+00:00 @KapilAnanth-MSFT any thing else that i have do to test further? I still have no resolution for this.
Sign in to comment
Sign in to answer