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 https://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, May 23 '19 at 17:25
I also do not understand how `SSHTunnelForwarder` prevents your from doing *"continuous session"* — Martin Prikryl, May 23 '19 at 17:25
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, May 23 '19 at 18:10
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, May 23 '19 at 18:44
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, May 23 '19 at 19:04
In your code `remote_port` is not *"Local Port Forward port"*. --- It would help, if you show us your working configuration from your database client. — Martin Prikryl, May 23 '19 at 19:46
Not following- the analogous SQLDev connection uses remote_port/1521 as its Local Port Forward Port input. My question is what are the equivalent Python inputs via paramiko or similar approach. What other info is required for Python that wasn't required for SQLDev (other than the local port info)? — DST, May 23 '19 at 19:59
Martin, thanks for your comments. They helped me feel more comfortable trying some of the things that didn't "feel right" but were ultimately necessary for the solution. I've been working on this for a few weeks on and off, should have posted sooner. — DST, May 24 '19 at 05:43

DST · Answer 1 · 2019-05-24T16:27:05.797

0

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()

edited May 24 '19 at 16:27

answered May 24 '19 at 05:57

DST

23
6

`with` statement is not a problem either. You just need to *"do something with db"* withing the `with` statement. – Martin Prikryl May 24 '19 at 06:00
'with' statement creates function-like batch processing, which isn't what I want when I'm working with a DB throughout the day and want to interact with intermediate steps before writing the next line of code. 'with' requires me to reconnect each time. – DST May 24 '19 at 12:39

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

OSD Inputs

Python Code

1 Answers1

Solution

Linked