Not able to connect visa SSL Tunnel with Multi host Thin mode

[sme-eprince]

Hi,
I am trying to use this module for connecting from Mac M1 Pro to Oracle Db via a SSH tunnel,

Usually our TNS connect string looks like below
tnsnames.ora is below

TESTDB =
(description=(address_list=(address=(protocol=tcp)(port=1481)(host=127.0.0.1))(address=(protocol=tcp)(port=1480)(host=Test_DB_Scan_Server_Name)))(connect_data=(service_name=ORA_TEST))(source_route=yes))

I open a ssh tunnel in 1481 , but when I give everything and try to connect it gives me timed out.

I am able to connect via SQL developer with the same connect string via JDBC thin driver with same SSH tunnel and ports

import oracledb
import os

oracledb.defaults.config_dir = "/Users/name/PycharmProjects/flaskProject2/"
un = "user"
pw = "pass"
cs = "TESTDB"

oracledb.defaults.config_dir = os.getcwd()
cp = oracledb.ConnectParams()
cp.parse_connect_string("TESTDB")
print(cp)
dn = cp.get_connect_string()
---- dn is None even though I try to set it

print(cp.get_connect_string())

with oracledb.connect(user=un, password=pw, params=cp) as connection:
    with connection.cursor() as cursor:
        sql = "select sysdate from dual"
for r in cursor.execute(sql):
    print(r)

I tried with the directly giving tnsnames.ora file as a parameter itself

with oracledb.connect(user=un,password=pw,dsn="TEST_DB",config_dir="/Users/name/PycharmProjects/flaskProject2/") as connection:
    with connection.cursor() as cursor:
        sql = "select * from tmp_dummy"
for r in cursor.execute(sql):
    print(r)

Still giving me the same timed out error

Traceback (most recent call last):
  File "/Users/name/PycharmProjects/flaskProject2/db_test.py", line 32, in <module>
    with oracledb.connect(user=un,password=pw,dsn="TEST_DB",config_dir="/Users/name/PycharmProjects/flaskProject2/") as connection:
  File "/Users/name/PycharmProjects/flaskProject2/lib/python3.9/site-packages/oracledb/connection.py", line 1012, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/Users/name/PycharmProjects/flaskProject2/lib/python3.9/site-packages/oracledb/connection.py", line 135, in __init__
    impl.connect(params_impl)
  File "src/oracledb/impl/thin/connection.pyx", line 298, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 189, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src/oracledb/impl/thin/connection.pyx", line 162, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src/oracledb/impl/thin/connection.pyx", line 111, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "/Users/name/PycharmProjects/flaskProject2/lib/python3.9/site-packages/oracledb/errors.py", line 111, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database. Connection failed with "timed out"

Could you please help

[anthony-tuininga]

The first thing you mentioned (calling parse_connect_string() followed by get_connect_string()) looks like a buglet which I can fix. Of course, the question is why you would bother doing so! Passing the connect string directly makes more sense!

Looking at your connect string, though, it looks like you may be using CMAN? It references source_route=yes which the thin driver doesn't understand currently. Can you provide more details on the database configuration? I'll ask internally regarding the CMAN configuration and source_route=yes and what is required to support that.

[sme-eprince]

Hi ,
Yes we use a CMAN which is running in my jump server, it is like the gateway for our DB which is in another VCN in OCI.
We want to secure the DB and make sure an extra layer of security with the Jump server authentication.

I tried many options so I used parse_connect_string() and other functions. Yes ideally connection string is easy method for us.

Configuration is like below

Client machine ---> Jump Server ---> Database
CMAN is in Jump Server -----> Database

We basically open a Terminal connection in SSH (Port 22) and create a tunnel to port 1480 of CMAN which will forward the connection to Database

ssh -i user@jumpserver -L 127.0.0.1:1481:jumpserver:1480

connection string will be then like below (Note: we make the address list with 1 host as local machine with the same port as local tunnel port)

(description=(address_list=(address=(protocol=tcp)(port=1481)(host=127.0.0.1))(address=(protocol=tcp)(port=1480)(host=Test_DB_Scan_Server_Name)))(connect_data=(service_name=ORA_TEST))(source_route=yes))

I am able to connect from SQL Developer which is using Thin driver and it works good.

Should I raise a bug for this ?

[anthony-tuininga]

Thanks for the explanation. It looks to me like some code needs to be added to thin mode to support this, so yes, please create a new enhancement issue from this.

[sme-eprince]

Thanks a lot. I have raised an issue below is the link

#81 (comment)