OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Can't connect to Oracle 19c db with SSH tunnel using python

  • Thread starter Thread starter Nándor Zaja
  • Start date Start date
N

Nándor Zaja

Guest
I am not an expert neither on Oracle or Python, so maybe the answer is obvious but I still couldn't find the answer to my problem on forums. I have an SSH connection to Oracle 19c db on SQL developer and DBeaver, but I cant connect with Python. I tried many thing like checking if thick mode is needed but got different errors.

In thin mode the SSH tunnel created but the Oracle connection run into errors.

In thick mode it freezed.

The code snippets are below.

Do you have any idea where is the problem and what could be a solution? Thanks

Thin mode:

Code:
class SSHTunnel:

    def __init__(self, ssh_host, ssh_port, ssh_user, ssh_key_file, remote_bind_host, remote_bind_port,
                 local_bind_port=1522):
        self.ssh_host = ssh_host
        self.ssh_port = ssh_port
        self.ssh_user = ssh_user
        self.ssh_key_file = ssh_key_file
        self.remote_bind_host = remote_bind_host
        self.remote_bind_port = remote_bind_port
        self.local_bind_port = local_bind_port
        self.client = None
        self.forward_tunnel = None

    def __enter__(self):
        logging.debug("Setting up SSH Tunnel")
        self.client = paramiko.SSHClient()
        self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        private_key = paramiko.RSAKey.from_private_key_file(self.ssh_key_file)
        self.client.connect(self.ssh_host, port=self.ssh_port, username=self.ssh_user, pkey=private_key,timeout=3)

        # Establish the tunnel to the remote database
        self.forward_tunnel = self.client.get_transport().open_channel(
            "direct-tcpip",
            (self.remote_bind_host, self.remote_bind_port),
            ('127.0.0.1', self.local_bind_port)
        )

        logging.debug(f"SSH Tunnel established on local port {self.local_bind_port}")
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        logging.debug("Tearing down SSH Tunnel")
        if self.forward_tunnel:
            self.forward_tunnel.close()
        if self.client:
            self.client.close()


class OracleConnection:

    def __init__(self, username, password, local_bind_port, service_name):
        self.username = username
        self.password = password
        self.local_bind_port = local_bind_port
        self.service_name = service_name
        self.connection = None

    def __enter__(self):
        logging.debug("Setting up Oracle connection")
        dsn = oracledb.makedsn("127.0.0.1", self.local_bind_port, service_name=self.service_name)
        logging.debug(f"Connecting to DSN: {dsn}")
        self.connection = oracledb.connect(user=self.username, password=self.password, dsn=dsn, disable_oob=True)
        logging.debug("Oracle connection established")
        return self.connection

    def __exit__(self, exc_type, exc_val, exc_tb):
        logging.debug("Tearing down Oracle connection")
        if self.connection:
            self.connection.close()


def main():

    ssh_host = "111.111.111.111"
    ssh_port = 22
    ssh_user = "one"
    ssh_key_file = "c:\\Users\\ssh-key.key"

    remote_bind_host = "12.7.0.41"
    remote_bind_port = 1521
    oracle_user = "system"
    oracle_password = "password"
    oracle_service_name = "oracledb.sub1.vcn03.oraclevcn.com"
    local_bind_port = 1522 

    with SSHTunnel(ssh_host, ssh_port, ssh_user, ssh_key_file,remote_bind_host, remote_bind_port, local_bind_port) as ssh_tunnel:
        with OracleConnection(oracle_user, oracle_password, ssh_tunnel.local_bind_port, oracle_service_name) as connection:
           logging.debug("Successfully connected to the Oracle database!")

            cursor = connection.cursor()
            cursor.execute("SELECT * FROM some_table")
            for row in cursor:
                print(row)

            # Close the cursor
            cursor.close()

if __name__ == "__main__":
    main()

Errors:

Code:
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=rid).
[WinError 10061] No connection could be made because the target machine actively refused it

Thick:

