Solved

SQL Server 2000 remote instance connection

Posted on 2013-05-31
6
523 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 100 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 200 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 200 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 200 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 200 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 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