Connect through ssh gateway

[maksimf]

• I'm on Mac OS X
• Using FreeTDS v1.00.26 and tiny_tds v1.1.0

I'm trying to connect to SQL Server using tiny_tds using ssh gateway like this:

require 'tiny_tds'
require 'net/ssh/gateway'

gateway = Net::SSH::Gateway.new(
  'host',
  'username',
  port: 'port',
  password: 'password'
)
port = gateway.open('127.0.0.1', 1433, 1234) # SQL Server uses 1433 port

db_client = TinyTds::Client.new(
  host: '127.0.0.1',
  username: 'username',
  password: 'password',
  port: 1234,
  database: 'database'
) # => TinyTds::Error: Adaptive Server connection timed out (127.0.0.1:1234)

As you can see, the connection times out. But, if I try to SSH to the Windows machine from the console and then connect to the SQL Server using sqlcmd - everything works just fine.

[metaskills]

Never seen this attempted before. Have you tried using FreeTDS' tsql utility to debug the connection first?

[coderjoe]

Agreed with the above. I'd do the following things:

1. Remove TinyTds from the picture and try testing the Net::SSH::Gateway via sqlcmd or the FreeTDS tsql command.
2. Remove Net::SSH::Gateway from the picture by manually configuring the gateway yourself using SSH, then test the resulting connection via both tsql and TinyTds

Both of these tests should be presumed on your local machine, not while SSH'd into the remote machine. Presuming it only uses that one port to communicate I can't think of a reason why this shouldn't work. Good luck and let us know how it turns out!

[maksimf]

Thank you for the heads up! I've tried the following:

1. Set up SSH gateway in ruby through Net::SSH::Gateway, then connect to SQL Server via CLI - it worked
2. Set up SSH gateway via ssh -L and connect via both TinyTds and CLI - it worked as well

I'm a bit confused what may prevent TinyTds from working via Net::SSH::Gateway then

[maksimf]

One more thing: in my app I also use the same gateway to connect to the remote Mysql server, which works fine

[metaskills]

What is the output of tsql -C, just curious.

[maksimf]

                            Version: freetds v1.00.26
             freetds.conf directory: /usr/local/Cellar/freetds/1.00.26/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

Also tried on Ubuntu with:

                            Version: freetds v0.91
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

[metaskills]

The second one looks like a problem. That version is too and I do not see OpenSSL in there.

[maksimf]

First one is fine, though? It won't work as well

[metaskills]

Right... but we have distilled that the issue is specifically TinyTds with a Net::SSH::Gateway and that tsql works just fine with it? Just making sure.

[maksimf]

Yep, that is correct

[metaskills]

So Odd! I wonder if using a freetds.conf with the encrypted option would help signal to DBLIB in TinyTDS. Maybe there is something in Ruby's implementation that is lacking too? Just guessing because there is nothing in the protocol that we have control over on this. No configs that I know of in DBLIB that make control that connection handshake.

[coderjoe]

I agree, very odd!

My understanding was that ssh gateways act as a transparent VPN of sorts through a machine running sshd and that the client using the gateway can be completely ignorant of the fact that it's using ssh or any form of encryption as a gateway.

If the gateway is set up using ssh port forwarding I would expect it to work in both cases since the port forward worked with both tsql and TinyTds. 😕

[zetetic]

Ran into the same problem today, virtually the same setup on macOS. freetds 1.00.27, TinyTds 1.0.5, TDS 7.3. Not much else to add. Certainly seems like something going on in TinyTds/freetds.

[zetetic]

