Connections to Named instance not working

Question

Connections to Named instance not working

John Burgess-239089

SSC Veteran

Points: 222
More actions
July 18, 2005 at 5:24 am

#90314

Recently, my Enterprise Manager connections to a customers SQL Servers have stopped working.
Connections to all SQLServers with named instances will not work if I use the TCP/IP protocol. Even with an alias defined in the client configuration screen. Connections to servers with a default instance works OK.
I can get a connection using a named pipe alias, but I must first map a drive to a share on the server before the Enterprise Manager connection will work.
Because I have 30+ SQLServers to administer, it is not practical for me to use named pipes if I must have a drive mapping to every server.
I can't understand why TCP/IP will not work. I have checked that I have access to the port numbers used by SQL Server and this is OK with telnet for example.
Has anyone had similar experience ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hello John,

when you say "I have checked that I have access to the port numbers used by SQL Server" do you mean 1433 / 1434 or you mean port numbers of named instances? Your problem does look very much as a firewall problem. Another thing: SQL Server service that browses between instances uses 1434. If you have instances ports open but do not have 1434 it may be a reason.

Yelena

Regards,Yelena Varsha

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Not knowing much about your situation, Are they default instances, or named instances?

if it is a default instance, then just using the machine name is okay.

If it is a named instance, then you need to use \\machineName\InstanceName.

Or IP address.

Do not know enough about your configuration to know if its port issue, or Firewall issue, but if it worked before then stopped what changed?

John Burgess-239089 SSC Veteran Points: 222 More actions · Answer 3

Hi

They are named instances, on clustered servers. In a cluster, when you perform the second installation of SQL Server 2000, you are forced to use an instance name. When the named instance starts, it uses a different port to the default instance. For example, I have a clustered server where the default instance is using port 1433 and I have no problem connecting to it, but I cannot connect to the named instance, which is using port 1155.

I have tried various options using an alias, setting the port number in the alias to match the actual one in use, but to no avail. Named Pipes works, but I have to make a drive mapping first (I don't know why) and this will not be practical with 30+ servers to administer.

This coincides with my company installing a firewall, so I think it could be a firewall problem, but I have made telnet connections to all these ports with no problem. I am more inclined to think there is a bug in the firewall software that doesn't like the servername\instance format. But to make matters more confusing, I have PC's also going through the firewall that have no problem connecting at all. Same MDAC version, same O/S and Service Pack.

John Burgess-239089 SSC Veteran Points: 222 More actions · Answer 4

Yelena you were right..

If I specifically use the port number that the SQL Server instance is listening on, then I can get a connection throught TCP/IP.

This simply means, having a TCP/IP alias for each server with a named instance, and unticking the `Dynamically determine port` option, then enter the number of the port it is using.

This must mean, as you said, that port 1434 is blocked and so we cannot dynamically determine the port.

Thanks for your help...much appreciated

/John

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

John,

I am glad it worked for you.

Yelena

Regards,Yelena Varsha

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

FYI --> port 1434 is UDP, not TCP. Your network folks may have blocked it to stop things like the 'slammer' virus (albeit a tad late and not necessary internally).

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

John Burgess-239089 SSC Veteran Points: 222 More actions · Answer 7

Strangely enough we have some SQL Servers listening on port 1434 for TCP/IP.

I still can't explain this, but using the actual port number in our cliconfg alias seems to work. If you tick the option `Dynamically determine port` then it stops working.

/John

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

Things stop working because unless you specify the actual port, SQL Server will try to establish communications on UDP port 1434 to try and figure oput the port number itself. So I'm almost certain that your network folks have done something.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

RandomStream Hall of Fame Points: 3735 More actions · Answer 9

I had the same problem and fortunately I was allowed to open all ports on the DB server:

Local Area Connection properties - TCP/IP - Advanced - Options tab -TCP/IP filtering properties - Check Permit All on three.

Of course, ideally I should've only allowed the SQL ports.