TinyTds::Error: Adaptive Server connection failed

[chinmay1219]

I am new to ruby on rails and I need to use MS SQL database that uses ntlmv2 authentication.
When I try to connect to database using tsql it works fine.
Below are the config

[vagrant@localhost ~]$ tsql -C
[TinyTds][v2.1.0.pre1][tsql]: /usr/bin/tsql
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.95.81
             freetds.conf directory: /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: yes
                            OpenSSL: no
                             GnuTLS: yes

freetds.config file

[global]
        # TDS protocol version
        tds version = 7.3

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
        dump file = /tmp/freetds.log
        debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 64512

[sqlhost]
        host = sqlhost.corp.xyz
        port = 50000
        tds version = 7.1
        use ntlmv2 = yes

When I try to connect using below command it works fine

[vagrant@localhost ~]$ tsql -H sqlhost -U org\\serviceaccount -P mypassword -p 50000
[TinyTds][v2.1.0.pre1][tsql]: /usr/bin/tsql
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

But when I am using rails application or irb I get below error

[vagrant@localhost ~]$ irb
2.3.0 :001 > require 'tiny_tds'
 => true 
2.3.0 :002 > client = TinyTds::Client.new(username: 'VISA\\serviceaccount', password: 'password', host: 'myhost', database: 'Data_Lake_Eagle', port: 50000)
/home/vagrant/.rvm/gems/ruby-2.3.0/gems/tiny_tds-2.1.0.pre1/lib/tiny_tds/client.rb:53: warning: TinyTds: :use_utf16 option not supported in this version of FreeTDS.

TinyTds::Error: Adaptive Server connection failed (myhost:50000)
	from /home/vagrant/.rvm/gems/ruby-2.3.0/gems/tiny_tds-2.1.0.pre1/lib/tiny_tds/client.rb:53:in `connect'
	from /home/vagrant/.rvm/gems/ruby-2.3.0/gems/tiny_tds-2.1.0.pre1/lib/tiny_tds/client.rb:53:in `initialize'
	from (irb):2:in `new'
	from (irb):2
	from /home/vagrant/.rvm/rubies/ruby-2.3.0/bin/irb:11:in `<main>'
2.3.0 :003 >

Below is the log output

17:01:13.718217 8121 (log.c:167):Starting log file for FreeTDS 0.95.75
        on 2017-09-06 17:01:13 with debug flags 0xffff.
17:01:13.718243 8121 (dblib.c:1237):tdsdbopen: Calling tds_connect_and_login(0x2929630, 0x2921950)
17:01:13.718253 8121 (iconv.c:328):tds_iconv_open(0x2929630, UTF-8)
17:01:13.718310 8121 (iconv.c:187):local name for ISO-8859-1 is ISO-8859-1
17:01:13.718312 8121 (iconv.c:187):local name for UTF-8 is UTF-8
17:01:13.718314 8121 (iconv.c:187):local name for UCS-2LE is UCS-2LE
17:01:13.718315 8121 (iconv.c:187):local name for UCS-2BE is UCS-2BE
17:01:13.718316 8121 (iconv.c:346):setting up conversions for client charset "UTF-8"
17:01:13.718318 8121 (iconv.c:348):preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
17:01:13.718328 8121 (iconv.c:395):preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
17:01:13.718334 8121 (iconv.c:400):tds_iconv_open: done
17:01:13.718340 8121 (net.c:202):Connecting to 10.10.211.108 port 50000 (TDS version 7.3)
17:01:13.718575 8121 (net.c:275):tds_open_socket: connect(2) returned "Operation now in progress"
17:01:13.794413 8121 (net.c:314):tds_open_socket() succeeded
17:01:13.794456 8121 (packet.c:740):Sending packet
0000 12 01 00 3a 00 00 00 00-00 00 1a 00 06 01 00 20 |...:.... ....... |
0010 00 01 02 00 21 00 0c 03-00 2d 00 04 04 00 31 00 |....!... .-....1.|
0020 01 ff 09 00 00 00 00 00-02 4d 53 53 51 4c 53 65 |........ .MSSQLSe|
0030 72 76 65 72 00 b9 1f 00-00 00                   |rver.... ..|

