Connection timeout issue with .NET Framework 4.6.1 - TransparentNetworkIPResolution

  • Articolo

 

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.

 

Further Update: Please refer the following documentation that talks about the connection behavior with this parameter.

Using Transparent Network IP Resolution: **https://docs.microsoft.com/en-us/sql/connect/odbc/using-transparent-network-ip-resolution** 

 

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

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

Comments

  • Anonymous
    August 30, 2016
    this problem also happens for .net framework 4.5.
  • Anonymous
    September 04, 2016
    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!
  • Anonymous
    April 13, 2017
    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 1SSMS Version: SQL Server Management Studio 2016SQL Server Version: SQL Server 2016Connection Details: Remote working connecting via VPN using Cisco Anyconnect.
  • Anonymous
    September 18, 2017
    The comment has been removed
    • Anonymous
      October 29, 2017
      The comment has been removed
  • Anonymous
    October 20, 2017
    Is this issue resolved in .NET Framework 4.7?
    • Anonymous
      November 06, 2017
      This Property still exists in .NET Framework 4.7. Hence even with Framework 4.7 you will have to disable this property if the lookup takes more than 500ms
  • Anonymous
    October 26, 2017
    I have this issue after applying 4.6.1 - Can't go back. Is there any news other than modifying the connection string. I have no access to connection string with some of our apps.This has been a pain.
  • Anonymous
    February 22, 2019
    It appears I have hit this issue. However, I see this error as the cause of failure in Transaction Log Shipping jobs. How can I disable TransparentNetworkIPResolution in this scenario?