Create Local Ssh Tunnel keyword gives no info about the success/failure of the Keyword

[aimeejtix]

I am working with RobotFramework and am trying to use this Create Local Ssh Tunnel keyword to open a connection to a MS SQL-Server DB on a remote host so I can run further DB commands.

So far if I create the tunnel myself using the following command from the command line:
ssh -fN -L 11433:10.x.x.x:1433 user1@40.x.x.x
And then run a small python program as follows:

import pymssql

conn = pymssql.connect(database='test_db', user='user', password='paswd1', host='localhost', port=11433)
cursor = conn.cursor()
cursor.execute('SELECT Name FROM Programs WHERE Id=4143')

for row in cursor:
    print('row = %r' % (row,))

This all works fine and I get output.

When I try to do the same from within RF like so:

Test: Open SSH Tunnel And Connect To DB
	[Tags]  SSH
	[Timeout]  10
	${index}=  Open Connection  40.x.x.x
	Log  ${index}
	${output}=  Login  myuser  mypaswd1
	Log  ${output}
	${tunnelOutput}=  Create Local Ssh Tunnel  11433  10.x.x.x  1433
	Log  ${tunnelOutput}
	${conn}=  Connect To Database  pymssql  'test_db'  'user1'  'paswd1'  'localhost'  11433
	Log  ${conn}
	Close All Connections

There is no output from the Create Local Ssh Tunnel keyword and I also get the following error when trying to connect to the DB:

INFO: Connecting using : pymssql.connect(database='test_db', user='user1', password='paswd1', host='localhost', port=11433) 
FAIL: InterfaceError: Connection to the database failed for an unknown reason.

Is there a way to tell whether the tunnel was created successfully?

[mihaiparvu]

There is no built-in way to tell if the tunnel was created sucessfully. But you can use netstat to see if there is a connection listening on port 11433.

[aimeejtix]

Thanks for the reply, I did indeed figure out that the tunnel was created successfully!
Took a few different ways to verify for definite.

I tried sabotaging the use of the keyword by giving in invalid host too which also returned a success response, I would have expected a fail if the host could not be found at least. It would be good to have some sort of check (whether it's another keyword or part of the Create Local Ssh Tunnel keyword. Just a suggestion going forward! :)

[mihaiparvu]

You're rasing a valid point. I will leave this issue open and implement some sort of keyword log in a future release.

[rkumarrb]

In my implementation of robot framework, I have a library called sshtunnel which has few keywords like start_ssh_tunnel, stop_sshtunnel with robot ssh libraries connection pool like implementation as well.

this library is not integrated with ssh library as it can be used with ssh, rest, db, web automation too. If interested, let me know.

[rkumarrb]

I have created package and uploaded to pypi. Link to Github - https://github.com/C-Squad/robotframework-sshtunnellibrary

[mihaiparvu]

Great job and thank you for open-sourcing your work! Your library has some useful keywords that, I think, can be also integrated into SSHLibrary as well!

[rkumarrb]

Thank you. Feel free to take all or any part of the code to integrate.

[fatgouchang]

Hi，rkumarrb, I used your SSHTunnelLibrary in my robotframework project, but i get a failed result ,Like this :

my testcase like this:
start ssh tunnel mysql_tunnel xx.xx.xx 22 yh.xx.com 22 myusername
mypassword 127.0.0.1 0

ps: my english is poor ,sorry, I hope I describe clearly

[rkumarrb]

Try like below or do not provide last argument. It should work. However I will handle this in code as well. Kindly use sshtunnel repo to raise bugs so that is more relevant place.

start ssh tunnel mysql_tunnel xx.xx.xx 22 yh.xx.com 22 myusername
mypassword 127.0.0.1 ${0}

[rkumarrb]

robot by default send all information as string to python part of code. so all port numbers sent as string so you have faced the issue.

I handled in code and published the next version to pypi. please upgrade your package and try.

[fatgouchang]

thankyou, the new package works well, my testcase now is successfull

[Raulsr]

I have created package and uploaded to pypi. Link to Github - https://github.com/C-Squad/robotframework-sshtunnellibrary

Thanks for providing this library

if the SSH_SERVER server needs a pem file along with username and password , is there a way i can pass that in robot framework ?

Start SSH Tunnel MySshTunnel ${REMOTE_HOST} ${REMOTE_PORT} ${SSH_SERVER} ${SSH_SERVER_PORT} ${SSH_SERVER_USERNAME} ${SSH_SERVER_PASSWORD} ${LOCAL_HOST} ${LOCAL_PORT}

@rkumarrb