Connection timeout issue with .NET Framework 4.6.1 - TransparentNetworkIPResolution


 

Understanding of the issue:

Client application using .NET Framework 4.6.1 fails to connect to SQL Server and the error we get is something like following.

Error:

'Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=11977; handshake=5138;’

Connection string example:

Server=myServerName;Database=myDataBase;Trusted_Connection=True

One more behavior to observer is, the connection works well with lower versions of .NET Framework.

 

What is the issue?

In short, the issue is because of a new property introduced in .NET Framework 4.6.1. A parameter in the connection string called ‘TransparentNetworkIPResolution’. This parameter is by default is set to true.

A little background on why we introduced this parameter,

There is a known design limitation with the way the SQL Server connections work on a SQL Server Availability Groups/ AlwaysOn inside a MultiSubnet Environment. Whenever the Availability Group Listener resolves to 2 different IP addresses of different Subnets based on the DNS lookup resolution the connection will go to the first IP for first attempt. If that is not the primary, then the connection will timeout.

By default, the behavior of the SQL client libraries is to try all IP addresses returned by the DNS lookup – one after another (serially) until the all of the IP addresses have been exhausted and either a connection is made, or a connection timeout threshold has been reached. This can become an issue when the first IP address returned is not the primary. Connection may timeout before it can attempt to connect to the next IP address.

To overcome this behavior, we had introduced a connection property called MultiSubnetFailover which we had to manually set it to true in such scenarios.

In .NET Framework 4.6.1, a new parameter “TransparentNetworkIPResolution” is introduced which would eliminate the need of this MultiSubnetFailover property in connection string. With this property being set, an initial connection attempt to the first-returned IP address is still made, but that attempt is timed-out after only 500ms, and then connection attempts to all the IP addresses are attempted in parallel. By default, this property is set to true.

In this scenario, where the connection is failing with .NET Framework 4.6.1, the DNS lookup is taking longer than 500ms. As this property is by default set in the connection string (Doesn’t check if this is Always On or not) it was timing out at 500ms. This made the connection timeout irrespective of it is Always On or not.

Tracert result will be something like following:

C:\Users\testuser>tracert test
Tracing route to test.xyz.com [158.1.2.14]
over a maximum of 30 hops:
1    <1 ms    <1 ms    <1 ms  158.1.2.3
2     1 ms    <1 ms    <1 ms  test1.xyz.com [158.1.2.4]
3   555 ms   552 ms   576 ms  test2.xyz.com [158.1.2.5]
4   604 ms   595 ms   559 ms  test3.xyz.com [158.1.2.6]
5   560 ms   559 ms   554 ms  158.1.2.7
6   553 ms   553 ms   559 ms  158.1.2.8
7   765 ms   783 ms   780 ms  158.1.2.9
8   776 ms   813 ms   842 ms  158.1.2.10
9   775 ms   854 ms   783 ms  158.1.2.11
10   832 ms   777 ms   772 ms  158.1.2.12
11   791 ms   819 ms   801 ms  158.1.2.13
12   773 ms   807 ms   771 ms  test.xyz.com [158.1.2.14]

One of the sample network communication for this scenario:

5

 

Resolution:

This issue can be resolved by modifying the connection string to set the parameter ‘TransparentNetworkIPResolution’ to false.

Example:

Server=myServerName;Database=myDataBase;Trusted_Connection=True; TransparentNetworkIPResolution = False

When we set it to false in the connection string it will not timeout at 500ms and the connection succeed.

Specifications of this property:

  • TransparentNetworkIPResolution is enabled by default
  • If we have MultiSubnetFailover parameter already set in the connection string, then “TransparentNetworkIPResolution” will be ignored.
  • If the database is mirrored, then the parameter is ignored

Please reach out to the CSS team in case you are still experiencing this issue after the above changes.

 

Author:         Chaitra Hegde – SQL Server BI-ONE Developer team, Microsoft

Reviewer:    Krishnakumar Rukmangathan, Technical Advisor, SQL Server BI-ONE Developer team, Microsoft

 

 

 


Comments (3)

  1. Ezzeldin says:

    this problem also happens for .net framework 4.5.

  2. AMOL DESAI says:

    I have to say, I am totally puzzled why this article does not appear at the top of Google searches and how does it not have a single comment on it yet. This exact issue caused a ton of problems at all ALL our customer sites who recently upgraded to .Net 4.6.1. It took us 2 weeks to finally come across this article and issue a patch which successfully fixed the problem. I am shocked nobody else is running into this issue!

  3. James Seddon says:

    This fix resolved an issue for a remote worker (using Cisco AnyConnect VPN Client 4.3.01095) attempting to connect to a SQL database using SSMS 2016 and domain credentials in a different domain to his Windows user account. After adding the correct domain credentials to Credential manager we received the error:

    "Connection Timeout Expired: The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to conenct to this server was - [Pre-Login] initialisation = 18781; handshake = 14836; (Microsoft SQL Server)"

    Applying a connection string of "TransparentNetworkIPResolution = False" allowed the user to connect without issue. It is worth noting that when using SQL authentication or a remote machine on the same domain as the SQL server this issue does not occur.

    Hope this helps somebody.

    Additional Information:

    Operating System: Windows 7 Professional Service Pack 1
    SSMS Version: SQL Server Management Studio 2016
    SQL Server Version: SQL Server 2016
    Connection Details: Remote working connecting via VPN using Cisco Anyconnect.

Skip to main content