SSH Tunnel

In computer networks, a tunnelling protocol allows a network user to access or provide a network service that the underlying network does not support or provide directly.

A Secure Shell (SSH) tunnel consists of an encrypted tunnel created through an SSH protocol connection. You can set up SSH tunnels to transfer unencrypted traffic over a network through an encrypted channel.

To set up a local SSH tunnel, configure an SSH client to forward a specified local port to a port on the remote machine. Once the SSH tunnel has been established, you can connect to the specified local port to access the network service. The local port does not have to be the same as the remote port.

Working with SSH Tunnels

To work with SSH tunnels, the Data Virtuality Server provides a special SYSADMIN_VDB.SSHTunnel table and several stored procedures - please see SYSADMIN_VDB Procedures for more information on them.

The CData Virtuality Server also logs history of changes for the SSH tunnel. All history is stored in the relevant SYSLOG.SSHTunnelHistory table.

Credentials Management

In order to facilitate SSH tunnels creation and usage, the CData Virtuality Server provides key generation and management functionality. This functionality is supported by the SYSADMIN_VDB.SSHCredentials table, which holds and controls all key pairs generated by the CData Virtuality Server and used by SSH tunnels, and dedicated procedures - please see SYSADMIN_VDB Procedures for more information on them.

The CData Virtuality Server also supports SSH credentials history of changes. All history is stored in the relevant SYSLOG.SSHCredentialsHistory table.

Examples

Creating a Simple Tunnel

Let us create a local port forwarding via SSH protocol for a MySQL server installed on a remote machine. Let us assume that the MySQL server is listening to the standard 3306 port on the remote machine, and we would like to use the local 5000 port for connecting to the MySQL server. These are the steps to do it:

Create an SSH Tunnel between the local 5000 port and 3306 port on a remote machine via SSH protocol:

SQL

CALL "SYSADMIN_VDB.createSSHTunnel"(
    "name" => 'test1',
    "localHost" => 'localhost',
    "localPort" => 5000,
    "remoteHost" => 'remotehost',
    "remotePort" => 3306,
    "host" => 'username@remotehost',
    "sshPort" => 22,
    "sshProperties" => '',
    "password" => '',
    "passPhrase" => 'testPhrase',
    "privateKeyPath" => 'C:\DataVirtuality\private_key'
);;

Check that the newly created SSH tunnel has the SUCCESS state as follows:

SQL

SELECT * FROM "SYSADMIN_VDB.SSHTunnel" WHERE name = 'test1' AND state = 'SUCCESS';;

If the SSH Tunnel has the SUCCESS state, run the following commands for creating MySQL data source using the local 5000 port which actually is 3306 on the remote machine:

SQL

CALL "SYSADMIN.createConnection"(
    "name" => 'testDS',
    "jbossCLITemplateName" => 'mysql',
    "connectionOrResourceAdapterProperties" => 'db=DB_NAME,user-name=USER,password=PASSWORD,host=localhost,port=5000'
);;
CALL "SYSADMIN.createDataSource"(
    "name" => 'testDS',
    "translator" => 'mysql5',
    "modelProperties" => 'importer.useFullSchemaName=false,importer.widenUnsingedTypes=false,importer.importIndexes=false',
    "translatorProperties" => 'supportsNativeQueries=true'
);;

Creating a Tunnel Using a Key Pair

There are two ways to create an SSH tunnel using a key pair: via the web interface with a pre-generated SSH key pair already created by the CData Virtuality Server or via special SQL statements.

Using the Web Interface

By default, the CData Virtuality Server has a pre-generated SSH key pair already created. In the web interface, click on the ‘Key pair’ field to select one of the SSH keys from the drop-down list and follow the instructions for deploying the public key on your SSH host/server:

Using SQL Commands

If you prefer to create your own SSH key pair, you can follow the steps described below.

Let us assume that the remote server is running on testuser@123.somedomain.com on the 1234 port, and we would like to access it as localhost:4321.

Create a key pair. Note the public key and place it on the SSH server for the respective user (in this case, for user testuser on testuser@123.somedomain.com). The tunnel will direct to the RDP port:
SQL
```
SELECT * FROM SYSADMIN_VDB.createSSHKeyPair ();;
```

Create the tunnel using the ID of the newly created key pair:

SQL

CALL SYSADMIN_VDB.createSSHTunnel(
    host => 'ec2-user@ec2-34-237-147-102.compute-1.amazonaws.com',
    name => 'test2',
    remoteHost => '10.0.0.4',
    remotePort => 1234,
    sshKeyPairId => 1,
	localPort => 4321
);;

Check that the newly created SSH tunnel has the SUCCESS state as follows:

SQL

SELECT * FROM "SYSADMIN_VDB.SSHTunnel" WHERE name = 'test2' AND state = 'SUCCESS';;

If the SSH Tunnel has the SUCCESS state, try connecting to localhost:4321 via RDP.