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?