SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Lately I was working on a new ASP.net application when I had to deploy the application on one machine and the database on the other. Other machine only had SQL Server 2000 deployed. Though I had been receiving the errors:

"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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) "

"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)"

After trying many posts, google searches I was still unable to resolve the error. Most importantly I was not sure why I would get an error for SQL 2005 when I was using only SQL Server 2000.

The second error is encountered when you put Network Library property with your connectionstring. Apparently adding the network library is used to indicate that your connection will use TCP/IP and not Named Pipes.

Anyway, I found couple of interesting solutions during my search. Though they have not helped me (I guess must be something wrong with my configuration), these might help you.

Solution 1:

Go to, Start >> Programs >> Microsoft SQL Server 2005 >> Configuration
Tools >> SQL Server 2005 Surface Area Configuration >> Surface Area
Configuration for Services and connections.

Within this check whether "Local and remote connections" is choosen.
If not choose it :)

- Open the "SQL Server Configuration Manager" (under Configuration Tools)
- Expand the "SQL Server 2005 Network Configuration"
- Select the "Protocols for "
- Set the Named Pipes To Enabled

SOLUTION 2:

1. Click Start, click Run, type cliconfg, and then click OK.
2. In the SQL Server Client Network Utility dialog box, click the Alias tab, and then click Add.
3. In the Add Network Library Configuration dialog box, under Network libraries, click TCP/IP.
4. In the Server alias box, type the IP address of the computer or the
name of the computer that is running SQL Server, and then click OK.

SOLUTIONS 3:

Enable the TCP/IP protocol using the Surface Area Configuration Utility
Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility
Make sure the SQL Server browser is started. Note this step is optional. It is possible to set the SQL Server instance to use a fixed IP address - but this is non-standard for named instances
Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.
Note: In order to get things to work. You might need to completely reboot the server machine after making the changes. There have been reports that starting and stopping the SQL Server and Browser software is not enough.