[freetds] Unable to connect: Adaptive Server is unavailable or does not exist

Discussion:

Krish

2013-05-06 09:53:08 UTC

While trying to connect remote SQL Server:

$tsql -S localhost -U myuser

I got error like:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20009 (severity 9):
Unable to connect: Adaptive Server is unavailable or does not exist
OS error 61, "Connection refused"
There was a problem connecting to the server

The output of $tsql -C is as below:

Compile-time settings (established with the "configure" script)
Version: freetds v0.91
freetds.conf directory: /usr/local/freetds/conf/freetds
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: no
TDS version: 5.0
iODBC: no
unixodbc: no
SSPI "trusted" logins: no
Kerberos: no

I have created tunnel for remote server like:

$ssh -L 1433:db_server:1433 user at mid_server

This forward the local port 1433 to db_server. I tested. My ssh tunnel
works because I can connect to database using Navicat (localhost on
1433). I can telnet localhsot 1433.

------------------------------------------------------------------------------------
The information transmitted is intended only for the person or entity
to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this
information by person or entity other than the intended recipient is
not permitted. If you received this in error, please contact the
sender and delete the material from any computer.

Randy Syring

2013-05-06 13:38:03 UTC

Permalink

Use TDSDUMPCONFIG:

http://freetds.schemamania.org/userguide/logging.htm

to make sure you are connecting the way you think you are. The error is
essentially saying it can't find the server.

*Randy Syring*
Development | Executive Director
Direct: 502.276.0459
Office: 812.285.8766
Level 12 Technologies <https://www.lev12.com/>
/Principled People | Technology that Works/

Post by Krish
$tsql -S localhost -U myuser
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Unable to connect: Adaptive Server is unavailable or does not exist
OS error 61, "Connection refused"
There was a problem connecting to the server
Compile-time settings (established with the "configure" script)
Version: freetds v0.91
freetds.conf directory: /usr/local/freetds/conf/freetds
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: no
TDS version: 5.0
iODBC: no
unixodbc: no
SSPI "trusted" logins: no
Kerberos: no
$ssh -L 1433:db_server:1433 user at mid_server
This forward the local port 1433 to db_server. I tested. My ssh tunnel
works because I can connect to database using Navicat (localhost on
1433). I can telnet localhsot 1433.
------------------------------------------------------------------------------------
The information transmitted is intended only for the person or entity
to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this
information by person or entity other than the intended recipient is
not permitted. If you received this in error, please contact the
sender and delete the material from any computer.
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

James K. Lowden

2013-05-07 00:41:01 UTC

Permalink

On Mon, 06 May 2013 09:38:03 -0400

Post by Randy Syring
http://freetds.schemamania.org/userguide/logging.htm
to make sure you are connecting the way you think you are. The error
is essentially saying it can't find the server.

That's good advice. Just to clarify, though, the careful reader can
distinguish between "can't find the server" and "server didn't
answer".

If you try "man errno", you may find the OS error messages and their
associated numbers are lists. You saw,

Post by Randy Syring
OS error 61, "Connection refused"

$ man errno | sed -ne '/61 /,/^$/p'

61 ECONNREFUSED Connection refused. No connection could be
made because the target machine actively refused it. This usually
results from trying to connect to a service that is inactive on
the foreign host.

whereas a name lookup failure looks a little different:

$ tsql -H no-such-host -p 17
Error 20012 (severity 2):
Server name not found in configuration files.
...
Error 20013 (severity 2):
Unknown host machine name.
There was a problem connecting to the server

(There should be an OS error for that, too, but the error-handling logic
has no way to pass along the fact that the error status for
gethostbyname(3) is in herror, not errno.)

Post by Randy Syring
From this we know you are reaching a machine ("locahost" was resolved,

whew!) but that machine is not answering on the port. I would suggest
a simple TDSDUMP first, because it will tell you on one line the
address & port it's connecting to, and the TDS version.

--jkl

Krish

2013-05-07 04:45:48 UTC

Permalink

Thanks James

I think problem is port forwarding, ssh tunnel is not working from freeTDS.

The SQL Server is not in localhost, but it is in machine called
db_server. Access to db_server is restricted to mid_server. I created
tunnel with

$ssh -L 1433:db_server:1433 user at mid_server

The above port forwardign is working for Navicat. I can connect
localhost:1433 via Navicat and access all database in db_server. I
also can telnet localhost 1433. So main problem is freeTDS not working
port forwarding. Is there any way to get this work?

Post by James K. Lowden
On Mon, 06 May 2013 09:38:03 -0400

Post by Randy Syring
http://freetds.schemamania.org/userguide/logging.htm
to make sure you are connecting the way you think you are. The error
is essentially saying it can't find the server.

That's good advice. Just to clarify, though, the careful reader can
distinguish between "can't find the server" and "server didn't
answer".
If you try "man errno", you may find the OS error messages and their
associated numbers are lists. You saw,

Post by Randy Syring
OS error 61, "Connection refused"

