Solved

Cannot connect to database.  Providor Named Pipes Providor: 40

Posted on 2006-06-10
12
2,273 Views
Last Modified: 2009-05-06
I get this error when I try and log into my SQL Server 2005 database:
"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) (.Net SqlClient Data Provider)"

It was working fine two days ago...I'm pretty new to this any help would be greatly appreciated!  Thank you!
0
Comment
Question by:eunuch_provocateur
12 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 60 total points
ID: 16878050
1. Open the SQL Server Configuration Manager by clicking Start, All Programs, Microsoft SQL Server 2005, Configuration Tools, SQL Server Configuration Manager.
2. In the left pane of the SQL Server Configuration Manger, expand the node for SQL Server 2005 Network Configuration and select Protocols for SQL.
3. In the right pane, right-click Named Pipes and choose Enable.
4. Right-click TCIP/IP and choose Enable.
5. In the left pane, select SQL Server 2005 Services.
6. In the right pane, right-click SQL Server Browser and choose Properties.
7. Click the Services tab in the Properties dialog box.
8. On the Services page, set the Start Mode property to Automatic and click OK.
9. Right-click SQL Server Browser and choose Start.
10. Right click SQL Server and choose Restart.
0
 

Author Comment

by:eunuch_provocateur
ID: 16878122
After I do that I get the error:
"The request failed or the service did not respond in a timely fashion.  Consult the event log or other applicable error logs for details."

The thing is that I alredy had those set like you stated in your solution.  I'm wondering if I should just reinstall the SQL server software?  Will that create more problems or let me start from scratch again?  I won' lose much data as I'm just getting started with this...I just want to know the way to do it right.  

Thanks for you help!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16879611
eunuch_provocateur,
> "The request failed or the service did not respond in a timely fashion.  
> Consult the event log or other applicable error logs for details."
check whether any firewall is installed ? also check the remote service has started in the server ?
0
 

Author Comment

by:eunuch_provocateur
ID: 16880018
I have a firewall installed but I have it turned off when I try and connect to the database.  I also have exceptions for anything related to SQL Server.  

I'm not sure how to check if the remote service has started in the server.  

Will it be of any user to uninstall SQL Server 2005 and then try and reinstall it?
0
 

Author Comment

by:eunuch_provocateur
ID: 16881449
Ok, I reinstalled SQL Server and I can connect to the database fine.  

I'm trying to install the sample database 'pubs' but cannot get this to work.  I think the problem is that I'm using the command:  osql.exe -E -S <computername>\<instancename>  -i instpubs.sql

The problem is I'm not sure what to do with the instance name.  Where do I set up the instance...apparantly I can not use just the default.  Any help would be greatly appreciated.  Thank you!

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16881480
Ok that's great..
One way to solve this problem is  to create an alias for this, with server name as 'ServerName\InstanceName'

Then you can use OSQL -E -S uraliaseName 0i
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:eunuch_provocateur
ID: 16881488
How do I create an alias?  Sorry for alll the questions...I just don't want to get locked out again for pressing something I shouldn't.  Thanks!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16881496
0
 

Author Comment

by:eunuch_provocateur
ID: 16881545
It's not working...I'm now trying set up the surface area to allow remote connections.

Should it be this difficult?
0
 
LVL 5

Assisted Solution

by:rmacfadyen
rmacfadyen earned 40 total points
ID: 16881629
The instance name is defined during SQL's installation procedure. If you installed SQL 2005 Express using the defaults this will install with an instance name of SQLExpress (so your server "name" is "myserver\sqlexpress"). With regular SQL 2005 (standard edition etc) the installer creates an default instance without a name (so your server name is just "myserver").

Typically to discover what instances are installed you need to use SQL Studio (on the machine where SQL is installed). Its connection dialog will list all the instances in the dropdown.

Regards,

Rob
0
 
LVL 3

Assisted Solution

by:lokeshgm7
lokeshgm7 earned 40 total points
ID: 16892271
You are running into a bug which has been fixed in SP1. the work around is to do the following

If you explicitly specify to the TCP/IP protocol to connect to the instance of SQL Server 2005, you can successfully connect to the instance. For example, you can use the following command to connect the instance:

Osql –E -S tcp:<IPAddress>[\<InstanceName>],<Port>
Note You must make sure that you enable the TCP/IP protocol in SQL Server 2005.  
• Restart the SQL Server 2005 service.  
0
 

Author Comment

by:eunuch_provocateur
ID: 16895506
I got the solution from this website:   http://www.codinghorror.com/blog/archives/000434.html

Thank you all for your help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now