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:
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:
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:
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:
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
Modified code with SSHTunnel:
Here is the code's connecting part:
import oracledb
import paramiko
import sshtunnel
from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel
from sqlalchemy.orm import sessionmaker #Run pip install sqlalchemy
from sqlalchemy import create_engine, text
import psycopg2
import os
import time
import logging
logging.basicConfig(level=logging.DEBUG)
def main():
ssh_host = "111.111.111"
ssh_port = 22
ssh_user = "ssh_user"
ssh_key_file = "c:\\Users\\ssh-key.key"
remote_bind_host = "12.5.21"
remote_bind_port = 1521
oracle_user = "or_user"
oracle_password = "or_pswd"
oracle_service_name = "db.oraclevcn.com"
with sshtunnel.open_tunnel(
(ssh_host, ssh_port),
ssh_username=ssh_user,
ssh_pkey=ssh_key_file,
remote_bind_address=(
remote_bind_host,
remote_bind_port),
local_bind_address=(
'127.0.0.1',
1522)) as server:
server.start()
print('Server connected via SSH')
local_port = str(server.local_bind_port)
engine = create_engine(f'oracle+oracledb://{oracle_user}:{oracle_password}@127.0.0.1:{local_port}/{oracle_service_name}')
Session = sessionmaker(bind=engine)
print('Connected to Oracle')
session = Session()
result = session.execute(text("SELECT * FROM all_users"))
result_exists = result.fetchone()[0]
session.close()
if __name__ == "__main__":
main()
And the end part of debuging + errors:
Server connected via SSH
DEBUG:paramiko.transport:[chan 0] Max packet in: 32768 bytes
Connected to Oracle
DEBUG:paramiko.transport:Received global request "[email protected]"
DEBUG:paramiko.transport:Rejecting "[email protected]" global request from server.
DEBUG:paramiko.transport:Debug msg: b'/home/opc/.ssh/authorized_keys:1: key options: agent-forwarding port-forwarding pty user-rc x11-forwarding'
DEBUG:paramiko.transport:[chan 0] Max packet out: 32768 bytes
DEBUG:paramiko.transport:Secsh channel 0 opened.
DEBUG:paramiko.transport:[chan 0] EOF sent (0)
DEBUG:paramiko.transport:Dropping user packet because connection is dead.
DEBUG:paramiko.transport:Dropping user packet because connection is dead.
DEBUG:paramiko.transport:EOF in transport thread
Traceback (most recent call last):
File "src\\oracledb\\impl/thin/connection.pyx", line 279, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "src\\oracledb\\impl/thin/protocol.pyx", line 242, in oracledb.thin_impl.Protocol._connect_phase_one
File "src\\oracledb\\impl/thin/protocol.pyx", line 400, in oracledb.thin_impl.Protocol._process_message
File "src\\oracledb\\impl/thin/protocol.pyx", line 379, in oracledb.thin_impl.Protocol._process_message
File "src\\oracledb\\impl/thin/messages.pyx", line 1875, in oracledb.thin_impl.ConnectMessage.process
File "C:\Python312\Lib\site-packages\oracledb\errors.py", line 182, in _raise_err
raise error.exc_type(error) from cause
oracledb.exceptions.OperationalError: DPY-6003: SID "db.oraclevcn.com" is not registered with the listener at host "127.0.0.1" port 1522. (Similar to ORA-12505)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 146, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 3300, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\impl.py", line 179, in _do_get
with util.safe_reraise():
File "C:\Python312\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 674, in __init__
self.__connect()
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 900, in __connect
with util.safe_reraise():
File "C:\Python312\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\engine\create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 620, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\oracledb\connection.py", line 1158, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\oracledb\connection.py", line 541, in __init__
impl.connect(params_impl)
File "src\\oracledb\\impl/thin/connection.pyx", line 381, in oracledb.thin_impl.ThinConnImpl.connect
File "src\\oracledb\\impl/thin/connection.pyx", line 377, in oracledb.thin_impl.ThinConnImpl.connect
File "src\\oracledb\\impl/thin/connection.pyx", line 337, in oracledb.thin_impl.ThinConnImpl._connect_with_params
File "src\\oracledb\\impl/thin/connection.pyx", line 318, in oracledb.thin_impl.ThinConnImpl._connect_with_description
File "src\\oracledb\\impl/thin/connection.pyx", line 284, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "C:\Python312\Lib\site-packages\oracledb\errors.py", line 182, in _raise_err
raise error.exc_type(error) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=53uh4nd==).
DPY-6003: SID "db.oraclevcn.com" is not registered with the listener at host "127.0.0.1" port 1522. (Similar to ORA-12505)
I saw in SQLAlchemy "create_engine" documentation that it uses cx_oracle, but it is not available in newer python only oracledb is available. In other cases they were interchangeable, maybe here it is not working.
I tried with dsn too, but I got error again:
dsn = f"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT={local_port}))(CONNECT_DATA=(SERVICE_NAME={oracle_service_name})))"
engine = create_engine(f'oracle+oracledb://{oracle_user}:{oracle_password}@/?dsn={dsn}')
sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) DPY-4011: the database or network closed the connection
Your code only sets up the forwarding between the client and the server.
But it does not actually open the local port and does not forward it to the SSH tunnel transport.
I suggest that you use sshtunnel module that does it for you and generally simplifies your code a lot. Some example:
Connecting to PostgreSQL database through SSH tunneling in Python