Access remote DB via ssh tunnel (Python 3)

user_4298822 0
Viewed 11289

I can not understand (even after reading few articles dedicated to ssh tunelling) what parameters of CLI ssh command where in this script. Basically I have to connect to some server (I called it 'ssh_tunnel_host:22'), than connect to db_host using this tunnel.

with SSHTunnelForwarder(
    ('ssh_tunnel_host', 22),
    ssh_username="ssh_username",
    ssh_pkey="/somepath/id_rsa",
    local_bind_address=('0.0.0.0', 1234),
    remote_bind_address=('127.0.0.1', 3306)
) as tunnel:
    client = paramiko.SSHClient()
    client.load_system_host_keys()
    client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    client.connect('127.0.0.1', 1234)
    db_connection = pymysql.connect(host=db_host, port=3306, db='mysql', user='user',
                                    password='password', charset='utf8mb4')

Could someone explain to me:

  1. What is local_bind_address - is it my local address or ssh_tunnel_host local adderss? Where can I learn IP and port?
  2. Depends on previous question - what is remote_bind_address - is it address of ssh_tunnel_host or db_host? Where can I learn these IP and port?
  3. Where should I connect with client.connect()? Local or remote bind?

(I did try to read the docs but it is still messing)

remote-access ssh-tunnel ssh python
2 Answers

Our situation is similar and I got tired of making sure SSH tunnel is up and running before trying to connect to MS Sqlserver db. So we have a remote server (ip1) and on that we installed a docker with MSSqlserver on it. Naturally, when you log into that server, you can forward the docker ip to localhost on that machine. So if you are 'inside' ip1 you can access the server as localhost.

MS Sqlserver for all versions enforce port 1433 as the access port.

So you have ip1->localhost:1433->MSSqlserver

So SSH creates a tunnel for access to ip1 using a simple SSH user@ip1, but you need to forward localhost:1433 on the remoteserver to operate as if it is local.

from sshtunnel import SSHTunnelForwarder
import values

server = SSHTunnelForwarder(
    (values.remote_server_ip, values.remote_server_port),
    ssh_username=values.remote_server_username,
    ssh_pkey=values.rsa_path+values.rsa_file,
    ssh_private_key_password=values.rsa_passphrase,
    remote_bind_address=(values.private_server_ip, values.private_server_port),
    local_bind_address=(values.db_server_ip, values.db_server_port) )
try:
    server.start()
except:
    print("trouble connecting to the tunnel. we will asume it is already up")
else:
    print("server is started and on port ",server.local_bind_port)  # show assigned local port

do some work here and then

try:
    server.stop()
except:
    print("could not stop the server. assume it is already down")
else:
    print("stopped the server successfully")

and values.py contains values for

remote_server_ip = "....remote ip...."
remote_server_port = 22
private_server_ip = "localhost"
private_server_port = 1433
db_server_ip = "localhost"
db_server_port = 1433
remote_server_username = "...."
rsa_passphrase = "...."
rsa_path = "˜/.ssh/"
rsa_file = "id_rsa"
user_27370980
Related