Named Pipes Provider, error: 40 – Could not open a connection to SQL Server

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

This one was frustrating to resolve as there are a number of reasons this error could occur and lots of information out there about fixing the problem. My scenario was this; my IIS 7.5 web server was throwing this error when trying to connect to a SQL Server database on a different machine. I would get this error when calling the web application from a third machine. One of the frustrations was that if I made the same request from my browser on the actual web server I did not get any error, just the page I was expecting.

After searching the net, trying a number of the suggested solutions, triple checking my connectionString entry in the web.config I remembered that I hadn’t tried using the IP Address in the connectionString rather than the server name. Making this change solved my problem and my web application worked as expected from any machine. As it turns out, supplying the FQDN (Fully Qualified Domain Name) in the connectionString also worked. So now I had to try and figure out why the the web server was having trouble resolving to this particular database server?

Running the TRACRT command-line against my database server name gave me an unexpected response; the name of another server. So that got me thinking about whether the database server had been renamed recently – it was a VM that I was not responsible for maintaining. So I opened up SSMS and ran the command¬†SELECT @@SERVERNAME AS ‘Server Name’. This gave me the name different name altogether! If I put this third server name into the connectionString that also worked, without needing to use the FQDN.

Some further investigation later revealed that the VM was a copy of another VM which was then renamed twice but no one had followed the procedure for renaming the SQL Server instance. This was what was contributing to the problems with the server name being resolved correctly.