Monday, October 01, 2007

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.

14 comments:

Noman said...

I am also getting the same error even though I have SQL server 2000 installed.
Were you able to find any solution?

Noman said...

I have sql server named instance running. and secondly have an alias for the server in domain.

Varun said...

Sometimes it also happens that the SQL Server is not running in the Services. When you encounter this error be sure to check in services.msc if the SQL Server is running or not.

Tom said...

I still got errors until I remembered SQL Server 2005 got installed with a Named Instance instead of the Default Instance, in other words, it had a longer name than just the SQL box's FQDN - the name in Connection String needed to be hostname\named instance. After enabling TCP/IP and Named Pipes in the Surface Area Config and changing the name, was good to go.

Raymundo Castillo said...

Try adding in your connection string the property "Port" ;) it works for me.

victor said...

I had this same problem too, and spent forever trying to figure it out. Turns out, I had to run the Client Network Utility and then create and Alias. But nowhere did it tell me I had to stop and then start the SQL server. Found out the hard way after a couple of hours... :-)

Jhordy said...

Hey!, Thanks, i resolve my SQL TCP/IP Connexion problem configurig my SQL Client, running the cliconfg program as you said. Im so happy :D... Have a nice day, and Thank You again!!

Jord said...

I got rs working on a laptop with SQL 2005 but got the named pipes error after every reboot. There appears to be a bug in SQL 2005 that causes the rs service to lose its logon info after a reboot. After much hardship I discovered that going to run>services.msc, right-clicking on 'properties', going to the 'logon' tab and re-entering the password info solved the problem (and also restarting the MSSQLSERVER service). I still have to do it after every reboot, but at least it works

SBL Software Solutions said...

This problem may occur when SQL Server 2005 is not configured to accept remote connections.Hope it may be solved after the configuration.

Regards
SBL Software Development Solutions
http://www.sblsoftware.com/

Meenu said...

Hello bodies
I have installed sql server2005 . When i am going to connect withe MSSMS it is throwing this error.I have try out all setting either it will relate with sql server or firewall . So please help me
"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 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)"


Thank you
JAved

Shijo Baby said...

Hi

I got the reasons for the following error. This error is comming because you have to enable remote connection for sql server



Sqlcmd: Error: Microsoft SQL Native Client: 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.

In the following url theire is ways to enable remote connections in sql server

http://sqlservererror-info.blogspot.com/2009/07/error-has-occurred-while-establishing.html

Regards

Meenu said...

Thanks Buddy,
I got the solution earlier.

Thank you
Meenu

yagnesh.tatmiya said...

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

i m steel getting this error, i found this is because of proxy, can i do for this, i followed above all 3 process.........

milin said...

hello dear i m still getting same error after applying all ur solutions !! is there any other solution ?