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:
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
- 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
- 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?