Accessing Remote Databases Without VPN Using SSH Tunnels

Accessing Remote Databases Without VPN Using SSH Tunnels

In this guide, we'll walk through setting up SSH tunnels to access remote databases (MariaDB and MSSQL) located in a separate network without the need for a VPN. We'll achieve this by configuring bastion servers in both networks and establishing secure SSH tunnels between them.

Table of Contents

1. Introduction
2. Network Architecture
3. Configuring SSH Servers
4. Setting Up SSH Key Authentication
5. Configuring SSH Client Settings
6. Creating SSH Tunnel Services with systemd
7. Starting and Enabling Services
8. Conclusion

Introduction

Accessing servers across different networks often requires a VPN setup, which can be cumbersome and resource-intensive. By using SSH tunnels and bastion servers, we can securely access remote databases without the overhead of a VPN.

Network Architecture

• Network X: Contains Server A, Server B, and Bastion X.
• Network Y: Contains MariaDB Server, MSSQL Server, and Bastion Y.

Goal: Allow servers in Network X to access the databases in Network Y via SSH tunnels between the bastion servers.

Configuring SSH Servers

Update SSH Server Settings

On both bastion servers, update the SSH daemon configuration to ensure the connection remains alive.

sudo vi /etc/ssh/sshd_config

Add or update the following lines:

ClientAliveInterval 60
ClientAliveCountMax 3
TCPKeepAlive yes

Restart the SSH service:

sudo systemctl restart sshd

Setting Up SSH Key Authentication

To enable password-less SSH authentication, we'll generate SSH key pairs and distribute them accordingly.

Generate SSH Key Pair on Bastion X

ssh-keygen -t rsa -b 4096 -C "[e-mail]"

Press Enter to accept the default file location and set a passphrase if desired.

Copy Public Key to Bastion Y

ssh-copy-id bastion_user@<BastionY_IP>

Alternatively, manually copy the public key:

ssh bastion_user@<BastionY_IP> 'mkdir -p ~/.ssh && chmod 700 ~/.ssh'
cat ~/.ssh/id_rsa.pub | ssh bastion_user@<BastionY_IP> 'cat >> ~/.ssh/authorized_keys'
ssh bastion_user@<BastionY_IP> 'chmod 600 ~/.ssh/authorized_keys'

Configuring SSH Client Settings

To simplify SSH commands and manage connection settings, we'll create an SSH configuration file.

Create or Update SSH Config File

Open or create the SSH config file in your home directory:

vim ~/.ssh/config

Add the following configuration:

Host bastionY
    HostName <BastionY_IP>
    User bastion_user
    IdentityFile ~/.ssh/id_rsa
    Port 22
    ServerAliveInterval 60
    ServerAliveCountMax 3

Explanation:

• Host: An alias (bastionY) for the SSH connection to Bastion Y.
• HostName: The IP address of Bastion Y (<BastionY_IP>).
• User: The username on Bastion Y (bastion_user).
• IdentityFile: Path to your SSH private key.
• Port: SSH port (default is 22).
• ServerAliveInterval and ServerAliveCountMax: Settings to keep the SSH connection alive.

This configuration allows you to SSH into Bastion Y using the alias bastionY, simplifying your SSH commands.

Creating SSH Tunnel Services with systemd on Bastion X

We'll create systemd service files to manage our SSH tunnels for the required ports.

SSH Tunnel for MariaDB (Port 3306)

Create the service file:

sudo vim /etc/systemd/system/ssh_tunnel_3306.service

Add the following content:

[Unit]
Description=SSH Tunnel for Port 3306
After=network.target

[Service]
User=ec2-user
ExecStart=/usr/bin/ssh -L 3306:<mariadb_local_ip>:3306 -g bastionY -N -o TCPKeepAlive=yes -o ServerAliveInterval=60
Restart=always

[Install]
WantedBy=multi-user.target

SSH Tunnel for MSSQL (Port 1433)

Create the service file:

sudo vim /etc/systemd/system/ssh_tunnel_1433.service

Add the following content:

[Unit]
Description=SSH Tunnel for Port 1433
After=network.target

[Service]
User=ec2-user
ExecStart=/usr/bin/ssh -L 1433:<mssql_local_ip>:<mssql port> -g bastionY -N -o TCPKeepAlive=yes -o ServerAliveInterval=60
Restart=always

[Install]
WantedBy=multi-user.target

Note:

• User: Replace ec2-user with the appropriate username on your server.
• ExecStart: The SSH command to establish the tunnel:
  • -L: Specifies port forwarding.
  • 3306:<mariadb_local_ip>:3306: Forwards local port 3306 to <mariadb_local_ip>:3306 on the remote network.
  • -g: Allows remote hosts to connect to local forwarded ports.
  • bastionY: The SSH alias we configured earlier.
  • -N: Do not execute a remote command (useful for port forwarding).
  • -o TCPKeepAlive=yes -o ServerAliveInterval=60: Keeps the SSH connection alive.

Starting and Enabling Services

Reload the systemd daemon to recognize the new service files:

sudo systemctl daemon-reload

Enable the services to start on boot:

sudo systemctl enable ssh_tunnel_3306.service
sudo systemctl enable ssh_tunnel_1433.service

Start the services:

sudo systemctl start ssh_tunnel_3306.service
sudo systemctl start ssh_tunnel_1433.service

Check the status to ensure they're running:

sudo systemctl status ssh_tunnel_3306.service
sudo systemctl status ssh_tunnel_1433.service

Conclusion

By setting up SSH tunnels and configuring them as systemd services, we've established a secure and persistent connection between Network X and Network Y. Servers in Network X can now access the MariaDB and MSSQL servers in Network Y without the need for a VPN.

---

Feel free to leave comments or ask questions if you need further assistance with this setup.