?
Solved

SQL Server 2000 remote instance connection

Posted on 2013-05-31
6
Medium Priority
?
539 Views
Last Modified: 2013-06-05
Hello

I have a SQL Serverinstance running on msde 2000 that I cannot connect to remotely. I can connect to the default instance.

TCP/IP is enabled for the instance.
0
Comment
Question by:adimit19
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 300 total points
ID: 39211638
2000 is getting pretty old. ;-)

I can only take a stab in the dark on this one... in 2008R2 there are 2 ways to connect to an instance.  Via the instance name and via the instance port.  I know you've enabled TCP/IP, but is it using a static port or a dynamic one?  I configure my instances with static ports (you can pick pretty much any number range that isn't in use.  14330 or 1435 make for okay starting points and then increment by one for each additional instance).

If you're using a dynamic port (ie you did *not* specify a static port), then your remote instances need to talk to the SQL Browser process, typically on port 1434.


When I have this issue, the first thing I do is to shut off my firewall and test a remote connection (turn your firewall back on again right away!)  9 times out of 10 this is the issue and I just need to add in a firewall rule.  You can try adding in the sqlserver.exe process for the new instance.  Usually I don't trust this, and again, use a static port and add that into my firewall.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 600 total points
ID: 39212837
Additionally to the above comments, please make sure the SQLBrowser service is actually running...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 39213112
Additionally to the above comments, please make sure the SQLBrowser service is actually running...
I did not know SQL Server 2000 had a SQLBrowser service.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 600 total points
ID: 39213259
<<I did not know SQL Server 2000 had a SQLBrowser service.>>
There was indeed no Browser Service on SQL 2000.  Since I was on a hurry to answer the question, I totally overlooked the version we were talking about.  My mistake.  Thanks for pointing out this error.  OP please ignore my comment which is only valid from 2005 to above.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 600 total points
ID: 39213301
Well not entirely incorrect.  :)

From SQL Server Browser Service

Background

Prior to SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 introduced the SQL Server Browser service as a replacement for SSRP.

...
Side-by-Side Installation with SQL Server 2000

In SQL Server 2000, the identification of the server connection endpoints is performed by the SQL Server service. SQL Server 2005 replaces that function with the SQL Server Browser service. If you install SQL Server on a computer that is also running SQL Server 2000 or MSDE, you must make sure that SQL Server 2000 or MSDE is upgraded to Service Pack 3a (SP3a) or later. Versions earlier than SP3a do not properly share port 1434 and may not make your instances of SQL Server available to requesting client applications. Although you can change the services startup order so that the SQL Server Browser service starts before SQL Server 2000 or MSDE, we recommend that you update all earlier versions of SQL Server to the latest service pack.

When an instance of SQL Server 2000 that is not updated to at least service pack 3a is installed on the computer, if the SQL Server Browser is not running, the SQL Server 2000 listener service starts. If SQL Server Browser starts after the listener service, it waits 5 seconds for SQL Server 2000 to give up port 1434. If that does not occur, SQL Server Browser fails to start. To resolve this problem with versions of SQL Server 2000 earlier than Service Pack 3a, stop SQL Server 2000, start SQL Server Browser, then restart SQL Server 2000. The SQL Server 2000 listener service continues to attempt to start on port 1434; therefore, the instance of SQL Server 2000 should be upgraded to Service Pack 3a as soon as possible.

SQL Server 7.0 has no similar capabilities and has no conflicts with SQL Server Browser.
0
 

Author Comment

by:adimit19
ID: 39222076
In the ned I removed named pipes from the sql netwrok client utility, and just left TCP/IP. That seemed to wrok and solve the problem.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question