Connect To Database: Connection to the database failed for an unknown reason.

[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

I get the following error:

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.

I have also tried a mix of running the 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}
        sleep 60

And then running the python program to connect to the DB which works, so I know the tunnel is created and I am can connect.
I have also tried the same with the other keyword, Connect to Database Using Custom Params
Any ideas as to why the DB connection can't be made within RF?

[jerry57]

I have run tests over ssh tunnels before, so I don't think there is a problem with Database Library. Could you try manually creating the ssh tunnel and then running a robotframework test using the databaselibrary? Also, have you tried using DBlibrary to connect without the ssh tunnel? Can you try running Robotframework with "--loglevel TRACE"

[aimeejtix]

I created the SSH tunnel myself and connected successfully using
Connect To Database Using Custom Params and I could see the connection string has changed slightly:
Connect To Database Using Custom Params : pymssql.connect(db_api_2.connect(database='test_db', user='user1', password='paswd1', host='localhost', port=11433))

It doesn't work this way using
Connect To Database - Same error as before - InterfaceError: Connection to the database failed for an unknown reason.

So now it seems

• I can create an ssh tunnel manually and connect to the DB using RF
• I can create an ssh tunnel with RF and connect to the DB from a python script

I must be missing something about how RF works.. it seems really strange.

I added the TRACE log levels and it doesn't seem to output anything more than before. When I try to run everything through RF the test just hangs until a specified timeout (and sometimes doesn't even abide by the timeout) so I can't even check the logs properly.

I'm not sure DBLibrary you are talking about exactly... could you be more specific?

[jerry57]

Unfortunately I don't have a setup to test this out right now. TRACE doesn't always give more info but sometimes it helps. I am rather puzzled as to this problem as well.

My reference to "DBlibrary" was just my shorthanding "DatabaseLibrary" and not doing a good job of it, sorry.
Have you tried the Connect to Database Using Custom Params with SSH tunnel all within RF?

[jerry57]

What are the chances this is a timing issue with how the tunnel is being created with RF and then used by RF DatabaseLibrary? Can you add a 10sec sleep between the tunnel creation and initiating the DB connect? What happens then?

[aimeejtix]

I have just tried with both:
Connect To Database Using Custom Params -> Test hangs and doesn't even stop when specified timeout is reached. I do notice that when it's hanging and I try to stop it, I can't. Ctrl+C does nothing so I have to kill the process.
Connect To Database -> Same error as mentioned before

[jerry57]

can you send me a testsuite that is failing and the suite/exact steps when it works? I will see what I can do with it here?

[KenClemson]

Hi Aimeejtix did you end up getting your issue to work I also need to run robot database tests on a machine that has ssh.