17:01:13.869764 8121 (packet.c:639):Received packet
0000 04 01 00 2b 00 00 01 00-00 00 1a 00 06 01 00 20 |...+.... ....... |
0010 00 01 02 00 21 00 01 03-00 22 00 00 04 00 22 00 |....!... ."....".|
0020 01 ff 0c 00 15 9c 00 00-03 00 00                |........ ...|

17:01:13.869794 8121 (login.c:1106):detected flag 3
17:01:13.869796 8121 (login.c:472):login packet rejected
17:01:13.869798 8121 (query.c:3772):tds_disconnect()
17:01:13.869853 8121 (util.c:165):Changed query state from IDLE to DEAD
17:01:13.869874 8121 (util.c:322):tdserror(0x283c660, 0x2929630, 20002, 0)
17:01:13.869880 8121 (dblib.c:7925):dbperror(0x2924910, 20002, 0)
17:01:13.869898 8121 (dblib.c:7993):dbperror: Calling dblib_err_handler with msgno = 20002; msg->msgtext = "Adaptive Server connection failed (myhost:50000)"
17:01:13.869904 8121 (dblib.c:5771):dbgetuserdata(0x2924910)
17:01:13.869920 8121 (dblib.c:5771):dbgetuserdata(0x2924910)
17:02:45.779932 8121 (dblib.c:743):dbloginfree(0x29072e0)

I tried to see login.c and after detected flag 3 it comes out with login packet rejected that's where it gets messy. Does tinytds require openSSL? I have gnuTLS with freetds version

I already tried to upgrade freetds version to v1.0 but it fails with tsql command itself.

Please help to solve this issue.

[chinmay1219]

Update:
After further troubleshooting I came to know that if I connect using dummy credentials on irb for example org\xyz on two different servers say host1 and host2
Then host1 would throw error adaptive server connection failed
and host2 would throw error login from untrusted domain (Logically makes sense because I am entering wrong credentials)

And host1 is implementing TLS1.2 and birthday attack security whereas host2 does not have any of these configured.
So I suspect that TinyTds is unable to work with TLS 1.2

[coderjoe]

@metaskills correct me if I'm wrong, but I believe all TLS/SSL is offloaded to the FreeTDS library which implements using either GnuTLS or OpenSSL, not implemented in tiny_tds, right? TLS support sould be dependent on the build of FreeTDS being used correct?

[coderjoe]

@chinmay1219 I'm not sure this is relevant, but apparently it's possible to adjust the supported openssl ciphers in the freetds.conf

https://github.com/FreeTDS/freetds/blob/5091fe4a98359011919959eb48791a6b9f02591d/freetds.conf#L30

If it's really a SSL cipher suite problem, maybe it's possible to either use openssl and configure a supported cipher suite? I wasn't able to find similar configuration for GnuTLS.

I wish I could be more help.

[metaskills]

Yes, we do nothing special with encrypted connections it all comes down to how FreeTDS handles it. Always try to use the latest version too. If tsql did not work on the latest version, we don't stand a chance either.

[chinmay1219]

@coderjoe @metaskills I am using GnuTLS.
When I use tsql -H sqlhost -U org\\serviceaccount -P mypassword -p 50000
on v95.81 it works perfectly fine and on the same version when I try to connect using tinyTDS it fails.

[metaskills]

Can you work at getting 1.0 FreeTDS's tsql working with your needs? Maybe that will open up doors to understanding what is happening with it and maybe TinyTDS?

[chinmay1219]

Thanks for your reply.
Yeah I did that but then tsql command itself start throwing unexpected EOF from server.
Maybe I will get v1.00 working and then see if it solves my issue.
I am sure that it's possible to connect to this database with the these server and db profile. Because I had this working 2 weeks back and accidentally I deleted Vagrant machine and all setup gone and I did not document anything as what changes I made. :(