Code:
class SSHTunnel:

    def __init__(self, ssh_host, ssh_port, ssh_user, ssh_key_file, remote_bind_host, remote_bind_port, local_bind_port=1522):
        self.ssh_host = ssh_host
        self.ssh_port = ssh_port
        self.ssh_user = ssh_user
        self.ssh_key_file = ssh_key_file
        self.remote_bind_host = remote_bind_host
        self.remote_bind_port = remote_bind_port
        self.local_bind_port = local_bind_port
        self.client = None
        self.channel = None

    def __enter__(self):
        logging.debug("Setting up SSH Tunnel")
        self.client = paramiko.SSHClient()
        self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        self.client.connect(
            self.ssh_host, port=self.ssh_port, username=self.ssh_user, key_filename=self.ssh_key_file
        )
        
        self.channel = self.client.invoke_shell()
        self.channel.send('uname -a\n')
        
        buff = ''
        while not buff.endswith('# '):
            resp = self.channel.recv(4096).decode('utf-8')
            buff += resp
        logging.debug(f"SSH Shell Prompt: {buff}")

        self.forward_tunnel = self.client.get_transport().open_channel(
            "direct-tcpip",
            (self.remote_bind_host, self.remote_bind_port),
            ('127.0.0.1', self.local_bind_port)
        )

        logging.debug(f"SSH Tunnel established on local port {self.local_bind_port}")
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        logging.debug("Tearing down SSH Tunnel")
        if self.forward_tunnel:
            self.forward_tunnel.close()
        if self.channel:
            self.channel.close()
        if self.client:
            self.client.close()

class OracleConnection:

    def __init__(self, username, password, local_bind_port, service_name):
        self.username = username
        self.password = password
        self.local_bind_port = local_bind_port
        self.service_name = service_name
        self.connection = None

    def __enter__(self):
        logging.debug("Setting up Oracle connection in thick mode")
        
        oracledb.init_oracle_client(lib_dir="c:\\Program Files\\Oracle Client for Microsoft Tools\\t")  

        dsn = oracledb.makedsn("127.0.0.1", self.local_bind_port, service_name=self.service_name)
        logging.debug(f"Connecting to DSN: {dsn}")
        self.connection = oracledb.connect(user=self.username, password=self.password, dsn=dsn)
        logging.debug("Oracle connection established")
        return self.connection

    def __exit__(self, exc_type, exc_val, exc_tb):
        logging.debug("Tearing down Oracle connection")
        if self.connection:
            self.connection.close()

def main():

    ssh_host = "111.111.111.111"
    ssh_port = 22
    ssh_user = "one"
    ssh_key_file = "c:\\Users\\ssh-key.key"

    remote_bind_host = "12.7.0.41"
    remote_bind_port = 1521
    oracle_user = "system"
    oracle_password = "password"
    oracle_service_name = "oracledb.sub1.vcn03.oraclevcn.com"
    local_bind_port = 1522 

    with SSHTunnel(ssh_host, ssh_port, ssh_user, ssh_key_file,remote_bind_host, remote_bind_port, local_bind_port) as ssh_tunnel:
        with OracleConnection(oracle_user, oracle_password, ssh_tunnel.local_bind_port, oracle_service_name) as connection:
           logging.debug("Successfully connected to the Oracle database!")

            cursor = connection.cursor()
            cursor.execute("SELECT * FROM some_table")
            for row in cursor:
                print(row)

            # Close the cursor
            cursor.close()

if __name__ == "__main__":
    main()

Errors:

