Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Connection to remote SQL server breaks when upgrading web server to .net framework 4.6.1

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.

like image 687
kjb4 Avatar asked Jan 05 '16 17:01

kjb4


People also ask

Can T Remote connect to SQL Server?

Right-click on your server name and click 'Properties'. Go to the Security page for Server Authentication, and select 'SQL Server and Windows Authentication' mode. Then, go to the Connections page and ensure that "Allow remote connections to this server" is checked, and click OK.

How do I fix the SQL Server Connection String?

Right-click on your connection and select "Properties". You will get the Properties window for your connection. Find the "Connection String" property and select the "connection string". So now your connection string is in your hands; you can use it anywhere you want.


1 Answers

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.

like image 111
kjb4 Avatar answered Sep 25 '22 17:09

kjb4