Solved

Cannot connect to database.  Providor Named Pipes Providor: 40

Posted on 2006-06-10
12
2,275 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 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