Solved

SQL Server 2000 remote instance connection

Posted on 2013-05-31
6
515 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

861 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now