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.