connecting to remote sql server

[MalcUK]

I am trying to set up an odbc connection to a remote SQL Server 2000 database. I have access to the server via remote desktop connection and can ping its IP successfully, but I cant get through odbc setup.

In Create New Data Source, I select the SQL Server driver, For server I put the correct IP address. I've tried NT and SQL authentication ... but because my NT login on the remote doesn't match Server, I select SQL authentication. Client Configuration shows the IP in Server alias and server name. Library is set t TCP/IP. Port is set to 1433. I've checked that remote firewall isn't blocking that port. I put "sa" in login id and supply the password.

When I click Next the result is always the same:
Connection failed, SQLstate 01000, Error 10061 ConnectionOpen(connect()), followed by SQLstate 08001, Error 17, Server does not exist or access denied.

This has had me stuck for a week. Can anyone help out?

 

[JFBouchard]

Hi, you should try to register in enterprise manager on the XP workstation (install it). Do not use the enterprise installed on the server.

Hope this helps. Please let know if this resolve your issue

Jeff

Failure is not an option

 

[MalcUK]

I'm working from home on XP SP2. I'm writing an application that needs to connect from any pc to the SQL server at the office. ODBC works okay connecting to a local server on my home network. It also works connecting to the office server from another pc on the internal office network. The bit I cant get to work is the ODBC connection from home to office. My home pc is not part of the office domain and I have a standard broadband ISDN line.

I can access their network and the sql server from here with Remote Desktop Connection, using the same IP I've been given for the server.

They say yes, 1434 UDP is enabled. Is there a way I can verify that?

As for logins, I've tried all sorts - using the admin name and password for the domain, my own office domain login, my sql login, and the 'sa' login. I've been in to the logins section on E.Manager and checked all the access rights.

Finally ... Thanks Jeff. Tried connecting with E.Manager on workstation. The server IP is shown as an available server, but connection fails no matter what login I provide.


Pathway UK Ltd

http://www.pathwayuk.co.uk

 

[JFBouchard]

I've tested a DSN when monitoring with TDIMON and the calls are made to 1433 TCP port not the 1434 UDP.

Hope this helps. Please let know if this resolve your issue

Jeff

Failure is not an option

 

[Catadmin]

It sounds like something in your network connection to the office. Is the only way you are connecting from home to the office via the Remote Desktop tool? If so, then the ODBC needs to be on the server and probably the application does too.

Does the application use a specific user name/pwd to log into SQL? Or does it rely on the user's Windows credentials?

At this point, I would look into alternative ways of connecting to the office network like dual network cards & hardware profiles (one for "in office" and one for "out of office") or something else. I don't think this is an actual SQL problem.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[SQLBill]

'Nother thing....have you tried registering the SQL Server by using the IP address vice the name? Sometimes name resolution isn't set up or not working properly.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Sorry, my fault, just reread the initial post and you did use the IP address.

-SQLBill

Posting advice: FAQ481-4875

 

[MalcUK]

Could we look at this another way for a minute.

A new user downloads my application from the internet onto a stand alone laptop. The application knows the server IP, port, a SQL username and password. Assume the server is running properly. What would need to be set up for the laptop to access a table in a database on the server? Does it need odbc at all?



Pathway UK Ltd

http://www.pathwayuk.co.uk

 

[JFBouchard]

Hi, not really. A .UDL file on the server can be used instead of a standard DSN on the client computer. The connection string may also be defined in your application.

Do you have other DSN who's actually working? Try to create a local one and test it. Maybe it's simply your MDAC who's corrupted. Try reinstall it on the client computer.

Hope this helps. Please let know if this resolve your issue

Jeff

 

[SQLBill]

Make sure any firewalls permit the traffic.
Make sure the client machine has the latest MDAC version as Jeff stated.
Set up the ODBC connection using an Admin account and using the System DSN setup.

Make sure the server and SQL Server are set to audit for failed and successful logins.
Try to connect. If it fails, check the Server OS Windows Event Viewer logs to see if it shows a failure. Then check the SQL Server Logs to see if it shows a failure.

-SQLBill

Posting advice: FAQ481-4875