CODEWITHSUNDEEP
pythonsshremote-accessssh-tunnel
Vladimir Kolenov
Vladimir Kolenov

Reputation: 438

Access remote DB via ssh tunnel (Python 3)

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)

Upvotes: 8

Views: 14554

Answers (2)

Koos Brandt
Koos Brandt

Reputation: 1

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"

Upvotes: -1

danny
danny

Reputation: 5270

Think of it like a proxy connection. You connect to ssh_tunnel_host:22 and you tell it to proxy connections from its <db host>:3306, meaning port 3306 on db_host accessed by ssh_tunnel_host to you, the client.

You can either specify the local (to you) ip:port you want the proxy connection to be available on, or let the client choose a free one. Omitting local_bind_address does the latter.

You then connect to your local port which is actually a proxy to remote_bind_address:3306.

local_bind_address <-> ssh_tunnel_host <-> remote_bind_address

Code should be:

db_host = '<address reachable only by ssh_tunnel_host>'
with SSHTunnelForwarder(
    ('ssh_tunnel_host', 22),
    ssh_username="ssh_username",
    ssh_pkey="/somepath/id_rsa",
    remote_bind_address=(db_host, 3306)
) as tunnel:
    port = tunnel.local_bind_port
    db_connection = pymysql.connect(
        host='127.0.0.1', port=port, db='mysql', user='user',
        password='password', charset='utf8mb4')

  1. Local to client address. Either set one, or let client choose and find its port from tunnel.local_bind_port.

  2. The address you want ssh_tunnel_host to proxy back to you. If it's a local to the server service, the IP will be 127.0.0.1 and port of the service. It could well be any other IP or an IP in ssh_tunnel_host's network that is not visible outside SSH tunnel host.

  3. Nowhere. The tunnel provides a local ip:port that proxies the remote connection. Once the tunnel is up, no other client is needed. Just connect to the local ip:port.

Upvotes: 16

Related Questions