TinyTds::Error: Adaptive Server connection failed - but works via tsql directly

[alexbevi]

Environment

Operating System

$ lsb_release -a; uname -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.3 LTS
Release:        22.04
Codename:       jammy
Linux vrt-svr-jobman 5.15.0-83-generic #92-Ubuntu SMP Mon Aug 14 09:30:42 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

TinyTDS Version and Information

$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.00.21
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: no

Description

I'm migrating a legacy application that uses tiny_tds to a newer machine (was Ubuntu 12.04), but I'm having issues getting the Rails 4.1 application to connect to MSSQL via TinyTds.

$ echo -e 'select @@version\nGO' | tsql -H 192.168.7.45 -p 1433 -U sa -P xxxxx
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> 2>
Microsoft SQL Server 2005 - 9.00.4060.00 (Intel X86)
        Mar 17 2011 13:20:38
        Copyright (c) 1988-2005 Microsoft Corporation
        Workgroup Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

(1 row affected)

From this machine, if I query the version via tsql I'm able to connect and get a response, but using the same information via TinyTds fails:

[1] pry(main)> TinyTds::VERSION
=> "2.1.5"
[2] pry(main)> client = TinyTds::Client.new username: 'sa', password: 'xxxxx', host: '192.168.7.45', port: 1433
TinyTds::Error: Adaptive Server connection failed (192.168.7.45:1433)
from /usr/share/rvm/gems/ruby-2.3.8/bundler/gems/tiny_tds-23ed1e41075c/lib/tiny_tds/client.rb:60:in `connect'

[daveomcd]

I'm getting this same issue out of the blue. @alexbevi were you able to find a solution?

[alexbevi]

I'm getting this same issue out of the blue. @alexbevi were you able to find a solution?

@daveomcd I have not unfortunately, so still waiting to hear back.

[daveomcd]

@alexbevi so this worked for me. In my freetds.conf file I had to change my version from 7.3 to 7.0. Hopefully that helps you.

[andyundso]

Could be that tsql knows or negotiates the possible TDS protocol version, while tiny_tds does not do that.

according to this page from Microsoft, SQL server 2005 supports TDS v7.2. v7.3 is apparently not supported.

another option to adapting the version in the freetds.conf file would be to compile freetds with a certain version, as described in our install guide.

[andyundso]

I'll close this issue, since @daveomcd provided a solution (manually setting the TDS version to v7.0).

If future readers stumble upon this issue, please note that tiny_tds v3 dropped support for SQL Server < 2017 and therefore also TDS v7.2 and below. If you happen to have the need to connect to a very old SQL server, please use tiny_tds v2.