Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: mark AT lutgen.net, FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] tsql having a hard time connecting to a MS SQL server
  • Date: Fri, 6 Jun 2014 16:46:30 +0100

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




Archive powered by MHonArc 2.6.24.

Top of Page