New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot get cx_Oracle working with sshtunnel module #158

Closed
gmaghera opened this Issue Mar 9, 2018 · 12 comments

Comments

Projects
None yet
5 participants
@gmaghera
Copy link

gmaghera commented Mar 9, 2018

I am using cx_Oracle version 6.2.1 against Oracle 12.1.0.2. The database is behind a bastion server, so I need to tunnel through the SSH port and the bastion server.

If I establish the tunnel manually, using the terminal, using cx_Oracle works just fine:

dsn_tns = cx_Oracle.makedsn(localhost, db_port, SID)

conn = cx_Oracle.connect(db_user, db_password, dsn_tns)

print(conn.version)
conn.close()

If I establish an SSH connection via the sshtunnel Python module, and leave it open (break while in debug mode on print('foo') below, I can connect to the database using SQLDeveloper.

with SSHTunnelForwarder(
    (bastion_server, 22),
    ssh_username="admin",
    ssh_pkey=ssh_pkey,
    remote_bind_address=(remote_bind_address, db_port),
    local_bind_address=('0.0.0.0', db_port)
) as tunnel:
    print('foo')

However, if I try to do both via code, when I try to do anything with the connection object, it times out.

with SSHTunnelForwarder(
    (bastion_server, 22),
    ssh_username="admin",
    ssh_pkey=ssh_pkey,
    remote_bind_address=(remote_bind_address, db_port),
    local_bind_address=('0.0.0.0', db_port)
) as tunnel:
    dsn_tns = cx_Oracle.makedsn(localhost, db_port, SID)

    conn = cx_Oracle.connect(db_user, db_password, dsn_tns)


    print(conn.version)
    conn.close()

In the above example, the script hangs when trying to execute print(conn.version). If I stop before reaching that line, and try to look at the conn variable, I get this error after a little while of it waiting: "Unable to display children:Timeout waiting for response on 110".

Any idea why this does not work? Is there something mutually exclusive about cx_Oracle and sshtunnel? I've searched the web for hours for an example, and I haven't found a thing. Even pages that I have found showing concrete examples of connecting to databases, will only show cx_Oracle examples without ssh tunneling (whereas the other examples weave sshtunnel into the example).

Thanks for reading this and looking into the issue!

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Mar 21, 2018

I tried code similar to yours and had the same experience. I found, however, that I was able to execute statements and fetch results without any difficulty! So I looked at the code and the code to get the connection version was holding the Python GIL while making a round trip to the database. Since the tunnel needs to do some processing, that causes difficulties. I've just pushed a commit that corrects that.

@gmaghera

This comment has been minimized.

Copy link
Author

gmaghera commented Mar 22, 2018

Thank you, Anthony!!!

@MartinasGit

This comment has been minimized.

Copy link

MartinasGit commented Apr 19, 2018

Thank you @gmaghera for sharing this - I'm trying the same thing at the moment!
May I ask you just quickly what you put as "localhost" in this line:
dsn_tns = cx_Oracle.makedsn(localhost, db_port, SID)

Many thanks!

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Apr 19, 2018

This is a complete script that works using the cx_Oracle test suite for the schema. Substitute your HOST, REMOTE_PORT, LOCAL_PORT, USER_NAME and DSN with values that work for you.

import cx_Oracle
import sshtunnel

HOST = "laptop"
REMOTE_PORT = 1521
LOCAL_PORT = 38399
USER_NAME = "atuining"
DSN = "cx_Oracle/welcome@localhost:%d/T122" % LOCAL_PORT

with sshtunnel.SSHTunnelForwarder(HOST, ssh_username = USER_NAME,
        remote_bind_address = ("127.0.0.1", REMOTE_PORT),
        local_bind_address = ("", LOCAL_PORT)) as server:

    conn = cx_Oracle.connect(DSN)
    cursor = conn.cursor()
    cursor.execute("select * from TestStrings")
    for row in cursor:
        print(row)

    print("Inserting into TestTempTable")
    cursor.execute("truncate table TestTempTable")
    data = [(i + 1, "Test String %d" % (i + 1)) for i in range(1000)]
    cursor.executemany("insert into TestTempTable values (:1, :2)", data)
    conn.commit()

    print("Checking connection version")
    print(conn.version)

print("Done!")
@MartinasGit

This comment has been minimized.

Copy link

MartinasGit commented Apr 19, 2018

Thanks @anthony-tuininga !
There is still something not working for me. Adjusted exactly as you described, but

  • when adding password as follows: conn = cx_Oracle.connect(DSN,password=mypw) I'm getting
    cx_Oracle.DatabaseError: ORA-12162: TNS:net service name is incorrectly specified

  • when leaving out the password (conn = cx_Oracle.connect(DSN)), I'm getting just:
    cx_Oracle.DatabaseError: ORA-12537: TNS:connection closed

Seems like I'm missing here something fundamental?!

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Apr 19, 2018

The password is included in the "DSN" value in my example -- its the value "welcome". If you want to do it separately, then you would need to do something like this instead:

DSN = "localhost:%d/T122" % LOCAL_PORT

conn = cx_Oracle.connect("cx_Oracle", password, DSN)

Note that the T122 value needs to be replaced with the service name of your database as well. Hope that helps!

@MartinasGit

This comment has been minimized.

Copy link

MartinasGit commented Apr 19, 2018

@anthony-tuininga , I appreciate your quick response and your help!
Here is my example code:

    host = 'h.o.s.t'
    localhost = 'lh.o.s.t'
    ssh_username = 'ssh_user'
    ssh_pw = 'ssh_pw'
    dsn = "localhost:port/db"

    with SSHTunnelForwarder(
            (host, hport),
            ssh_username=ssh_username,
            ssh_password=ssh_pw,
            remote_bind_address=(localhost, 2151),
            local_bind_address=('', 2151)

    ) as server:
        print("SSH connection to server")

        db_user = 'db_user'
        db_userpw = 'db_pw'
        conn = cx_Oracle.connect(db_user,db_userpw,dsn)
        
        print(conn.version)
        conn.close()

I'm getting now the following errors:
ERROR | Could not establish connection from ('lh.o.s.t', 2151) to remote side of the tunnel
and
cx_Oracle.DatabaseError: ORA-12537: TNS:connection closed

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Apr 19, 2018

You have something messed up with your SSH tunnel configuration. The code you have posted won't work as is. I'm assuming you're trying to obscure things and this isn't the exact code you are running? The definition of localhost is "127.0.0.1", not "1h.o.s.t", for example. If you can get your SSH tunnel working I suspect everything will work as expected.

Take a look at https://pypi.org/project/sshtunnel for some examples that don't use cx_Oracle and an explanation of how it works.

@gmaghera

This comment has been minimized.

Copy link
Author

gmaghera commented Apr 20, 2018

@MartinasGit apologies for the late response. I was using the value "127.0.0.1" for the localhost variable in my example, similar to what Anthony is suggesting to use.

@edmarmunhoz

This comment has been minimized.

Copy link

edmarmunhoz commented Jun 28, 2018

Hi @anthony-tuininga,

I am trying using your example but it is not working.
I am using cx-Oracle-6.3.1

The scenario that I have is: My Python app is my machine and I want to connect it with a DB in a DB Server in other network.
For that I need to use a jumping server (gateway) and from there I am able to connect to DB server, as below.
Flow of the tunneling:

My machine -> Jumping server -> DB server

May be I am doing something wrong.

This is the code I am using:

import os
os.chdir("C:\\Oracle\\instantclient_12_2")
import cx_Oracle
import sshtunnel

HOST = "10.12.123.123" #IP of jumping server
REMOTE_PORT = 1521
LOCAL_PORT = 1521
USER_NAME = "user" #User from jumping server
#The IP here is the IP of DB server
DSN = "sa/sa@(DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.222.33.44)(PORT=1521)))(CONNECT_DATA=(SID=DBNAME)(SRVR=DEDICATED)))"

#Here in remote_bind_address I am using again the DB IP address
with sshtunnel.SSHTunnelForwarder(HOST, ssh_username = USER_NAME,
        ssh_password="pass",
        remote_bind_address = ("10.222.33.44", REMOTE_PORT)) as server:

    conn = cx_Oracle.connect(DSN)

    cursor = conn.cursor()
    cursor.execute("select * from TestStrings")
    for row in cursor:
        print(row)

    print("Inserting into TestTempTable")
    cursor.execute("truncate table TestTempTable")
    data = [(i + 1, "Test String %d" % (i + 1)) for i in range(1000)]
    cursor.executemany("insert into TestTempTable values (:1, :2)", data)
    conn.commit()

    print("Checking connection version")
    print(conn.version)

print("Done!")

Is it correct or I am doing something wrong?

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jun 28, 2018

I'm no expert on network configuration but you can use the instructions from sshtunnel itself (https://pypi.org/project/sshtunnel/) to verify that aspect is correct. Once the sshtunnel aspect is working it shouldn't be hard to integrate the calls to cx_Oracle.

@edmarmunhoz

This comment has been minimized.

Copy link

edmarmunhoz commented Jun 28, 2018

Hi @anthony-tuininga,

I checked it and it worked fine now.
Thanks a lot!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment