Connect SQL Server 2000 remotely in same network

Question

Hi,

after i successfully installed MS SQL SERVER 2000, another problem came.

I tried to connect to the sql server 2000 in the same network,but i can't even find the server under my "AVAILALE LIST" of server. Anyone has idea?

Please help and advice..Many thanks..

Regards,
Terry

Answer 1

Hi Terry,

Can you run OSQL -L in the command prompt to see whther the sql 2000 instance shows up in the remote machine ?
Also post the error message .

---

Thanks, Leks

Answer 2

Type in my Local PC or the remote machine?

Answer 3

The remote server host name is not showed up here after i follow your instruction.

Answer 4

Can you try to ping the server from the remote machine ?

Run in cmd mode -- > PING SERVERNAME

Servername - your machine name where sql 2000 instance lives.

Also open SSMS or EM in your remote machine and try to connect to your sql 2000 instance and see what is the error that pops up.

---

Thanks, Leks

Answer 5

Hi..

Ping server see the response from the server and indicate the server is up and running.. so basically the server is there.

But when try to connect will see this error msg:

SQL SERVER does not exists or access denied. ConnectionOpen(Connect())

Answer 6

Are you using sql authenticated login , if yes then check your sql 2000 whether it has been configured with mixed mode authentication.
Remove sql server from windows firewall if somthing exists.
Enable TCP/IP and named pipes protocol in your client network and server network utility on the sql 2000 instance.
Apart from all of these , make sure your SQL 2000 is started and running fine . You can check this from services.msc.

---

Thanks, Leks

Answer 7

Is it because of OS? The server is using XP pro sp3.....the local pc is also same OS.

I had followed all the things u stated above..but still cannot locate the server.

I use SQL AUtehtication MOde. The user login i used is SA when try to connect to server.

SQL SERVER is running fine.

TCP\IP and also named pipe protocol is enabled

Answer 8

Hi Terry,

The OS shouldnt be a problem here .
What is the servername that you are providing here in the connection box ? Also Login to your SQL 2000 EM and  right click on the server and properties and go to security and check once again whether MIXED mode authentication is enabled.

---

Thanks, Leks

Answer 9

The host name of the remote server. that is TND-NB-024

Yes.I login to 2000 EM and right click on the server property-->security....

at here...i had chosen "SQL Server and Windows" for authentication mode.

Answer 10

this TND-NB-024 never show up in the list of server.

Answer 11

 Execute select @@servername in sql 2000 and see that matches with your host name (That should match as you have installed sql 2000 as default instance)

Go to cmd mode in your remote machine (not the sql 2000 machine) and exec

OSQL -U name_of_sql_account -P password_for that _sql_acc -S [TND-NB-024 ]

Run this and see what is the error being generated.

---

Thanks, Leks

Answer 12

The server return value: TND-NB-024 which is equal.

[DBNETLIB] SQL SERVER does not exists or access denied

[DBNETLIB]ConnectionOpen (Connect())

Answer 13

Please check whether SQL Server's default port is 1433 .
Some times the DNS will be making this problem...This error comes out when thr connection can't recognise the name of the server. Can you try to connect with your IP once ? Check your password again .!

---

Thanks, Leks

Answer 14

Default port: 1433

then i try connect with IP.....also yied no result. same error when connecting to sql server.

Th IP is assigned by DHCP server.

Answer 15

Can you (or the DBA) login with the sa password if you are on the same computer (as SQL Server 2000 instance)? Using terminal services?

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 16

Yes. I can login with sa password on the same computer.NO, is not using terminal services.

I use netstat -a command to list out all the ports status...but 1433 is missing. will it due to router's port forwarding?

Then i login to my colleague's pc on the same network (INstalled with SQL SERVER 2005, NOT sql server 2000), the port 1433 is LISTENING.

Answer 17

Hi Terry,

May be these links can be helpful

http://www.sqlservercentral.com/Forums/Topic525000-5-1.aspx
http://sqladvice.com/blogs/repeatableread/archive/2005/03/04/4262.aspx
http://msdn.microsoft.com/en-us/library/ms189307.aspx

 

---

Thanks, Leks

Answer 18

Thanks. Problem still exists.

When i try to login as "SA" with a password (in the remote server itself), the following error is displayed:

Meta Data Services:

 mmc has detected an error in a snap-in. it is recommended that you shut down and restart mmc

MDAC version is 2.8 an i believe this version do not contain any flaw but not sure about this.

Answer 19

Hi terry,

If that is the error , can you close all the MMC windows and reopen the application again.
Then again try with your sa password.

If there seems to be any problem , download mmc 3.0 for win XP and install it .
http://support.microsoft.com/kb/907265

---

Thanks, Leks

Answer 20

Can you check whether the port 1433 is opened in firewall? Can you run telnet and check whether you are able to connect to the server using 1433?

TELNET servername 1433

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 21

Hi..using Telnet servername 1433 will see the following msg:

could not open connection to the host, on port 1433: connect failed.

and also...the firewall...i had "Add Port" -1433. Is anything wrong in this firewall or the step i missing to open the 1433 port?

Answer 22

Now try to exclude Sql server , port 1433 from the windows firewall on your sql 2000 hosted machine.

But earlier in the posts you had said that firewall wasn’t enabled.

---

Thanks, Leks

Answer 23

yaya...I off the window firewall already before this. NOw i open back just few minutes ago and add port. The firewall exception list include already this sql server

The MMC download...system telling me that my version is more updated than the one i try to download. So cannot proceed. MMC got anything to do with this? weird.

I also re-installed again the sql server 2000 but problem still exists.

Answer 24

To check whether the port is open now , can you again run this

TELNET servername 1433

If this is ok , Try to reboot your machine (If you havent done it after the reinstall)

---

Thanks, Leks

Answer 25

still cannot.

TELNET TND-NB-024 1433

response: could not open connection to the host, on port 1433

The server is connected to network using WIRE....and then the machine is using wireless. I worry problem might due to router setting? But is in a LAN and the system admin told me that they did not block any port from running internally.

Answer 26

and now i OFF the window firewall.. EASIER.

I type netstat -a.  the 1433 is not listening to any incoming request

Answer 27

Hi Terry,

Have you tried to connect to sql 2000 from remote client with the Ip address after the re-install. ?
Check the service status once again.
Have you tried to create a system DSN for sql server and then try connecting?

---

Thanks, Leks

Answer 28

YEs...i did try connect with IP addess..no luck. -ERROR SHOWN: SQL SERVER does not exists or access denied. ConnectionOpen(connect())

service status is START.

never use DNS.

ok.I think i try describe the whole installation process that i did (sql server 2000--the SERVER).

computer name: TND-NB-024

server and client tools.

Mixed mode authetication with SA account and password.



SQL SERVER: Auto Start service for SQL SERVER. - the checkbox is checked

SQL AGENT : Auto Start service for SQL SERVER. -  NO

BOTH use LOCAL SYSTEM instead of DOMAIN.

that;s it. WHat went wrong.

Answer 29

Just a try ,

Can you try to use your servername inside a double quotes.
Like this "TND-NB-024" in your connection string.

---

Thanks, Leks

Answer 30

haha. Putting this double quotes does not help either.

thxs for putting ur heart to help me solve this problem. Appreciate that, really.

MMC problem..is this a cause?

Answer 31

MMC problem is on the sql server 2000 machine or the remote machine ?

---

Thanks, Leks

Answer 32

MMC problem isi n the sql server 2000 machine (the server).

Could it possibly due to user permission? Local security policy

Answer 33

Hi....the problem seem OK already. I can connect by using IP. SOmetme using hostname is not really a way to go. Don't know why.

I can connect and perform data manipulation in the server. Looks good.

But one error msg which i force IGNORE the MMC error snap-in and continue to connect to the server. SO basically, i deem that was minor error...but it still work great. I'm not sure WHY the MMC error will show up in this case. No idea at all. Prior to this for MMC error, i always choose to SHUT DOWN the sql server option,thinking that it must be cannot work later on.

Remember...connect by IP is te best way when we fail to connect. The server never show up in the "Available Sever: list, so i just manually key in the server IP address. Then connect. Although getting the MMC error msg..i just ignore and continue to connect.

Not sure i had to update the MMC or not as OS already come with the SP3.

Thanks Less.

Cheers.