FreeTDS connection problems

Question

I'm using FreeTDS 0.91 to connect to a remote MSSQL server but all attempts have borne no fruit thus far.

Upon doing a tsql command on my Unix, I get the following error:

  locale is "en_US.UTF-8"
  locale charset is "UTF-8"
  using default charset "UTF-8"
  Error 20004 (severity 9)
  Read from the server failed
  OS error 104, "Connection reset by peer"
  Error 20002 (severity 9)
  Adaptive Server connection failed
  There was a problem connecting to the server

The freetds.log has the following in it:

16:05:49.144905 20207 (log.c:196):Starting log file for FreeTDS 0.91
  on 2011-09-28 16:05:49 with debug flags 0xffff.
  16:05:49.145281 20207 (iconv.c:330):tds_iconv_open(0x3d29320, UTF-8)
  16:05:49.145426 20207 (iconv.c:187):local name for ISO-8859-1 is ISO-8859-1
  16:05:49.145448 20207 (iconv.c:187):local name for UTF-8 is UTF-8
  16:05:49.145459 20207 (iconv.c:187):local name for UCS-2LE is UCS-2LE
  16:05:49.145469 20207 (iconv.c:187):local name for UCS-2BE is UCS-2BE
  16:05:49.145479 20207 (iconv.c:349):setting up conversions for client charset "UTF-8"
  16:05:49.145489 20207 (iconv.c:351):preparing iconv for "UTF-8" "UCS-2LE" conversion
  16:05:49.145508 20207 (iconv.c:391):preparing iconv for "ISO-8859-1" "UCS-2LE" conversion
  16:05:49.145533 20207 (iconv.c:394):tds_iconv_open: done
  16:05:49.145550 20207 (net.c:205):Connecting to 70.70.218.13 port 2600 (TDS version 7.0)
  16:05:49.145686 20207 (net.c:270):tds_open_socket: connect(2) returned "Operation now in progress"
  16:05:49.236008 20207 (net.c:310):tds_open_socket() succeeded
  16:05:49.236061 20207 (util.c:156):Changed query state from DEAD to IDLE
  16:05:49.236083 20207 (login.c:782):quietly sending TDS 7+ login packet
  16:05:49.236173 20207 (token.c:328):tds_process_login_tokens()
  16:05:49.857930 20207 (util.c:331):tdserror(0x3d29080, 0x3d29320, 20004, 104)
  16:05:49.858072 20207 (util.c:361):tdserror: client library returned TDS_INT_CANCEL(2)
  16:05:49.858090 20207 (util.c:384):tdserror: returning TDS_INT_CANCEL(2)
  16:05:49.858114 20207 (util.c:156):Changed query state from IDLE to DEAD
  16:05:49.858137 20207 (token.c:337):looking for login token, got  0()
  16:05:49.858155 20207 (token.c:122):tds_process_default_tokens() marker is 0()
  16:05:49.858168 20207 (token.c:125):leaving tds_process_default_tokens() connection dead
  16:05:49.858179 20207 (login.c:466):login packet accepted
  16:05:49.858189 20207 (util.c:331):tdserror(0x3d29080, 0x3d29320, 20002, 0)
  16:05:49.858209 20207 (util.c:361):tdserror: client library returned TDS_INT_CANCEL(2)
  16:05:49.858220 20207 (util.c:384):tdserror: returning TDS_INT_CANCEL(2)
  16:05:49.858232 20207 (mem.c:615):tds_free_all_results()

Does this at all seem like it could be an issue with the SQL server, or am I installing or have configured FreeTDS wrong?

So lost. Please help.

When I connect to the server via telnet, this is the response I get back:

Trying 70.70.218.13...
Connected to 70.70.218.13.
Escape character is '^]'.
^^
Connection closed by foreign host.

Would this mean that the telnet connection to the host was successful?

The SQL database is run on a windows server. Would connecting to the Windows server (just like you would through remote desktop) be the right sort of values to use for the hostname and port? Or would I have to find a direct hostname to the SQL server on the windows server?

Can you post SQL Server logs? Can you connect to the port with telnet from local machine? — Igor Borisenko, Sep 29 '11 at 00:37
I notice it's using TDS version 7.0. I have had in the past to set an environment variable `TDSVER=8.0` to get a successful connection — Michael Berkowski, Sep 29 '11 at 02:03
can you post your host config from the FreeTDS configuration file? — JamesHalsall, Oct 19 '11 at 21:06

fangzhzh · Answer 1 · 2014-04-30T08:28:59.800

24

Khan.

I just encountered this same situation. And I resolved this problem by adding a .freetds.conf to my home,

[global]
    # TDS protocol version
    tds version = 7.0

And I know this is exactly the answer to most FreeTDS connect problems.

And this method is also provided by @Michael Berkowski.

edited Apr 30 '14 at 08:28

answered Jan 18 '13 at 06:37

fangzhzh

2,172
21
25

Thank you. Worked for me too – Pentux May 23 '17 at 11:59

score 2 · Answer 2 · answered Jul 25 '16 at 23:48

With apologies for the five year necromancy, it appears that this error will also occur when tsql successfully connects to an open port, but the listener is some service other than SQL Server. It seems FreeTDS does not try to check if it's talking to a service other than SQL Server on login, so the error messages are cryptic. (In my case, I was using the port number for Remote Desktop.)

isapir · Answer 3 · 2017-11-07T19:50:17.347

If you don't want to create .freetds.conf you can simply set the TDSVER Environment variable instead (e.g. on Windows to connect to SQL Server 2008 use TDS version 7.3):

set TDSVER=7.3

(for *nix use export).

See http://www.freetds.org/userguide/envvar.htm for more environment variables.

See http://www.freetds.org/userguide/choosingtdsprotocol.htm for TDS versions of different RDBMSs

You can also specify a path for a log file which can provide more information by setting the environment variable TDSDUMP, which will help troubleshoot problems and show you where config files are looked for. e.g.

set TDSDUMP=C:\temp\freetds.log

In my case setting the TDSDUMP variable revealed an error in the logs:

iconv.c:346:setting up conversions for client charset "CP1252;LC_CTYPE=English_United States.1252;LC_MONETARY=C;LC_NUMERIC=C;LC_TIME=C"
iconv.c:348:preparing iconv for "CP1252;LC_CTYPE=English_United States.1252;LC_MONETARY=C;LC_NUMERIC=C;LC_TIME=C" <-> "UCS-2LE" conversion
iconv.c:423:tds_iconv_info_init: client charset name "-1" invalid

Apparently FreeTDS didn't parse the charset name from the string "CP1252;LC_CTYPE=English_United States.1252;LC_MONETARY=C;LC_NUMERIC=C;LC_TIME=C"

I created a .freetds.conf file with the following contents:

[global]
    tds version = 7.3           # version 7.3 for MSSQL 2008
    client charset = UTF-8      # so that we can get unicode characters

And saved it to C:\Windows\System32\config\systemprofile\AppData\Roaming\ as suggested by the log file, which fixed the problem for me.

FreeTDS connection problems

3 Answers3