SqlClient – Connection Delay/Timeouts

Recently we were faced with a problem where connections to SQL Server (using ADO.NET System.Data.SqlClient) from a web server were taking just too long or were timing out.

It turned out that although the SQL Server was listening on all 3 protocols (shared memory, TCP, named pipes), there was a firewall between the web and SQL server and the default TCP port 1433 was not open.

Looks like the SqlClient tries all the protocols - shared memory, TCP, named pipes - in that order till it finds a valid protocol. Since namedpipes was the only valid protocol, the connection was taking long/timing out, since all other protocols had to fail before it attempted to use namedpipes.

Here is a blog post which discusses this behaviour - http://blogs.msdn.com/b/adonet/archive/2010/04/18/sqlclient-default-protocol-order.aspx

On the web server, we created an alias for the server and configured the alias to use the named pipes protocol. We also disabled all the other protocols in the SQL Client Configuration. This seemingly solved the problem.

Here are a few more good reads on the issue:

• SqlClient Timeouts Revealed – this blog post describes various timeouts of SqlClient.
• How to troubleshoot connectivity issues in SQL Server 2000 and Troubleshoot Connectivity Issue in SQL Server 2005 - Part III discusses troubleshooting connectivity to SQL Server.