Code:
DEBUG
.transport:[chan 0] Sesch channel 0 request ok
DEBUG
.transport:[chan 0] Sesch channel 0 request ok
DEBUG
.transport:[chan 0] Unhandled channel request "[email protected]"
Traceback (most recent call last):
File "D:\Users\ssh.py", line 1995, in <module>
DEBUG
.transport:[chan 0] EOF received (0)
DEBUG
.transport:[chan 0] EOF sent (0)
main()
File "D:\Users\ssh.py", line 402, in main
with SSHTunnel(
File "D:\Users\ssh.py", line 333, in enter
resp = self.channel.recv(4096).decode('utf-8')
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\paramiko\channel.py", line 697, in recv
out = self.in_buffer.read(nbytes, self.timeout)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\paramiko\buffered_pipe.py", line 150, in read
self._cv.wait(timeout)
File "C:\Python312\Lib\threading.py", line 355, in wait
waiter.acquire()
KeyboardInterrupt
DEBUG.transport in transport thread
<p>I am not an expert neither on Oracle or Python, so maybe the answer is obvious but I still couldn't find the answer to my problem on forums.
I have an SSH connection to Oracle 19c db on SQL developer and DBeaver, but I cant connect with Python.
I tried many thing like checking if thick mode is needed but got different errors.</p>
<p>In thin mode the SSH tunnel created but the Oracle connection run into errors.</p>
<p>In thick mode it freezed.</p>
<p>The code snippets are below.</p>
<p>Do you have any idea where is the problem and what could be a solution?
Thanks</p>
<p>Thin mode:</p>
<pre><code>class SSHTunnel:

def __init__(self, ssh_host, ssh_port, ssh_user, ssh_key_file, remote_bind_host, remote_bind_port,
local_bind_port=1522):
self.ssh_host = ssh_host
self.ssh_port = ssh_port
self.ssh_user = ssh_user
self.ssh_key_file = ssh_key_file
self.remote_bind_host = remote_bind_host
self.remote_bind_port = remote_bind_port
self.local_bind_port = local_bind_port
self.client = None
self.forward_tunnel = None

def __enter__(self):
logging.debug("Setting up SSH Tunnel")
self.client = paramiko.SSHClient()
self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
private_key = paramiko.RSAKey.from_private_key_file(self.ssh_key_file)
self.client.connect(self.ssh_host, port=self.ssh_port, username=self.ssh_user, pkey=private_key,timeout=3)

# Establish the tunnel to the remote database
self.forward_tunnel = self.client.get_transport().open_channel(
"direct-tcpip",
(self.remote_bind_host, self.remote_bind_port),
('127.0.0.1', self.local_bind_port)
)

logging.debug(f"SSH Tunnel established on local port {self.local_bind_port}")
return self

def __exit__(self, exc_type, exc_val, exc_tb):
logging.debug("Tearing down SSH Tunnel")
if self.forward_tunnel:
self.forward_tunnel.close()
if self.client:
self.client.close()


class OracleConnection:

def __init__(self, username, password, local_bind_port, service_name):
self.username = username
self.password = password
self.local_bind_port = local_bind_port
self.service_name = service_name
self.connection = None

def __enter__(self):
logging.debug("Setting up Oracle connection")
dsn = oracledb.makedsn("127.0.0.1", self.local_bind_port, service_name=self.service_name)
logging.debug(f"Connecting to DSN: {dsn}")
self.connection = oracledb.connect(user=self.username, password=self.password, dsn=dsn, disable_oob=True)
logging.debug("Oracle connection established")
return self.connection

def __exit__(self, exc_type, exc_val, exc_tb):
logging.debug("Tearing down Oracle connection")
if self.connection:
self.connection.close()


def main():

ssh_host = "111.111.111.111"
ssh_port = 22
ssh_user = "one"
ssh_key_file = "c:\\Users\\ssh-key.key"

remote_bind_host = "12.7.0.41"
remote_bind_port = 1521
oracle_user = "system"
oracle_password = "password"
oracle_service_name = "oracledb.sub1.vcn03.oraclevcn.com"
local_bind_port = 1522

with SSHTunnel(ssh_host, ssh_port, ssh_user, ssh_key_file,remote_bind_host, remote_bind_port, local_bind_port) as ssh_tunnel:
with OracleConnection(oracle_user, oracle_password, ssh_tunnel.local_bind_port, oracle_service_name) as connection:
logging.debug("Successfully connected to the Oracle database!")

cursor = connection.cursor()
cursor.execute("SELECT * FROM some_table")
for row in cursor:
print(row)

# Close the cursor
cursor.close()

if __name__ == "__main__":
main()
</code></pre>
<p>Errors:</p>
<pre><code>oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=rid).
[WinError 10061] No connection could be made because the target machine actively refused it

</code></pre>
<p>Thick:<br/></p>
<pre><code>class SSHTunnel:

