DB connectivity issue with sql server 2000 named instance

Question

DB connectivity issue with sql server 2000 named instance

Madhava Rao-462422

SSC Enthusiast

Points: 117
More actions
May 18, 2010 at 11:23 am

#73679

Hi all,
I am facing an issue while connecting to sqlserver 2000 named instance.
We have a new Dev Box with Windows server 2008 (SE, 32Bit). I was first installed sqlserver 2008 named instance (machinename\SQL2008)and applied sp1. Later installed sqlserver 2000 named instance (machinename\SQL2000) and applied SP4. I can able to connect both instances from the server. But when I try to connect to machinename\SQL2000 from remote client, I am getting the below error.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
I can able to connect to sqlserver 2008 named instance form remote client successfully. Why I am getting this error while connect sql2000 named instance. I have disabled windows firewall but of no use. While troubleshooting this issue, I found that the named instance machinename\SQL2000 is not there in Instance Names drop down list on "Server Network Utility". Can you please help me troubleshooting this issue??
Thanks in Advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

enable remote connections on your SQL 2000; i belive by default express versions set remote access as off, and you have to turn it on:

exec sp_configure 'remote access', 1

reconfigure

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Madhava Rao-462422 SSC Enthusiast Points: 117 More actions · Answer 2

Thank you for the quick reply. Remote connections were already enabled. I am not using express edition. It is developer edition.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

There migh be any kind of issue, it's hard to predict one..

Have you made sure, you are able to connect to the SQL 2000 Instance locally on the server? Just to make sure the SQL Server is started and accepting connections (at least locally)

Are you aware of the Port # on which SQL Server listens? Tried connecting using IP,Port# to connecr remotely? Ensure that the required Protocols are enabled etc.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Madhava Rao-462422 SSC Enthusiast Points: 117 More actions · Answer 4

Yes.. I can able to connect to the instance locally on the server. I am getting the error when connecting from the remote client only. Actually I have installed two named instances on the server. one is MACHINENAME\SQL2008 other one is MACHINE_NAME\SQL2000. I can able to see only SQL2008 instance in the "SQL server network utility". Please see the attached image. Why I can not able to see other SQL2000 instance.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 5

Have you checked that TCP/IP is enabled on the SQL 2000 instance and that it is using a different port number than the 2008 instance? I thought that by default developer edition had TCP/IP disabled by default; only shared memory is enabled, but I could be wrong.

Joie Andrew
"Since 1982"