Solved

SQL Server 2000 remote instance connection

Posted on 2013-05-31
6
514 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 200 total points
Comment Utility
<<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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

6 Experts available now in Live!

Get 1:1 Help Now