Cannot connect a remote sql server

[Joe]

Hello,

I'm having a strange problem here. I'm trying to connect to a remote SQL
Server database but I keep getting this error:
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 can connect using other machine just not the one I want to. The machine
I'm trying to connect with is a Windows 2003 Server running SQL Server 2005.

I sort of think it may be getting confused and thinks I'm trying to connect
locally.

I even tried to connect using the SQL Server Management Studio but I get the
same error.

I set Named Pipes to enabled in the SQL Server Config.

Any ideas?

Thanks,
Joe

 

[george wen]

I would suggest you to setup TCP/IP and try to connect using tcp/ip instead.

it is also easier to troubleshoot, you should probably test the connectivity
between these two machine first...

 

[Joe]

TCP/IP is setup already. I can also ping the server I'm trying to connect
to.

 

[Steven Cheng]

Hi Joe,

From your description, you're encountering some connectivity issue when try
connecting a SQL Server 2005 instance from a certain client box ,correct?

Generally for SQL Server connecting issue, there are many possible causes
such as the server's firewall, SQL Server's remote connection setting,
protocols settings .. As you mentioned that some other boxs can connect the
instance correctly, but this paricular client not work. I think it is
likely that there might be something wrong with the client machine's SQL
protocol configuration or the network connection of that box has some
problem.

here is a standard troubleshooting guide for SQL Server 2005 connectivity
issue:

#SQL Server 2005 Remote Connectivity Issue TroubleShoot
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remot
e-Connectivity-Issue-TroubleShooting.aspx

I think you can quickly go throught the server-side checklist and focus on
the client-side ones:

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

 

[Joe]

I checked all the possible causes but still no luck.

 

[George Wen]

I would suggest:

1, check your tcp/ip port and listening IP then run 'netstat -an' on the
server and write down the result, make sure they match.

2, on the working workstation, run 'telnet sqlserver port' and see if
you can connect;

3, run the same command as step 2 on the computer that has the issue and
check the result;

4, try 'ping sqlserver' on both computer see if you can resolve computer
name,

5, if there is a problem with dns resolving, replace 'sqlserver'
computername with IP address and try again.

6, if the above steps don't lead to a solution, try using SQL login from
the problematic computer....

 

[Joe]

I can telnet to the server from the machines that do work but cannot from
the machine that will not connect.

I can ping the server though using the name on all machines.

I also tried telnet by IP but that doesn't work either.

I don't know if this matter but the server I'm trying to connect to is SQL
Server 2000.

 

[William \(Bill\) Vaughn]

Okay, it sounds like a firewall issue. Try disabling the firewall on the
client. I would also make sure the SQL Browser service is running and that
the login credentials you're using have rights to the default database.

I too have written quite a bit about connecting. I suggest you start with
the whitepaper on my blog. I've also written a long chapter on connecting
that has helped a lot of people establish and maintain connections. In any
case, let's see your ConnectString. That will help isolate the issues
further.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Joe]

It turned out it was a firewall issue on the router. Instead of the port
being opened it was blocked.

Thanks for your help and sorry about wasting your time.
-Joe

 

[Steven Cheng]

Thanks for your followup Joe,

I'm glad that you've figured it out. Firewall on router does well explain
this behavior.

Have a good day!

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).