$ man errno | sed -ne '/61 /,/^$/p'
61 ECONNREFUSED Connection refused. No connection could be
made because the target machine actively refused it. This usually
results from trying to connect to a service that is inactive on
the foreign host.
$ tsql -H no-such-host -p 17
Server name not found in configuration files.
...
Unknown host machine name.
There was a problem connecting to the server
(There should be an OS error for that, too, but the error-handling logic
has no way to pass along the fact that the error status for
gethostbyname(3) is in herror, not errno.)

Post by Randy Syring
From this we know you are reaching a machine ("locahost" was resolved,

whew!) but that machine is not answering on the port. I would suggest
a simple TDSDUMP first, because it will tell you on one line the
address & port it's connecting to, and the TDS version.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

--
Life is short where is time to hate eachother
http://www.krishnasunuwar.com.np

------------------------------------------------------------------------------------
The information transmitted is intended only for the person or entity
to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this
information by person or entity other than the intended recipient is
not permitted. If you received this in error, please contact the
sender and delete the material from any computer.

Krish

2013-05-07 04:48:26 UTC

Permalink

and my config is:

[sqlserv]
host = localhost
port = 1433
tds version = 7.0

Post by Krish
Thanks James
I think problem is port forwarding, ssh tunnel is not working from freeTDS.
The SQL Server is not in localhost, but it is in machine called
db_server. Access to db_server is restricted to mid_server. I created
tunnel with
$ssh -L 1433:db_server:1433 user at mid_server
The above port forwardign is working for Navicat. I can connect
localhost:1433 via Navicat and access all database in db_server. I
also can telnet localhost 1433. So main problem is freeTDS not working
port forwarding. Is there any way to get this work?

Post by James K. Lowden
On Mon, 06 May 2013 09:38:03 -0400

Post by Randy Syring
http://freetds.schemamania.org/userguide/logging.htm
to make sure you are connecting the way you think you are. The error
is essentially saying it can't find the server.

That's good advice. Just to clarify, though, the careful reader can
distinguish between "can't find the server" and "server didn't
answer".
If you try "man errno", you may find the OS error messages and their
associated numbers are lists. You saw,

Post by Randy Syring
OS error 61, "Connection refused"

$ man errno | sed -ne '/61 /,/^$/p'
61 ECONNREFUSED Connection refused. No connection could be
made because the target machine actively refused it. This usually
results from trying to connect to a service that is inactive on
the foreign host.
$ tsql -H no-such-host -p 17
Server name not found in configuration files.
...
Unknown host machine name.
There was a problem connecting to the server
(There should be an OS error for that, too, but the error-handling logic
has no way to pass along the fact that the error status for
gethostbyname(3) is in herror, not errno.)

Post by Randy Syring
From this we know you are reaching a machine ("locahost" was resolved,

--
Life is short where is time to hate eachother
http://www.krishnasunuwar.com.np
------------------------------------------------------------------------------------
The information transmitted is intended only for the person or entity
to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this
information by person or entity other than the intended recipient is
not permitted. If you received this in error, please contact the
sender and delete the material from any computer.

James K. Lowden

2013-05-08 04:15:02 UTC

Permalink

On Tue, 7 May 2013 10:30:48 +0545

Post by Krish
The SQL Server is not in localhost, but it is in machine called
db_server. Access to db_server is restricted to mid_server. I created
tunnel with
$ssh -L 1433:db_server:1433 user at mid_server
The above port forwardign is working for Navicat. I can connect
localhost:1433 via Navicat and access all database in db_server. I
also can telnet localhost 1433. So main problem is freeTDS not working
port forwarding. Is there any way to get this work?
[sqlserv]
host = localhost
port = 1433
tds version = 7.0

I don't know Navicat, but if that works, then FreeTDS should work,
too. Probably just a small configuration error.

Try:

$ TDSVER=7.0 tsql -H localhost -p 1433

That should work. If not, you'll have to get ssh to tell you what's
going on. You could also try tracing the Linux system calls. FreeTDS
just opens a TCP connection, nothing fancy, same as telnet.

If the above command works and "tsql -S" does not, then FreeTDS is not
looking at your freetds.conf file. To see why not, try:

$ TDSDUMPCONFIG=dump tsql -S sqlserv

and look at the "dump" file. Usually the output is clear enough to let
the reader diagnose the problem.

HTH.

--jkl

Krish

2013-05-08 04:50:17 UTC

Permalink

Thanks HTH,

I got it worked by setting version = 7.0 on freetds.conf under [global]

Krish

Post by James K. Lowden
On Tue, 7 May 2013 10:30:48 +0545

I don't know Navicat, but if that works, then FreeTDS should work,
too. Probably just a small configuration error.
$ TDSVER=7.0 tsql -H localhost -p 1433
That should work. If not, you'll have to get ssh to tell you what's
going on. You could also try tracing the Linux system calls. FreeTDS
just opens a TCP connection, nothing fancy, same as telnet.
If the above command works and "tsql -S" does not, then FreeTDS is not
$ TDSDUMPCONFIG=dump tsql -S sqlserv
and look at the "dump" file. Usually the output is clear enough to let
the reader diagnose the problem.
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds