Tuesday, October 12, 2010

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

Reference: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/c488cf76-2515-440f-b3f8-9cfad689c5b6

The error is reported by client library. While your server is listeing on remote TCP, client will still try TCP and NP connection in order. So the error client behavior is expected. From what you have described, I believe that even though you enabled the remote TCP connection on the XPSP2 machine, you didn't make the TCP listening port an exception of XPSP2 personal firewall. You should follow steps below to resolve this issue.



1. check the SQL Server Errorlog to make sure SQL Server is now listening on TCP/IP and confirm which port it is listening on. Usually 1433. In the Errorlog, you will see several lines that discuss what SQL Server is listening on. Below is an example:

2006-01-04 01:41:07.65 server SQL server listening on 10.254.1.150: 1433. <--Shows the IP Address and the port.

2006-01-04 01:41:07.65 server SQL server listening on 127.0.0.1: 1433. <--Shows another IP Address and the port.

2006-01-04 01:41:07.69 server SQL server listening on TCP, Shared Memory, Named Pipes.

2006-01-04 01:41:07.69 server SQL Server is ready for client connections

2, Make sure on Windows XP that the firewall is not blocking that port.

3, go to your client machine and run the client network configuration tool (cliconfg.exe) Make sure TCP/IP is enabled, click properties and make sure the port number is the same one as SQL Server is listening on. Here you can enable NP or disable client NP as well.

Once both the client and the server are using TCP/IP with the same port number and the firewall on server machines is not blocked, you should be able to connect.

No comments:

Related Posts Plugin for WordPress, Blogger...