CODEWITHSUNDEEP
sql-serverazureazure-web-app-serviceazure-virtual-networkazure-vpn
Matt Peterson
Matt Peterson

Reputation: 5769

Query of on-premises SQL Server from Azure web app (over Azure S2S VPN) fails

Our infrastructure team has worked to configure a site-to-site Azure VPN connection between our Azure subscription and our on-prem firewall, essentially following these steps. To test this, we've created a simple Azure web app that makes a query against a SQL Server that is located on-prem behind the firewall.

This web app works locally with no issues. Furthermore, the same code and connection string, when compiled as a console application and run on an Azure virtual machine, works correctly as well. But when deployed to the web app in Azure, the connection to the SQL Server fails:

[Win32Exception (0x80004005): The wait operation timed out]

[SqlException (0x80131904): 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: 0 - The wait operation timed out.)]

Both the Azure VM and web app are configured to point to the Azure VNet. It seems like something is preventing the web app from communicating with the SQL Server on its default port (1433). If I open up the web app's debug console and do a tcpping at the SQL Server with the default port (80), it returns successfully. But a tcpping to port 1433 times out.

It doesn't appear that the Azure Network Security Group is blocking that port:

enter image description here

The only solutions I've found that are relevant to our specific setup basically boil down to "use Azure Hybrid Connections instead", which would not be our first choice.

Upvotes: 1

Views: 557

Answers (1)

Matt Peterson
Matt Peterson

Reputation: 5769

After working with Microsoft's support, the following changes were made and the VNet integration is working now. I apologize for the lack of detail on some of these, but our infrastructure team did most of the troubleshooting. Hopefully, some of these items will help point someone else the direction of a solution for their setup:

  • Initially, the connection was being made through public internet and not the VNET Integration and VPN. We identified that the VNET integration was failing due to the tunnel type being used. Azure App Service has a requirement that the tunnel type is SSTP. Once we changed it and synced the network, we were able to tcpping the SQL Server through its private IP.
  • We noticed it had to allow on the on-premises Network the point-to-site address pool. As a workaround, we decided to use the New VNET Integration (Preview). We created an empty subnet and were able to use this new feature.
  • We noticed then the App Service was not using the custom DNS. To resolve this, we added the DNS on the Virtual Network and also in the App Settings ("WEBSITE_DNS_SERVER") for the web app.

Upvotes: 2

Related Questions