Connection to remote SQL server breaks when upgrad

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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;