connection issue with enterprise manager but works with management studio

Hi,

I have an interesting scenario.

I have a SQL Server 2000 Standard Edition instance running on Computer A. I'm trying to access it through computers B and C.

Computer B has Sql Server 2005 Express Edition installed and I was able to use its Management Studio and
connect to instance on Computer A.

Computer C has SQL Server 2000 Standard Edition installed. When I try to connect to the instance on Computer A,
I get connection failed message.

I checked some settings. Both TCP/IP and Named pipes are enabled on the instance in A. The TCP/IP port is set to 1433 for
both client and server.

Please give me some ideas as to how I can solve this problem.

-Thanks

What is the error message that is displayed?|||I am assuming all three are on the same network then it is a simple right click at the top of Management Studio and register the 2000 then it becomes local to you. I have registered 68 7/2000 in one box so it works . Hope this helps.|||

The error said something like

Invalid Connection.ConnetionOpen(Invalid Instance)

|||

I figured it out.

Both A and C had Sql Server 2000 instances. When you try to connect to one Sql Server 2000 instance from another, Enterprise Manager automatically sets "Dynamically determine port" option and hence the connection fails. What you can do is use the Client Network Utility in C and create a new TCP/IP alias and uncheck the "dynamically determine port" option and manually set it to 1433. Then u can use Enterprise Manager in C and select the new alias that you created and finish registering the 2000 instance from A.

Thanks for your replies.

|||

shiversticks:

I figured it out.

Both A and C had Sql Server 2000 instances. When you try to connect to one Sql Server 2000 instance from another, Enterprise Manager automatically sets "Dynamically determine port" option and hence the connection fails. What you can do is use the Client Network Utility in C and create a new TCP/IP alias and uncheck the "dynamically determine port" option and manually set it to 1433. Then u can use Enterprise Manager in C and select the new alias that you created and finish registering the 2000 instance from A.

Thanks for your replies.

(on the same network then it is a simple right click at the top of Management Studio and register the 2000 then it becomes local to you. I have registered 68 7/2000 in one box so it works )

For the reference of future readers of this thread you just right click and register the other SQL Server as I originally posted and leave your port setings alone in eigth years I have not had reason to touch port settings it is not needed. Port settings could lead to SQL injection attacks.