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

I have come across this error many a times, I thought why people to make struggle to find out the solution over google and then after reading all forums views and then getting into solution.You know sometime you spend much time than expected just to look into solution and then applying it which may work or may not. I found the way how to make someone happy by just providing a quick and relible solution which works for all.

“Since it a common scenario when you work with more than one machine where one is your application sever and another is your database server”. I was getting the below error messages …

"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 error encountered when you put Network Library property with your connectionstring. Adding the network library to indicate that your connection will use TCP/IP and not Named Pipes.


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

Look into the following solution either will surely work for you. The solutions basically depends upon the type of configuration used while installing SQL Server.

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:

· Click Start, click Run, type cliconfg, and then click OK.

· In the SQL Server Client Network Utility dialog box, click the Alias tab, and then click Add.

· In the Add Network Library Configuration dialog box, under Network libraries, click TCP/IP.

· 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.