SSH Tunneling

DronaHQ Studio supports connecting to Postgres, MySQL, MSSQL, and MongoDB databases that are hosted within a private network via SSH tunneling. The steps for Ubuntu and Windows Server would differ. Let us see the steps involved in enabling SSH tunneling for both platforms.

For Ubuntu

  1. While configuring the Database category or managing the environments SSH Tunneling can be enabled by clicking the Enable SSH tunneling toggle button. This opens the form to get SSH details of the server.

  2. You need to provide the Host/Domain or Public IP of the server to which the SSH connection is to be established. The default SSH port is assumed to be 22. If that’s not the case, please do mention the SSH port of the server.

  3. To establish the connection successfully to the host server, a user dronahq should be created in the server and DronaHQ’s public key should be added to the host’s authorized_keys. The methods to be used for Ubuntu and Windows Server would differ as for the respective servers.

    • Script to add dronahq user in Ubuntu

      $ sudo adduser dronahq --disabled-password
      Adding user dronahq' ... Adding new group dronahq’ (1003) …
      Adding new user dronahq' (1002) with group dronahq’ …
      Creating home directory /home/dronahq' ... Copying files from /etc/skel’ …
      Changing the user information for dronahq
      Enter the new value, or press ENTER for the default
      Full Name []:
      Room Number []:
      Work Phone []:
      Home Phone []:
      Other []:
      Is the information correct? [Y/n] y

    • Adding the DronaHQ’s public key to host’s authorized_keys using these commands

      i. login as root

      > sudo su
      

      ii. make an ssh directory for dronahq user

      > mkdir -p /home/dronahq/.ssh
      

      iii. This command will add or edit the authorized_keys file. Now add the DronaHQ public key to this host’s authorized_keys file. You can copy it by clicking Here from the SSH public key authentication block under Enable SSH tunneling. The public key is copied to the clipboard from where it can be pasted in the authorized_keys file.

      > vi /home/dronahq/.ssh/authorized_keys
      

      iv. Allow proper permissions

      > chmod 644 /home/dronahq/.ssh/authorized_keys
      

      v. Change owner of authorized_keys file to dronahq

      > chown dronahq:dronahq /home/dronahq/.ssh/authorized_keys
      

For Windows Server

  1. While configuring the Database category or managing the environments SSH Tunneling can be enabled by clicking the Enable SSH tunneling toggle button. This opens the form to get SSH details of the server.

  2. Host/Domain or Public IP of the server to which the SSH connection is to be established should be given. The default SSH port is assumed to be 22. If that’s not the case, please do mention the SSH port of the server.

  3. If you do not have SSH Server then first install it using these steps.

    a. Under Settings app, go to Apps > Apps & features > Manage optional features.

    b. Click “Add a feature.


    c. Locate “OpenSSH server” feature, expand it, and click Install. The installation process would be started.

  4. Then you need to Configure the SSH server. To configure follow these steps

    a. Go to Control Panel > System and Security > Administrative Tools and open Services.

    b. Right click OpenSSH SSH Server service and go to Properties.

    c. If you want the server to start automatically when your machine is started then under Properties change the Startup type to Automatic and confirm. Start the OpenSSH Server service by clicking Start.

  5. Now we need to allow incoming connections to SSH server in Windows Firewall. So go to Control Panel > System and Security > Windows Defender Firewall to configure the firewall.
    a. Go to Windows Defender Firewall > Advanced Settings > Inbound Rules and add a new rule.

    b. Create a new rule for port 22 to allow TCP traffic. Allow the connection, if the condition matches. According to the use case, select the profiles and enter the name and description to your rule, and finish.

    c. Check the newly created rule in Inbound rules listing and make sure all configurations are correctly set.

  6. Now, set up SSH public key authentication with the following steps:

    a. Create the .ssh folder (for the authorized_keys file) in your Windows account profile folder (typically in C:\Users\username.ssh).

    b. For permissions to the .ssh folder and the authorized_keys file, what matters are Windows ACL permissions, not simple *nix permissions. Set the ACL so that the respective Windows account is the owner of the folder and the file and is the only account that has a ‘write’ access to them. The account that runs the OpenSSH Server service (typically SYSTEM or sshd) needs to have read access to the file.

    Note: Though, with the default Win32-OpenSSH configuration, there is an exception set in sshd_config for accounts in Administrators group. For these, the server uses a different location for the authorized keys file: %ALLUSERSPROFILE%\ssh\administrators_authorized_keys (i.e. typically C:\ProgramData\ssh\administrators_authorized_keys).

    c. Save the dronahq public key in authorized_keys to establish a connection.

Once the above configuration is successfully done you will be able to access your database using its private ip or as a localhost behind your Secure Host.