def __init__(self, ssh_host, ssh_port, ssh_user, ssh_key_file, remote_bind_host, remote_bind_port, local_bind_port=1522):
self.ssh_host = ssh_host
self.ssh_port = ssh_port
self.ssh_user = ssh_user
self.ssh_key_file = ssh_key_file
self.remote_bind_host = remote_bind_host
self.remote_bind_port = remote_bind_port
self.local_bind_port = local_bind_port
self.client = None
self.channel = None

def __enter__(self):
logging.debug("Setting up SSH Tunnel")
self.client = paramiko.SSHClient()
self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
self.client.connect(
self.ssh_host, port=self.ssh_port, username=self.ssh_user, key_filename=self.ssh_key_file
)

self.channel = self.client.invoke_shell()
self.channel.send('uname -a\n')

buff = ''
while not buff.endswith('# '):
resp = self.channel.recv(4096).decode('utf-8')
buff += resp
logging.debug(f"SSH Shell Prompt: {buff}")

self.forward_tunnel = self.client.get_transport().open_channel(
"direct-tcpip",
(self.remote_bind_host, self.remote_bind_port),
('127.0.0.1', self.local_bind_port)
)

logging.debug(f"SSH Tunnel established on local port {self.local_bind_port}")
return self

def __exit__(self, exc_type, exc_val, exc_tb):
logging.debug("Tearing down SSH Tunnel")
if self.forward_tunnel:
self.forward_tunnel.close()
if self.channel:
self.channel.close()
if self.client:
self.client.close()

class OracleConnection:

def __init__(self, username, password, local_bind_port, service_name):
self.username = username
self.password = password
self.local_bind_port = local_bind_port
self.service_name = service_name
self.connection = None

def __enter__(self):
logging.debug("Setting up Oracle connection in thick mode")

oracledb.init_oracle_client(lib_dir="c:\\Program Files\\Oracle Client for Microsoft Tools\\t")

dsn = oracledb.makedsn("127.0.0.1", self.local_bind_port, service_name=self.service_name)
logging.debug(f"Connecting to DSN: {dsn}")
self.connection = oracledb.connect(user=self.username, password=self.password, dsn=dsn)
logging.debug("Oracle connection established")
return self.connection

def __exit__(self, exc_type, exc_val, exc_tb):
logging.debug("Tearing down Oracle connection")
if self.connection:
self.connection.close()

def main():

ssh_host = "111.111.111.111"
ssh_port = 22
ssh_user = "one"
ssh_key_file = "c:\\Users\\ssh-key.key"

remote_bind_host = "12.7.0.41"
remote_bind_port = 1521
oracle_user = "system"
oracle_password = "password"
oracle_service_name = "oracledb.sub1.vcn03.oraclevcn.com"
local_bind_port = 1522

with SSHTunnel(ssh_host, ssh_port, ssh_user, ssh_key_file,remote_bind_host, remote_bind_port, local_bind_port) as ssh_tunnel:
with OracleConnection(oracle_user, oracle_password, ssh_tunnel.local_bind_port, oracle_service_name) as connection:
logging.debug("Successfully connected to the Oracle database!")

cursor = connection.cursor()
cursor.execute("SELECT * FROM some_table")
for row in cursor:
print(row)

# Close the cursor
cursor.close()

if __name__ == "__main__":
main()

</code></pre>
<p>Errors:</p>
<pre><code>DEBUG
.transport:[chan 0] Sesch channel 0 request ok
DEBUG
.transport:[chan 0] Sesch channel 0 request ok
DEBUG
.transport:[chan 0] Unhandled channel request "[email protected]"
Traceback (most recent call last):
File "D:\Users\ssh.py", line 1995, in <module>
DEBUG
.transport:[chan 0] EOF received (0)
DEBUG
.transport:[chan 0] EOF sent (0)
main()
File "D:\Users\ssh.py", line 402, in main
with SSHTunnel(
File "D:\Users\ssh.py", line 333, in enter
resp = self.channel.recv(4096).decode('utf-8')
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\paramiko\channel.py", line 697, in recv
out = self.in_buffer.read(nbytes, self.timeout)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\paramiko\buffered_pipe.py", line 150, in read
self._cv.wait(timeout)
File "C:\Python312\Lib\threading.py", line 355, in wait
waiter.acquire()
KeyboardInterrupt
DEBUG.transport in transport thread

</code></pre>
 
Top