Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

SQL Server 2000 remote instance connection

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
adimit19
Asked:
adimit19
5 Solutions
 
nemws1Database AdministratorCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Additionally to the above comments, please make sure the SQLBrowser service is actually running...
0
 
Anthony PerkinsCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
 
Anthony PerkinsCommented:
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
 
adimit19Author Commented:
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now