Did a bit more digging -- turns out FreeTDS will log if you set an environment variable (per http://www.freetds.org/userguide/logging.htm). Here is a sample session:

log.c:167:Starting log file for FreeTDS 1.00.27
	on 2017-04-27 11:30:44 with debug flags 0x4fff.
dblib.c:1175:tdsdbopen(0x7ff871e020e0, localhost:1433, [microsoft])
dblib.c:1201:tdsdbopen: dbproc->dbopts = 0x7ff871e05520
dblib.c:1212:tdsdbopen: tds_set_server(0x7ff871e32ae0, "localhost:1433")
dblib.c:256:dblib_get_tds_ctx(void)
dblib.c:1229:tdsdbopen: About to call tds_read_config_info...
config.c:168:Getting connection information for [localhost:1433].
config.c:172:Attempting to read conf files.
config.c:358:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:371:... $FREETDS not set.  Trying $HOME.
config.c:297:Could not open '/Users/thead/.freetds.conf' ((.freetds.conf)).
config.c:301:Found conf file '/usr/local/Cellar/freetds/1.00.27/etc/freetds.conf' (default).
config.c:509:Looking for section global.
config.c:568:	Found section global.
config.c:571:Got a match.
config.c:594:	tds version = '7.0'
config.c:924:Setting tds version to 7.0 (0x700).
config.c:594:	text size = '64512'
config.c:568:	Found section egserver50.
config.c:568:	Found section egserver70.
config.c:582:	Reached EOF
config.c:509:Looking for section localhost:1433.
config.c:568:	Found section global.
config.c:568:	Found section egserver50.
config.c:568:	Found section egserver70.
config.c:582:	Reached EOF
config.c:307:[localhost:1433] not found.
config.c:358:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:371:... $FREETDS not set.  Trying $HOME.
config.c:297:Could not open '/Users/thead/.freetds.conf' ((.freetds.conf)).
config.c:301:Found conf file '/usr/local/Cellar/freetds/1.00.27/etc/freetds.conf' (default).
config.c:509:Looking for section global.
config.c:568:	Found section global.
config.c:571:Got a match.
config.c:594:	tds version = '7.0'
config.c:924:Setting tds version to 7.0 (0x700).
config.c:594:	text size = '64512'
config.c:568:	Found section egserver50.
config.c:568:	Found section egserver70.
config.c:582:	Reached EOF
config.c:509:Looking for section localhost.
config.c:568:	Found section global.
config.c:568:	Found section egserver50.
config.c:568:	Found section egserver70.
config.c:582:	Reached EOF
config.c:307:[localhost] not found.
config.c:802:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
config.c:712:tds_config_login: client_charset is UTF-8.
config.c:802:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
dblib.c:1256:tdsdbopen: Calling tds_connect_and_login(0x7ff871e31f60, 0x7ff871e0e920)
iconv.c:325:tds_iconv_open(0x7ff871e31f60, UTF-8)
iconv.c:185:local name for ISO-8859-1 is ISO-8859-1
iconv.c:185:local name for UTF-8 is UTF-8
iconv.c:185:local name for UCS-2LE is UCS-2LE
iconv.c:185:local name for UCS-2BE is UCS-2BE
iconv.c:343:setting up conversions for client charset "UTF-8"
iconv.c:345:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:384:tds_iconv_open: done
net.c:216:Connecting to ::1 port 1433 (TDS version 7.3)
net.c:242:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:277:getsockopt(2) reported: Connection refused
net.c:216:Connecting to 127.0.0.1 port 1433 (TDS version 7.3)
net.c:242:tds_open_socket: connect(2) returned "Invalid argument"
net.c:216:Connecting to fe80::1%lo0 port 1433 (TDS version 7.3)
net.c:242:tds_open_socket: connect(2) returned "Invalid argument"
util.c:165:Changed query state from IDLE to DEAD
net.c:338:tds_open_socket() failed
net.c:216:Connecting to 127.0.0.1 port 1433 (TDS version 7.3)
net.c:242:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:343:tds_open_socket() succeeded
packet.c:741: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-00 4d 53 53 51 4c 53 65 |.ˇ...... .MSSQLSe|
0030 72 76 65 72 00 c6 8b 00-00 00                   |rver.∆.. ..|

util.c:322:tdserror(0x7ff871e3dcc0, 0x7ff871e31f60, 20003, 36)
dblib.c:7964:dbperror(0x7ff871e02ad0, 20003, 36)
dblib.c:8032:dbperror: Calling dblib_err_handler with msgno = 20003; msg->msgtext = "Adaptive Server connection timed out (localhost:1433)"
dblib.c:5792:dbgetuserdata(0x7ff871e02ad0)
dblib.c:5792:dbgetuserdata(0x7ff871e02ad0)
dblib.c:4892:dbdead(0x7ff871e02ad0) [alive]
dblib.c:749:dbloginfree(0x7ff871e020e0)

This occurs when attempting to connect via a jump box:

require 'tiny_tds'
require 'net/ssh/gateway'

gateway = Net::SSH::Gateway.new(
  'jump_box',
  'jump_user',
  password: 'jump_pass'
)

gateway.open('db_host', 1433, 1433) do |port|
  puts "Gateway open, trying connection to port #{port}"
  TinyTds::Client.new(
    username: 'db_user',
    password: 'db_pass',
    login_timeout: 60,
    database: 'db_name',
    host: 'localhost',
    port: port
  )
end

[itsfocus]

I'm having the same issue with tiny tds 0.6, 1.3 and 2.0 running Ubuntu 14.04 with FreeTDS 1.00.21. I'm able to connect with tiny tds if I forward the ports through ssh in the terminal but not programmatically with Net::SSH::Gateway. My code is essentially identical to zetetics.

Has anyone found a solution to this?

[zetetic]

@itsfocus I was never able to figure this out. I suspect the issue may be in FreeTDS instead of tiny_tds, but I don't know enough about SQL Server to go down that road with any confidence. Maybe it assumes that any reference to a port on localhost means that it is trying to reach a local server somehow? and ignores the forwarding? Might help to reach out to FreeTDS as well.

[maksimf]

Same her, never figured it out

[coderjoe]

I decided to do my own test following these steps:

1. I created 3 droplets on DigitalOcean named freetds, gateway, and sqlserver
2. I installed SQL server on the sqlserver machine listening on port 1433
3. I set up SSH on all 3 nodes with a common user and password
4. I installed FreeTDS on the freetds node

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

5. I installed ruby and tiny_tds-2.1.0.pre1 on freetds

I tested using the following 2 scripts to generate SSH Gateways:

require 'net/ssh/gateway'

gateway = Net::SSH::Gateway.new(
        '45.55.184.27',
        'coderjoe',
        password: 'coderjoetest'
)

gateway.open('10.132.11.218', 1433, 1433) do |port|
        puts "Gateway open, try connecting via localhost:#{port}"

        while true do; end
end

require 'net/ssh/gateway'
require 'tiny_tds'

gateway = Net::SSH::Gateway.new(
        '45.55.184.27',
        'coderjoe',
        password: 'coderjoetest'
)

gateway.open('10.132.11.218', 1433, 1433) do |port|
        puts "Gateway open, try connecting via localhost:#{port}"

        puts "Connecting with TinyTDS"
        client = TinyTds::Client.new(
                username: 'sa',
                password: '0c1af7866b544a6385e9bfa6$',
                login_timeout: 60,
                host: '127.0.0.1',
                port: port
        )

        puts "Running SQL..."

        result = client.execute("SELECT 'test completed through gateway!'")

        puts "Checking results.."
        result.each do |row|
                puts "got row: #{row.to_s}"
        end
end

When using the gateway (without tiny_tds) I can connect just fine as reported above. When using the gateway with tiny_tds I can not connect. I was unable to test with tiny_tds's version of tsql since it currently produces an error.

More tests will come later but it seems to point to some problem with tiny_tds right now.

[metaskills]

Can we test this more with 2.1.0.pre2?

[zetetic]

freetds: stable 1.00.91 (bottled)

tiny_tds: 2.1.2

Issue same as before ... connection is opened through the gateway but the initial request times out:

[snip]
net.c:226:Connecting to 127.0.0.1 port 40777 (TDS version 7.3)
net.c:252:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:372:tds_open_socket() succeeded
packet.c:742: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-00 4d 53 53 51 4c 53 65 |.<FF>...... .MSSQLSe|
0030 72 76 65 72 00 fb 5c 01-00 00                   |rver.<FB>\. ..|

util.c:322:tdserror(0x7fb424872280, 0x7fb42487e020, 20003, 36)
dblib.c:7980:dbperror(0x7fb4248793c0, 20003, 36)
dblib.c:8048:dbperror: Calling dblib_err_handler with msgno = 20003; msg->msgtext = "Adaptive Server connection timed out (127.0.0.1:40777)"
dblib.c:5808:dbgetuserdata(0x7fb4248793c0)

[violen]

Hey guys I've got the same error...

Environment

Windows with WSL (ubuntu xenial) ruby: 2.4.1 tiny_tds_gem: 2.1.2

MS SQL Server 2016

tsql -C output

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.00.94
             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

FreeTDS Log:

net.c:226:Connecting to 127.0.0.1 port 1433 (TDS version 7.3)
net.c:252:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:372:tds_open_socket() succeeded
packet.c:742: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-00 4d 53 53 51 4c 53 65 |........ .MSSQLSe|
0030 72 76 65 72 00 73 33 00-00 00                   |rver.s3. ..|

util.c:322:tdserror(0x677baa0, 0x60250e0, 20003, 115)
dblib.c:7982:dbperror(0x68d2850, 20003, 115)
dblib.c:8050:dbperror: Calling dblib_err_handler with msgno = 20003; msg->msgtext = "Adaptive Server connection timed out (127.0.0.1:1433)"
dblib.c:5810:dbgetuserdata(0x68d2850)
dblib.c:5810:dbgetuserdata(0x68d2850)
dblib.c:4910:dbdead(0x68d2850) [alive]

When opening a SSH Gateway via 'net-ssh-gateway' gem. I can connect via tsql, SSMS and other DB Tools like HeidiSQL

Now hopefully a hint to solve this issue... When opening the gateway via putty or ssh -L even TinyTDS can connect. There seems a problem TinyTds works with SSH tunneling. It differs when tunneling via Ruby Net::SSH or using the more native stuff somehow.

Ruby Code

require 'net/ssh/gateway'
require 'tiny_tds'

ssh_configs = ['HOST', 'USER']
ssh_configs << { passphrase: ENV['MY_SSH_PASSPHRASE'] }
gateway = Net::SSH::Gateway.new(*ssh_configs)

p = gateway.open('sql_host', 1433, 1433)
sql_client = TinyTds::Client.new(username: 'SQL_USER', password: 'SQL_PW', host: '127.0.0.1', port: p)

[violen]

TL;DR my Solution for now...

open a Tunnel via CLI out of ruby:

system("`which ssh` -i #{ident_file} -f -N -L 1433:#{SQL_HOST}:1433 #{user}@#{tunnelhost}")

and opening the client as intendet:

client = TinyTds::Client.new(username: 'SQL_USER', password: 'SQL_PW', host: '127.0.0.1', port: '1433')

to discard the tunnel you need to get the process ID from that command:

%x(kill -kill `pidof $(which ssh)`)

done ;)

Hope this helps everyone who is encountering this Problem until it gets fixed.

[jwood74]

Excited to find this thread, but then I saw that a solution hasn't been found yet.

violen's solution seems to sometimes work, but not reliably for what I'm after.

Still trying to investigate some options to query a remote SQL Server, through ssh tunnel.