Setting Up SSH on Windows Server 2019 and Securely Connecting to a Database Server via SSH Tunneling

 

Setting Up SSH on Windows Server 2019 and Securely Connecting to a Database Server via SSH Tunneling

In this blog post, we'll build on the previous topic of using SSH tunneling to connect to a remote database server. Specifically, we’ll show you how to set up SSH on Windows Server 2019 (acting as the App Server) and securely connect to a database server from a local PC via that app server. By setting up an SSH tunnel, you can bypass restrictions like firewalls or network configurations while maintaining security.

What is SSH Tunneling?

SSH tunneling, or port forwarding, is a method of securely forwarding network traffic from a local machine to a remote server. In this scenario, the app server acts as a bridge, forwarding traffic from your local machine to the database server.

Why Use SSH Tunneling?

• Secure access to the database from a remote machine.
• Bypass network restrictions when the database server is not accessible directly.
• Encrypt traffic, ensuring that sensitive data (like passwords or queries) remains safe.

Step 1: Set Up SSH on Windows Server 2019

Before you can create an SSH tunnel, you'll need to set up an SSH server on Windows Server 2019. Fortunately, Windows Server 2019 includes an OpenSSH Server as a feature that can be installed easily.

1. Install OpenSSH on Windows Server 2019

1. Open Server Manager: Log into your Windows Server 2019 and launch Server Manager.

2. Add Roles and Features:

   • Click Manage in the top-right corner and select Add Roles and Features.
   • Click Next until you reach the Features section.

3. Install OpenSSH Server:

   • In the Features section, scroll down until you find OpenSSH Server.
   • Check the box next to OpenSSH Server and click Next to install it.

4. Start the OpenSSH Service:

   • After installation, open Services (by typing services.msc in the Run dialog).
   • Find OpenSSH SSH Server in the list of services.
   • Right-click it and choose Start.
   • Set the startup type to Automatic if you want it to start automatically after every server restart.

5. Allow SSH Traffic in Windows Firewall:

   • Open Windows Defender Firewall with Advanced Security.
   • Add a new Inbound Rule to allow traffic on port 22 (the default SSH port).
   • This ensures that incoming SSH connections are allowed through the firewall.

2. Test SSH Access to the App Server

Now that SSH is installed and running, you can test the connection to your Windows Server 2019 from a local machine.

1. Open PuTTY (or any SSH client) on your local machine.
2. Enter the IP address of the Windows Server 2019 (app server) in the Host Name field.
3. Make sure the Port is set to 22 and the Connection Type is SSH.
4. Click Open to start the SSH session and log in with your credentials.

If successful, you should see a terminal prompt, indicating that SSH is working properly.

Step 2: Set Up SSH Tunneling via the App Server

Now that the Windows Server 2019 app server is set up with SSH, we’ll use it as an intermediary to tunnel traffic to the database server.

1. Open PuTTY and Configure the Tunnel

1. Open PuTTY on your local machine.

2. In the Session category, enter the hostname or IP address of the Windows Server 2019 (app server) in the Host Name field.

   • Example:

     css
     Host Name (or IP address): 192.168.1.100
     Port: 22
     

3. Navigate to Connection > SSH > Tunnels.

4. Configure the SSH Tunnel:

   • Source Port: This is the port on your local machine that will forward traffic to the database. For example, use 1434 for SQL Server.
     • Example:

       yaml
       Source port: 1434
       

   • Destination: This is the IP address and port of the database server. For example, use 1433 for SQL Server and 1521 for Oracle.
     • Example:

       arduino
       Destination: 192.168.2.50:1433 (for SQL Server)
       

       makefile
       Destination: 192.168.2.60:1521 (for Oracle)
       

5. Click Add to save the tunnel configuration. The tunnel should look like this:

   L1434 192.168.2.50:1433
   

6. Go back to the Session category and Save the session so you can reuse it in the future.

2. Start the SSH Session

1. Click Open to start the SSH session.
2. Enter your username and password for the app server to authenticate the session.

Once authenticated, your SSH tunnel will be active, and traffic from your local machine to the database server will flow securely through the app server.

Step 3: Connect to the Database via Visual Studio

Once the SSH tunnel is active, you can connect to the database from Visual Studio or any other tool that uses the database.

For SQL Server:

1. Open Visual Studio and go to Server Explorer.
2. Right-click Data Connections and select Add Connection.
3. In the Server Name field, enter:

   localhost,1434
   

4. Enter your SQL Server credentials (username and password).
5. Select or enter the database name and test the connection.

For Oracle:

1. Ensure that the Oracle Managed Data Access Client is installed in Visual Studio.
2. Go to Server Explorer > Add Connection.
3. In the connection string, enter the details as follows:

   scss
   Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SID=ORCL)));User Id=YourUsername;Password=YourPassword;
   

4. Test the connection and proceed.

Step 4: Run Queries or Debug in Visual Studio

Now, you’re connected to the remote database securely via SSH tunneling. You can run queries, perform updates, or debug your code as if the database were local.

Here’s a simple C# code to query data from the remote database via the SSH tunnel:

csharp
using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost,1434;Database=YourDatabase;User Id=YourUsername;Password=YourPassword;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection to SQL Server via SSH tunnel successful!");

                SqlCommand command = new SqlCommand("SELECT TOP 10 * FROM YourTable", connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(reader["YourColumnName"].ToString());
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Conclusion

Setting up SSH tunneling via Windows Server 2019 allows secure and flexible connections to remote databases, enabling you to overcome network restrictions while keeping your data safe. By following the steps outlined in this blog, you can configure OpenSSH on a Windows Server, set up an SSH tunnel using PuTTY, and connect to the database from your local machine via Visual Studio.

This method is ideal for remote development, debugging, or managing databases in environments where security is a top priority. Now you can work securely with your database as if it were local—happy coding!