SQL Server 2005 remote connection failure

Prabal

I'm having a big problem with a SQL 2K5 cluster (using standard edition). I can get onto the box and connect to the sql server locally fine. However when I try to connect remotely I get errors.

Let's call the two sides of the cluster, CL1 and CL2 and the "virtual server" is CL3. Locally I can connect without any problem at all to the SQL server, but I just can't connect to it from any remote machine.

The server in question is instance name LMS_PRD running on CL3, port 10051. TCP/IP is definitely enabled for local and remote connections (I have checked this in the SQL server surface area configuration). I can connect locally without any problem. Also, locally, if I do,

telnet CL3 10051

it connects fine

From a SQL 2000 box - when I try to connect from sql query analyzer, I get

"Unable to connect to server LMS_PRD Server Msg 17 ... SQL server does not exist or access denied"

Client network utility shows server alias - LMS_PRD, TCP/IP, CL3\LMS_PRD,10051

[I can connect to other SQL 2005 servers OK from this same server using this same method]

On this box, when I do telnet CL3 10051 , I get "Connecting to CL3...Could not open a connection to host on port 10051 : Connect failed"

[When I do a similar one for another SQL 2005 box, "telnet U01 10051", it connects fine]

From another SQL 2005 box - when I try to register the server, I get

"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 thew target machine actively refused it.) (Microsoft SQL Server, Error: 10061)"

IN the SQL native client configuration it shows alias name - CL3\LMS_PRD, server=CL3\LMS_PRD, protocol tcp, parameters 10051

Again if I do telnet CL3 10051, I get the error above.

From this same box I can register another SQL 2005 server without any problem and do the telnet to it OK

Just to emphasise:

(a) The SQL server configuration manager shows that TCP/IP is enabled, and that 10051 is the port (in IPALL, TCP port). Also I have checked the SQL server errorlog - it is definitely using 10051 as the port.

(b) The SQL server surface area configuration shows under local and remote connections using both TCP/IP and named pipes selected (I've tried both this and using the tcp/ip only option)

(c) SQL server browser is running

(d) There are NO firewalls etc involved

Any help or advice MUCH appreciated

Jack He

i think it's the network problem.

as you say: you has do the telnet the port .but it's failed.So it must the network problem

you could telnet the CL3 10051 from tow independent machine. if you get error in two box,

there are no doubt that :the network is not proper .

1: the DNS to CL3 work wrong : it cant' dns the name CL3 to the ip address

(So you could test another port of CL3 etc:CL3 80,if you could telnet it ok)

2: the pipeline connect the 10051 port was been interrupted by firewall or the network setting .

Prabal

Hello Jack, thanks for your response. I don't think it's the DNS, ping CL3 gives the correct IP address, also telnet <ip addr> 10051 also doesn't work. I tried "telnet CL3 21" that worked. Does that mean it must be what you say in point 2 above i.e. firewall or network settings are stopping the communication

Jack He

Yes,i think there are problem about the port 10051

May be you could try to change the port to another ,and test it again!

Prabal

I tried port 1433 (I.e. the default) and it still doesn't work

oj

1. Do a "ping -a <virtualIP>" and "ping <virtualName>". Compare the two and make sure they're correct.

2. Alot of client tools are x86 so be sure you have a valid alias for x86 (or wow64 on x64 system). Check %windir%\system32\cliconfg.exe for x64 and %windir%\syswow64\cliconfg.exe for x86.

3. Create a tcp alias (name=cl3\lms_prd, server=virtualIP, port=10051). By specifying the actual port, you by-pass sqlbrowser completely.

Jack He

HI,Prabal

Could you please show the capture of the "cluster administrator" and "SQL server configuration manager"

and "SQL server surface area configuration"

I would like to see it and check if there no problem

OK

Prabal

Finally got to the bottom of this. It suddenly occurred to me that I hadn't looked in the event log, so I had a look and lo and behold: there was an error that suggested that the virtual server's IP address was not unique on the network! Looks like some sort of solaris machine was using the same IP address. I've changed it (impressed how easy it was - just a setting from the cluster administrator) and now all connectivity issues are gone.

I suppose the moral of the story is always to check the system logs first!

Thanks guys for your help

Jack He

congratulations!!

oj

Great. If you ever have a need to troubleshoot connectivity issue again, do remember to do "ping -a <IP>" and "ping <servername>". Also, do nslookup to make sure you are connecting to the right server as you think you are.

SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure

Re: SQL Server 2005 remote connection failure