I got this error recently and wanted to share with the world what took me a day to debug.

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)

This will look like a total duh moment but it makes sense when one considers the circumstances.

The first response (assuming you know the SQL instance is running) to above is to go to the Surface Area Configuration manager for SQL 2005. Once there, make sure that your instance is set up to work with TCP and Named Pipes.

The next course of action is to check the firewall to see if it is blocking the server or the ports you need to access the system remotely.

If none of that is an issue, you need to consider another option. Look in your services. I am going to assume that the sql server instance is started and you have at least tried to connect to it from you local SSMS.

However, what about the SQL Server Browser service? If you are like me, you want to keep as little from starting at start-up as possible and shut down services when they are not used frequently. However, if when you start the server instance and forget to start the SQL Server Browser instance you will run into an error very similar to the one above. Make sure that your SQL Server Browser service is started and you should be good to go.

Your response: