12

We're currently working on updating our asp.net web application (hosted on IIS 7.5) from .net framework v4.5 to v4.6.1. On small lower environments/local development in which SQL server runs on the same box as IIS, this update works fine and does not break anything. However, once we update our web servers in our test environments which host SQL server remotely from our web servers, our application can no longer establish a connection to the database. We receive this 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 SQL server is running with CLR version v4.0.30319. We use Entity Framework version 6.0.0.0 for data access, and the connection strings all use integrated security. Do we need to update the boxes hosting SQL server to .net 4.6.1 also? I don't see why that would be necessary for our app to establish a connection to the database, but I've been unable to find any guidance on MSDN about this.

EDIT:

After this breakage, we downgraded our web servers back to .net v4.5 and we were able to re-establish a connection to SQL server. re-upgrading to v4.6.1 caused the breakage yet again. Therefore, we are relatively sure that the upgrade is the issue, and not a change in application code and/or IIS settings.

kjb4
  • 141
  • 1
  • 6
  • check the event viewer on the SQL server box to see if there are any logs generated on the application or security tabs at the times of the errors. Check also if the port is open, or if there is an access control list that is blocking the connection. – user1666620 Jan 05 '16 at 17:13
  • also, what has changed on the web servers since they last worked? – user1666620 Jan 05 '16 at 17:20
  • No change in application code/IIS settings on the web servers. We have downgraded the web servers back to .net 4.5 and our connectivity has been re-established with SQL server. re-upgrading to 4.6.1 results in the same breakage. This also indicates that the port is open. I will check the event logs on the SQL server box though. – kjb4 Jan 05 '16 at 17:26
  • may as well upgrade the SQL boxes to .net 4.6.1 then. This article indicates that the way SQL connections were handled were changed for 4.6.1: https://visualstudiomagazine.com/articles/2015/12/11/net-framework-4-6-1-released.aspx Haven't looked for the exact changes though. – user1666620 Jan 05 '16 at 17:31
  • We had the same problem while we upgraded to .NET 4.6.1 in production. We didn't catch that problem in DEV, QA or Staging. To make matters worst, we have 3 SQL Servers (2008 R2) and only access to one of them was failing consistently. After rolling back to .NET 4.5.1, the issue went away. We are 100% sure that this problem is related to connection changes made in .NET 4.6.1 over 4.5.x. – Frank.Germain Apr 05 '16 at 13:22

4 Answers4

1

Update - so it looks like we've found (at the least the solution to) the problem. Turns out -- as the exception suggested -- that by increasing the connection timeout property on our connection string (default is 15 seconds, we set it to 60 seconds), we were able to make a connection to our database through our web app. However, opening this connection takes a prohibitively long time, so we started looking for solutions to make our connection open faster. We've discovered that we have Netbios over TCP/IP enabled on our database server, and that by opening up UDP ports (137, 138) on our network for Netbios access, we were able to open connections to the database way faster, timing at <1 second instead of >15 seconds. We're still not sure why the .net upgrade exposed this problem. By testing with a UDL file, we were able to establish that the network connectivity to our database performs about the same on our web servers on .net 4.5 as on our web servers on .net 4.6.1. So it seems as if our connections were opening so slowly that we were already very close to timing out, and some sort of extra logic/cruft in 4.6.1 put us over the edge. I'll update if we find more clarity on that.

kjb4
  • 141
  • 1
  • 6
  • Why do you assume it was an issue with 4.6.1 instead of some random network condition that caused the connection to cross the threshold? Looks like you used a NETBIOS name instead of an IP or FQDN, thus forcing ADO.NET to try multiple protocols until it found the one that worked. Why didn't you use TCP/IP and an IP or FQDN in the first place? – Panagiotis Kanavos Feb 01 '16 at 16:50
  • 4
    We use an IP, not a NETBIOS name. We assumed it was an issue with 4.6.1 because by rolling the .net framework upgrade back to 4.5, we could fix the issue. So we were/are fairly confident that the upgrade prompted the failure. However, like I mentioned above, it sounds like the upgrade simply exposed an existing bottleneck. – kjb4 Feb 02 '16 at 19:09
  • 1
    We're seeing the exact same behavior with 4.6.1. – etc Feb 24 '16 at 17:02
1

The following article describes a new default connection string setting for SQL Server connections in .net 4.6.1.

https://blogs.msdn.microsoft.com/dataaccesstechnologies/2016/05/07/connection-timeout-issue-with-net-framework-4-6-1-transparentnetworkipresolution/

This was to solve one problem in some environments, but also caused the issue you are experiencing in other environments.

Basically, you'll want to add the following your connection string:

TransparentNetworkIPResolution=False;
LucidObscurity
  • 309
  • 3
  • 4
0

you might want to check the machine.config and web.config files in the windows\micorsoft.net\framework64\v######\config folders. each version of .NET runs on different config files. Since the same code is used in both environments, it must be from the config inherited from here. I am guessing that the 4.6.1 is set to default the connection string to localhost and since the SQL in local and dev are on the same server it is not an issue. You will probably find that the config in the .NET 4.5 version has a connection string defined beyond localhost.

if 4.5 and 4.61 are using the same config files, then make sure you define a default connection string to be used by the entity framework in that web.config.

Dr. Aaron Dishno
  • 1,859
  • 1
  • 29
  • 24
0

First of all - this issue appears only when you're using Active Directory authentication.

I've managed dirty-fix: add your MSSQL server to your local (machine which can not connect to MSSQL server) hosts file (%windir%\system32\drivers\etc\hosts).

For example: 192.168.0.5 mssqlserver

It realy doesn't matter the name. It also works well if you have multiple SQL servers on one IP address (connecting through NAT).

This dirty-fix will also fix slow loading using SQL Management Studio issue.

Sergey 'dreik' Kolesnik
  • 19
  • 3
  • 1
    The hosts file has nothing to do with AD authentication. The timeout has nothing to do with authentication either. This "fix" is simply trying to short-circuit ADO.NET's logic when trying to determine the protocol to use. A *far* better option is to simply use an FQDN or IP. Or specify the protocol in the connection string – Panagiotis Kanavos Feb 01 '16 at 16:51
  • That's really strange, in my tests I was able to reproduce this issue only using AD auth with connection using IP,PORT combination. FQDN wasn't available option for me. – Sergey 'dreik' Kolesnik Feb 01 '16 at 16:54
  • Then you should check your domain setup. What you did was hide the NetBIOS name behind a host name. You didn't fix the problem that caused timeouts. Windows authentication *is* the default and probably most secure mechanism for SQL Server, used extensively for services, web sites, etc. If you can't use it, you have bigger issues – Panagiotis Kanavos Feb 01 '16 at 16:57
  • I've fixed it for myself and for all of my colleagues - connecting to SQL server through NAT which was broken after installing on it/dev pc's. – Sergey 'dreik' Kolesnik Feb 01 '16 at 16:59
  • The real "fix" would be to use a proper FQDN in the connection strings, like `myserver.mydomain.local`. – Panagiotis Kanavos Feb 01 '16 at 17:01
  • No, it will not if you're doing remote connection through NAT in case to do not publish 50ton of ports through internet. Just a simple example if you are ready to test: two separate VLANs with OWN AD Forests in each (no trusts). Domains with the same name, username's and password also identical. MSSQL is available only through port 1432 (other ports firewalled between VLANs). And - Yes this setup was broken once .NET 4.6 was installed on "clients" pcs. – Sergey 'dreik' Kolesnik Feb 01 '16 at 17:07