[freetds] tsql having a hard time connecting to a MS SQL server

Frediano Ziglio freddy77 at gmail.com
Fri Jun 6 11:46:30 EDT 2014


2014-06-05 15:12 GMT+01:00 Mark Lutgen <mlutgen at gmail.com>:

> I'm having an issue on a Red Hat 5 64 bit server but I'm running a nearly
> identical build on a Red Hat 4 32 bit. The error message is much like the
> ones many others have complained about over the years but I've not seen one
> matching this. Please allow me to show you below what happens:
>
> In Bash as root:
>
> export TDSDUMP=/var/log/freetds.log
> export TDSDUMPCONFIG=/var/log/freetds_config.log
>
> then I execute
>
> tsql –S “SQLSERVER\TEST2000” -U readonly -P dbreadonly
>
> It resolves the server name no problem but will not connect. The output is:
>
> locale is "en_US.UTF-8"
> locale charset is "UTF-8"
> using default charset "UTF-8"
> 16There was a problem connecting to the server
>
> /var/log/freetds.log shows this:
>
> log.c:196:Starting log file for FreeTDS 0.91
>         on 2014-06-05 02:41:23 with debug flags 0x4fff.
> iconv.c:330:tds_iconv_open(0x1eaea3b0, UTF-8)
> iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
> iconv.c:187:local name for UTF-8 is UTF-8
> iconv.c:187:local name for UCS-2LE is UCS-2LE
> iconv.c:187:local name for UCS-2BE is UCS-2BE
> iconv.c:349:setting up conversions for client charset "UTF-8"
> iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
> iconv.c:391:preparing iconv for "ISO-8859-1" <-> "ISO-8859-1" conversion
> iconv.c:394:tds_iconv_open: done
> net.c:934:tds7_get_instance_port(172.16.50.28, TEST2000)
> net.c:985:tds7_get_instance_port: timed out on try 0 of 16
> net.c:985:tds7_get_instance_port: timed out on try 1 of 16
> net.c:985:tds7_get_instance_port: timed out on try 2 of 16
> net.c:985:tds7_get_instance_port: timed out on try 3 of 16
> net.c:985:tds7_get_instance_port: timed out on try 4 of 16
> net.c:985:tds7_get_instance_port: timed out on try 5 of 16
> net.c:985:tds7_get_instance_port: timed out on try 6 of 16
> net.c:985:tds7_get_instance_port: timed out on try 7 of 16
> net.c:985:tds7_get_instance_port: timed out on try 8 of 16
> net.c:985:tds7_get_instance_port: timed out on try 9 of 16
> net.c:985:tds7_get_instance_port: timed out on try 10 of 16
> net.c:985:tds7_get_instance_port: timed out on try 11 of 16
> net.c:985:tds7_get_instance_port: timed out on try 12 of 16
> net.c:985:tds7_get_instance_port: timed out on try 13 of 16
> net.c:985:tds7_get_instance_port: timed out on try 14 of 16
> net.c:985:tds7_get_instance_port: timed out on try 15 of 16
> net.c:1057:instance port is 0
> login.c:436:invalid port number
> mem.c:615:tds_free_all_results()
>
> On the host running tsql commands, a tcpdump shows this:
>
>
> tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 96
> bytes
> 02:42:33.743293 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:34.740010 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:35.739934 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:36.739969 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:37.740032 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:38.746722 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:39.744165 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:40.744277 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:41.744298 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:42.744369 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:43.744599 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:44.744505 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:45.744542 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:46.744630 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:47.744805 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
> 02:42:48.744941 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto:
> UDP (17), length: 38) 172.16.45.36.56931 > 172.16.50.28.1434: UDP, length
> 10
>
>
1434 is the UDP discovery port. FreeTDS is trying to understand at which
port to connect to (it cannot connect to an UDP port!).


>
> The server is indeed listening on port 1434. From the redhat box running
> tsql, I see this:
> telnet 172.16.50.28 1434
> Trying 172.16.50.28...
> telnet: connect to address 172.16.50.28: Connection refused
> telnet: Unable to connect to remote host: Connection refused
>
> TCPdump shows this bidirectional communication:
>
> 03:14:20.909525 IP (tos 0x10, ttl  64, id 46922, offset 0, flags [DF],
> proto: TCP (6), length: 60) 172.16.45.36.43076 > 172.16.50.28.1434: S,
> cksum 0xf130 (correct), 3060033121:3060033121(0) win 5840 <mss
> 1460,sackOK,timestamp 3972911403 0,nop,wscale 7>
> 03:14:20.909660 IP (tos 0x0, ttl 127, id 32670, offset 0, flags [none],
> proto: TCP (6), length: 40) 172.16.50.28.1434 > 172.16.45.36.43076: R,
> cksum 0x2dca (correct), 0:0(0) ack 3060033122 win 0
>
> Now, if I export TDSPORT=1434
>
> I get:
>
> 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 111, "Connection refused"
> There was a problem connecting to the server
>
>
Obviously, it's UDP, telnet (and mssql main connection) run on TCP.


> If I export TDSPORT=1298
>
> I can connect to the MS SQL server:
>
> locale is "en_US.UTF-8"
> locale charset is "UTF-8"
> using default charset "UTF-8"
> Msg 4062 (severity 11, state 1) from SQLMN201\TEST2000:
>         "Cannot open user default database. Using master database instead."
>
>
Good, try with

tsql -H SQLSERVER -p 1298 -U readonly -P dbreadonly

(-H is host, -p is port)


>
> output from tsql -C is:
>
> Compile-time settings (established with the "configure" script)
>                             Version: freetds v0.91
>              freetds.conf directory: /usr/local/etc
>      MS db-lib source compatibility: yes
>         Sybase binary compatibility: yes
>                       Thread safety: yes
>                       iconv library: yes
>                         TDS version: 4.2
>                               iODBC: no
>                            unixodbc: yes
>               SSPI "trusted" logins: no
>                            Kerberos: no
>
>
>
> Does anyone have any insight to this issue?  The same build works on a 32
> bit redhat 4 server.
>
> Mark
>

Probably on the other machine you have some settings on freetds.conf for
SQLSERVER. TDSDUMPCONFIG and TDSDUMP are your friend.
Note that if you plan to use just mssql using protocol 4.2 is REALLY a bad
idea!

Frediano


More information about the FreeTDS mailing list