Translate Oracle SQL Developer SSH Host w/ Local Port Forward Connection to Python

Question

I'm trying to create a Python connection to a remote server through an SSH Jump Host (one I've successfully created in Oracle SQL Developer) but can't replicate in Python. Can connect to SSH Host successfully but fail to forward the port to the remote server due to timeout or error opening tunnels. Safe to assume my code is incorrect rather than server issues. Also need a solution that doesn't use the "with SSHTunnelForwarder() as server:" approach because I need a continuous session similar to OSD/cx_Oracle session rather than a batch processing function.

Similar examples provided here (and elsewhere) using paramiko, sshtunnel, and cx_Oracle haven't worked for me. Many other examples don't require (or at least clearly specify) separate login credentials for the remote server. I expect the critical unclear piece is which local host + port to use, which my SQL Developer connection doesn't require explicitly (although I've tried using the ports OSD chooses, not at the same time).

Closest match I think was best answer from paramiko-port-forwarding-around-a-nat-router

OSD Inputs

SSH Host
- host = proxy_hostname
- port = proxy_port = 22
- username = proxy_username
- password = proxy_password

Local Port Forward
- host = remote_hostname
- port = remote_port = 1521
- automatically assign local port = True

Connection
- username = remote_username
- password = remote_password
- connection type = SSH
- SID = remote_server_sid

Python Code

i.e., analogous code from paramiko-port-forwarding-around-a-nat-router

import paramiko
from paramiko import SSHClient

# Instantiate a client and connect to the proxy server
proxy_client = SSHClient()
proxy_client.connect(
    proxy_hostname,
    port=proxy_port,
    username=proxy_username,
    password=proxy_password)

# Get the client's transport and open a `direct-tcpip` channel passing
# the destination hostname:port and the local hostname:port
transport = proxy_client.get_transport()
dest_addr = (remote_hostname,remote_port)
local_addr = ('localhost',55587)
channel = transport.open_channel("direct-tcpip", dest_addr, local_addr)

# Create a NEW client and pass this channel to it as the `sock` (along 
# with whatever credentials you need to auth into your REMOTE box
remote_client = SSHClient()
remote_client.connect(
    'localhost',
    port=55587, 
    username=remote_username, 
    password=remote_password,
    sock=channel)

Rather than a connection to the remote server I get

transport.py in start_client()
SSHException: Error reading SSH protocol banner

Your remote_client code does match that of stackoverflow.com/q/18968069/850848 - You connect to remote_hostname:remote_port, while you should connect to localhost:22. Also 22 is a bad choice of a port, you may need to use a higher port number, as 22 might conflict with existing SSH local server - also on *nix, you need root permissions to open 22 port anyway. Just strictly follow the code from the other question. — Martin Prikryl
I also do not understand how SSHTunnelForwarder prevents your from doing "continuous session" — Martin Prikryl
Changing local_addr to port 55587 (a successfully used port with SQL Developer) and remote_client.connect('localhost',port=55587...) returns the same error. — DST
How exactly are you using 55587 in SQL developer? + What is 1521? Is that a database port? If it is, then you obviously cannot use SSH client (remote_client = SSHClient()) to connect to it. — Martin Prikryl
The successful SQLDev connection automatically assigns a local port and 55587 has been selected in the past. I don't know anything about 1521 except that's the Local Port Forward port on the remote machine where I used to login directly from Python before needing to go through the Jump Host. — DST

DST DST · Accepted Answer · 2019-05-24T05:57:43

Solution

Finally figured out a solution! Analogous to OSD's automatic local port assignment and doesn't require SSHTunnelForwarder's with statement. Hope it can help someone else- use the question's OSD input variables with...

from sshtunnel import SSHTunnelForwarder
import cx_Oracle 

server=SSHTunnelForwarder(
    (proxy_hostname,proxy_port),
    ssh_username=proxy_username,
    ssh_password=proxy_password,
    remote_bind_address=(remote_hostname,remote_port))

server.start()
db=cx_Oracle.connect('%s/%s@%s:%s/%s'%(remote_username,remote_password,'localhost',server.local_bind_port,remote_server_sid))
# do something with db
server.close()

Translate Oracle SQL Developer SSH Host w/ Local Port Forward Connection to Python

OSD Inputs

Python Code

1 Answers

Solution