ODBC login timeout on Azure App Service with VNet Integration

Question

We are using a premium tier Azure App Service and we have a SQL server that is located in our on premise data centre which is ExpressRoute connected. We have validated firewall rules and connectivity.

Our App Services in most cases use VNet Integration for outbound connectivity to key resources including SQL. We have found that whilst TCP 1433 connectivity is OK, ODBC connectivity fails with the following error. This is testing from a debug console which aligns with the error in the app code and insights.

Exception calling "Open" with "0" argument(s): "ERROR [08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Timeout error [258]. ERROR [HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired ERROR [08001] [Microsoft][ODBC Driver 17 for SQL Server]Unable to complete login process due to delay in prelogin response" At line:1 char:1 + $conn.open() + ~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : OdbcException

This is the simple test we are performing through a debug console. This generates the error above.

$conn = New-Object System.Data.Odbc.OdbcConnection("DRIVER={ODBC Driver 17 for SQL Server};SERVER=FQDN of SQL Server (VNet Int);DATABASE=DB Name;UID=;PWD=")

$conn.open()

Historically, before my time, hybrid connections have been created to support connectivity. Substituting the FQDN with the short name as defined in the relay/hybrid connection then this connects without issue.

We have carried out basic TCP 1433 (tcpping) tests using both and no errors with either. It only seems to be ODBC over the VNet Integration connection.

As an additional test, we connected a VM to the same VNet as used by the VNet Integration. From that, we can successfully connect using ODBC, with the same ODBC driver (ODBC Driver 17 for SQL). FYI, we are using SQL auth, we have checked DNS and firewalls/NSGs/Routes and no issues and VNet Integration is set to Route All.

We're a bit baffled, hybrid connections provide a temporary solution but we would like to route this traffic privately over our ExpressRoute. Any suggestions would be gratefully appreciated.

Answer 1

Iain Jones, Firstly, apologies for the delayed response from over the weekend.

From the issue description and based on the error message you provided; it seems that the ODBC connection is timing out when connecting to your SQL server. One possible cause of this issue is hitting a limit while making new outbound connections. The limits you can hit include TCP connections and SNAT ports.

I understand that you have validated firewall rules and connectivity, and that TCP 1433 connectivity is OK. However, ODBC connectivity fails. You also mentioned that hybrid connections have been created to support connectivity, and substituting the FQDN with the short name as defined in the relay/hybrid connection then this connects without issue.

 

It is possible that the issue is related to the VNet Integration for outbound connectivity to key resources including SQL. You mentioned that you have checked DNS and firewalls/NSGs/Routes and no issues and VNet Integration is set to Route All. However, it only seems to be ODBC over the VNet Integration connection that is failing.

 

Just to isolate/one possible solution to avoid SNAT port limitations is to use connection pools. By pooling your connections, you could avoid opening new network connections for calls to the same address and port.

Another approach is to use service endpoints or private endpoints. If your destination is an Azure service that supports service endpoints, you could avoid SNAT port exhaustion issues by using regional VNet Integration and service endpoints or private endpoints. When you use regional VNet Integration and place service endpoints on the integration subnet, your app outbound traffic to those services will not have outbound SNAT port restrictions. Likewise, if you use regional VNet Integration and private endpoints, you will not have any outbound SNAT port issues to that destination.

Also, ff your destination is an external endpoint outside of Azure, using a NAT gateway gives you 64k outbound SNAT ports. It also gives you a dedicated outbound address that you don't share with anybody.

 

Reference: Troubleshooting intermittent outbound connection errors in Azure App Service

 

--You may always leverage App Service diagnostics from Azure Portal> Navigate to your App Service app in the Azure Portal. (screenshot below)

 In the left navigation, click on Diagnose and solve problems - Review and run – Network troubleshooter