Sql-server – Sporatic Connection issues connecting to SQL 2005 – Named Pipes vs TCP/IP issue

sql servertcpip

We have a production website that is experiencing periodic connection errors to our SQL 2005 server, several times a day during peak usage:

An error has occurred while
establishing a connection to the
server. When connecting to SQL Server
2005, this failure may be caused by
the fact that under the default
settings SQL Server does not allow
remote connections. (provider: Named
Pipes Provider, error: 40 – Could not
open a connection to SQL Server)

We're certainly investigating other avenues, but so far we haven't seen anything out of the ordinary on the SQL side. We're wondering whether this might be a Named Pipes issue, or whether we'd see the same thing if we forced the web server to use TCP/IP instead. So my questions are:

  • Anyone seen a problem like this? Most of the searching I do on this error is for people who can't talk to their SQL server at all because the surface area configuration is messed up. That's not our situation.
  • What's the difference between the two? Do they do name resolution differently? These servers are not domain members, they are isolated in their own DMZs, if that changes anything.
  • The internet team set up a SQL alias on the web server: "mySQLserverName – tcp/ip – xxx.xx.x.xx, 1433". Would this only be used for TCP/IP resolution, and not named pipes? Could this be part of the problem?
  • If I do want to force TCP/IP instead of named pipes, what's the recommended way to do it? This Microsoft KB says I can do it by modifying the connection string. This MSDN forum thread says I can modify the "preferred order" of the native client configuration protocols. I suppose I could also disable Named Pipes on the SQL server altogether, but that seems a bit drastic, and probably not something to try on a production box.

Best Answer

Typically we only use TCP/IP in our connection strings (with a non default port number) and have no scalability issues, for both local and wan based servers.

You can FORCE the use connection to use TCP/IP by using the server=dbservername,1433 in the connection string (i.e. specifying the port number).

We usually leave named pipes open as it allows you to see the "Green" / "Red" indicator on SQL Management Studio for the server status.

I would look at the general health of the server, especially network load (connections / collisions) and RAM usage (SQL Server PerfMon counters). Sometimes you can get problems with connection pooling / release on very busy servers (typically when there is 1,000's of connections per second).

Related Topic