Ted Palmer
asked on
Connecting to a Named Instance of MS SQL Server 2000 using Query Analyzer
Issue: Connect to MS-SQL-Server 2000
OS: Windows Server 2003 Standard Edition
I have 2 differend network servers each running Windows Server 2003 SE as the Network Operating System (NOS) (Primary and Secondary). Let's name them WS2K3_SVR1 and WS2K3_SVR2. Each network server has a named instance of MS SQL Server 2000 running on them. Let's call them WS2K3_SVR1\Inst and WS2K3_SVR2\Inst. Using Query Analyzer, I can connect from my workstation running Windows 2000 Pro over the network to WS2K3_SVR2\Inst using Windows Authentication, but I can not connect to WS2K3_SVR1\Inst using Windows Authentication. My network user ID is a member of the Administrators Group in Computer Management on both Network Servers. Both network servers are located in a different state from where me and my workstation are located. Connectivity is over an Intranet using TCP/IP.
Looking in Enterprise Manager (EM) using (Remote Admin) on both systems, under the instance name of each MS SQL Server 2000 instance: \\Console Root\Microsoft SQL Servers\SQL Server Group\WS2K3_SVR1\Inst\Secu rity\Login s "BUILTIN\Administrators Windows Group" is listed.
The only difference I see at this point is the string "SQL Server Group" in the path. It is different on the one that I can connect to. That is because on installation it was assigned that way. "SQL Server Group" looks to me like a generic name provided as a default by the setup installation program. The default was not taken when WS2K3_SVR2\Inst was installed. That is the one that I can connect to. This part of the instance name is not specified anywhere that I can find in any of the syntax definitions that I can find for how to specify a connect string.
So why can I not connect..??
Ted Palmer
OS: Windows Server 2003 Standard Edition
I have 2 differend network servers each running Windows Server 2003 SE as the Network Operating System (NOS) (Primary and Secondary). Let's name them WS2K3_SVR1 and WS2K3_SVR2. Each network server has a named instance of MS SQL Server 2000 running on them. Let's call them WS2K3_SVR1\Inst and WS2K3_SVR2\Inst. Using Query Analyzer, I can connect from my workstation running Windows 2000 Pro over the network to WS2K3_SVR2\Inst using Windows Authentication, but I can not connect to WS2K3_SVR1\Inst using Windows Authentication. My network user ID is a member of the Administrators Group in Computer Management on both Network Servers. Both network servers are located in a different state from where me and my workstation are located. Connectivity is over an Intranet using TCP/IP.
Looking in Enterprise Manager (EM) using (Remote Admin) on both systems, under the instance name of each MS SQL Server 2000 instance: \\Console Root\Microsoft SQL Servers\SQL Server Group\WS2K3_SVR1\Inst\Secu
The only difference I see at this point is the string "SQL Server Group" in the path. It is different on the one that I can connect to. That is because on installation it was assigned that way. "SQL Server Group" looks to me like a generic name provided as a default by the setup installation program. The default was not taken when WS2K3_SVR2\Inst was installed. That is the one that I can connect to. This part of the instance name is not specified anywhere that I can find in any of the syntax definitions that I can find for how to specify a connect string.
So why can I not connect..??
Ted Palmer
What TCP/IP ports are the instances responding on? Can you Telnet to them on the relevant port?
ASKER
If you send e-mail to the address in my profile, I won't see it till I get home. My e-mail address at work is different from the one in my profile and I don't want to include it here.
That aimed at me, or a general comment?
ASKER
Using the SQL Server Network Utility I can tell that they are both the default 1119.
ASKER
General comment.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If I try to telnet using port 1119 I get "unable to connect to host".
On the dodgy machine or on both?
ASKER
When I telnet to the one that I can connect to on port 1119, I get a Ready prompt. I never would have thought of trying to telnet to a MS SQL Server running on port 1119. I thought Telnet was port 23..??
I have to go to a meeting with my boss. I'll be back soon I hope..!!
I have to go to a meeting with my boss. I'll be back soon I hope..!!
Telnet can be used on any port, it simply establishes that there is a listener there. You almost certainly have a firewall issue I would say. The firewall guys have probably opened up the default instance port of 1433 and not expected the named instance port you need of 1119.
ASKER
muzzy2003:
It appears that item 1 of the 3 item list is the cause of the problem. I can ping both hosts. I can establish a telnet connection to the same MS SQL Server that I can connect to using Query Analyzer; but I can not do the same for the MS SQL Server that I also can not connect to using Query Analyzer.
The resolution of these issues is outside the scope of this question. At least I now know what the issue is that has to be resolved. Making that happen is another bureaucratic night mare that will consume more of my time than I even want to think about. But that is my problem. You got the solution so you get the points.
THANK YOU..!!
It appears that item 1 of the 3 item list is the cause of the problem. I can ping both hosts. I can establish a telnet connection to the same MS SQL Server that I can connect to using Query Analyzer; but I can not do the same for the MS SQL Server that I also can not connect to using Query Analyzer.
The resolution of these issues is outside the scope of this question. At least I now know what the issue is that has to be resolved. Making that happen is another bureaucratic night mare that will consume more of my time than I even want to think about. But that is my problem. You got the solution so you get the points.
THANK YOU..!!
It's a pleasure. As you can probably tell, I've run into the same problem more times than I care to remember. One of the downsides of being a contractor - every time you move companies, you have start educating the firewall gorillas all over again ...