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?

 

[JamesLean]

First check you can actually get to the server thru that port. From a command line type:

telnet x.x.x.x 1433

If you get a blank screen with a flashing cursor that is ok.

--James

 

[MalcUK]

Hi James.
Thx for your quick and useful reply. Ping works, but Telnet with port 1433 returns Connect Failed. Unfortunately, the chap who handles the firewall that end is off today but it looks like the problem is to do with their firewall, doesn't it, despite what they say about having cleared it. Is it just that port that ODBC will need or are there others?

Malc

 

[JamesLean]

Yep, it certainly seems like a firewall problem. You should just have to open TCP port 1433 and you're in business.

--James

 

[JamesLean]

BTW, that's assuming the SQL Server is set up with defaults. If the firewall guy says he has opened that port, it could be that SQL has been configured to listen on a different port.

--James

 

[Catadmin]

Also, verify what protocol you're using. Sometimes if you change from Named Pipes to TCP/IP or vice versa, the connection on the ODBC will go through. Simply hit the "Client Configuration" button on the second page of the ODBC setup.

Don't ask me why changing the protocol sometimes makes the difference with ODBC setups, but it does. @=)

Hope this helps!



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

 

[MalcUK]

ok. ... tech people on the server say firewall *is* set to allow port 1433. They ask, "Does it need UDP as well as TCP port enabled?" and "Are there any other ports which SQL server might need to use in addition to the default one?" Could someone enlighten me?

Is there a situation where a clever firewall would still block telnet even if the port was enabled ... because of security or authorisation settings? Or does telnet failing prove they haven't enabled the port correctly?

Thx Catadmin. I tried named pipes but no joy.

 

[Catadmin]

As far as I know, only port 1433 needs to be enabled for regular SQL Server communication. For other applications, such as PC Anywhere or Port Forwarding, usually a TCP port (Data) and a UDP port (Status) are required, but I've never seen any SQL Server documentation that requires it to have both a TCP and a UDP port open.

Have you checked msdn.microsoft.com to see if you can find any port info?

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

 

[Catadmin]

Okay, ask them if they have Port 1434 open as UDP.



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

 

[MalcUK]

Thx for your help, guys. Much appreciated.

Getting nowhere fast here though. In summary
From the client ...
Client settings are for TCP, port 1433
Ping the server IP works
Telnet the server IP port 1433 fails
Odbcping server IP and port, user name, password fails with errors 17 and 10061 (same as odbc wizard)
PortQry the IP, port 80, reports LISTENING
PortQry the IP, port 1433, reports NOT LISTENING

On the server ...
I have access to server enterprise manager via remote desktop connection
SQL Server is configured to use TCP and port 1433
PortQry port 1433 reports
PID Port State Remote IP
1704 TCP 1433 Listening 0.0.0.0:40984
1704 TCP 57070 Established x.x.x.x:1025
1704 UDP 57071 *:*

Their tech guy assures me the firewall is not blocking 1433. He says he can 'see' my attempts to access the prot and they are getting through. His view is that I've not configured the server properly - but I have checked and double checked protocols, logins etc.

And of course ODBC is still failing to connect.

 

[JFBouchard]

Hi, JamesLean is quite right about making certain that your sql server is configured to use the default port.

Since the SQL Slammer worm, many admins (such as myself) have reconfigure their servers to use another port than the default to increase security.

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

Jeff

 

[MalcUK]

Hi Jeff

On the server I rightclick (Local), select Properties. Click Network configuration button. Named Pipes and TCP/IP are enabled. Force encyption is off, enable winsock proxy is off. I highlight TCP/IP and click properties.

Default port says 1433
Hide server is off.

I've also checked that maximum concurent connections is 0 (unlimited). Authentication is SQL and Windows. Startup parameters are -d -e and -l (the defaults)

 

[JFBouchard]

Hi, ok for the port.

Am I correct if I assume that the workstation you're trying to set the ODBC data source on is a Windows XP SP2?

If so, it's may be the firewall on the workstation that is preventing you from creating your DSN (try to disable it to test this hypothesis). (start/run/firewall.cpl/select disabled)

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

Jeff

Don't steal. The Government hates competition

 

[MalcUK]

Thx for suggestion, Jeff. I turned off client firewall. Tests with ODBC wizard, ping, telnet and odbcping all still fail. We're missing something.

 

[JFBouchard]

Try (even if the firewall is disabled) to open TCP port 1433 and UDP port 1434 in the firewall exceptions on the workstation.





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

Jeff

Don't steal. The Government hates competition

 

[SQLBill]

I had a problem with a user's system recently. Couldn't set up an ODBC connection for Crystal Reports to talk to SQL Server. Box is XP SP2. Did everything you did, did everything suggested - nothing worked.

Finally, found out that the ODBC connection had to be set up by the ADMINISTRATOR. It was a security policy in place. So I logged on with my Admin loging on the user's box and set the ODBC connection.

So, have the sysadmin for the user's box login and try to set the connection up.

-SQLBill

Posting advice: FAQ481-4875

 

[JFBouchard]

Hi,

SQLBill, your post is very pertinent but since MalcUK can turn off the windows firewall, he must have local admin permissions to do this.

MalcUK, can you set the tcp/ip connection to default instead of named pipes?

Also, have you tried to register the rmeote server in sql enterprise manager? does it works?

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

Jeff

Failure is not an option

 

[Catadmin]

And make sure you set up the ODBC connection as a System DSN instead of a User DSN or other users besides the SysAdmin won't be able to use the ODBC.

Also, verify there aren't multiple ODBCs under the same DSN name in effect.



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

 

[MalcUK]

Good idea, SQLBill but as pointed out later, I am the sysadmin for the XP SP2 workstation.

Yes, I'm using system DSN, and I've deleted all other DSNs

Re:Registering the server in enterprise manager ... Is that rightclicking on (local) and selecting Add SQL server registration? I can edit existing local one. I can also create a new registration where it says there is an available server named MSF01.

I've have tcp/ip connection as default instead of named pipes. In ODBC Client configuration I've tried ticking 'dynamically determin port' as well as specifying 1433 directly.

Huge thanks to all who are helping on this.

Pathway UK Ltd

http://www.pathwayuk.co.uk

 

[Catadmin]

I apologize if someone already asked these questions, but I'm going to ask again.

Is the SQL Server you're trying to connect to in the same domain you are? The same subnet or a different one?

If not the same domain, are you in a workgroup instead of a domain? And is there a trusted 2 way connection between your workstation and the SQL Server machine?

You said earlier "They ask, "Does it need UDP as well as TCP port enabled?" and "Are there any other ports which SQL server might need to use in addition to the default one?" " Did you manage to check to see if they had port 1434 set as UDP through the firewall as well?

Finally, did you verify with whomever is the Admin of the SQL Server box that the login you are trying to connect with (via telnet AND ODBC) has permissions to actually log into the SQL Server?

Remember, SQL Server logins are NOT the same thing as Database login permissions. You have to have the first before you can log into SQL Server. Also, verify that you aren't part of a domain group that has any deny permissions that might trickle down to the SQL Server logins or that your personal login doesn't have deny permissions overriding other permissions.



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