SSH Tunnel
In computer networks , a tunneling 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. Users may set up SSH tunnels to transfer unencrypted traffic over a network through an encrypted channel.
To set up a local SSH tunnel, one configures an SSH client to forward a specified local port to a port on the remote machine. Once the SSH tunnel has been established, the user 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.
To work with SSH Tunnels Data Virtuality Server provides the following tables and stored procedures:
SYSADMIN_VDB.SSHTunnel table to hold and control all SSH Tunnels created before
Field name | Field type | Mandatory | Description |
---|---|---|---|
id | biginteger | NOT NULL | A unique identifier. |
name | string(255) | NOT NULL | A unique name of an SSH Tunnel. |
localHost | string(255) | NOT NULL | A host of a client's machine (localhost by default). |
localPort | integer | NOT NULL | A port of a client's machine. |
remoteHost | string(255) | NOT NULL | A host of a remote machine. |
remotePort | integer | NOT NULL | A port of a remote machine. |
host | string(255) | NOT NULL | A username and host used for connecting to a remote machine via SSH protocol (must be in the format: username@host). |
portForwardingType | string(1) | NOT NULL | Type of SSH port forwarding (L by default). As Data Virtuality Server supports only local port forwarding at the moment the field can have only L value. |
sshPort | integer | NOT NULL | A port used by SSH Server on a remote machine (22 by default). |
sshProperties | string | NULL | SSH comma separated properties in CSV-like form: property1=<value1>,property2=<value2>. It's used to provide a timeout, cypher parameters, key parameters like used algorithms, a possibility to provide classes for custom algorithms and so on. |
password | string(255) | NULL | A password used for a simple authentication on the SSH server (encrypted). |
passPhrase | string(255) | NULL | A password phrase used in case of a secured private key and key authentication on the SSH server (encrypted). |
privateKey | string(4096) | NULL | A private key used for key authentication on the SSH server. |
proxy | string(255) | NULL | Proxy type. |
proxyHost | string(255) | Null | The proxy host. |
proxyPort | integer | Null | The proxy port. |
proxyUser | string(255) | Null | The proxy user name. |
proxyPassword | string(255) | Null | The proxy user password (encrypted). |
state | string(128) | NOT NULL | A state of a particular SSH Tunnel (possible values: SUCCESS or FAILED). A special Refresh daemon controls all SSH Tunnels by a timeout (60 secs) to re-create an SSH Tunnel in case it failed. |
failureReason | string(4096) | NULL | A failure reason in case a particular SSH Tunnel failed. |
creationDate | timestamp | NOT NULL | The creation date of an SSH Tunnel. |
lastModifiedDate | timestamp | NOT NULL | The date of the latest modification of an SSH Tunnel. |
creator | string(255) | NOT NULL | An author who created an SSH Tunnel. |
modifier | string(255) | NOT NULL | An author who modified an SSH Tunnel. |
SYSADMIN_VDB.createSSHTunnel stored procedure to create an SSH Tunnel
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NOT NULL | A unique name of an SSH Tunnel. |
localHost | string | NULL | A host of a client's machine (localhost by default). |
localPort | integer | NOT NULL | A port of a client's machine. |
remoteHost | string | NOT NULL | A host of a remote machine. |
remotePort | integer | NOT NULL | A port of a remote machine. |
host | string | NOT NULL | A username and host used for connecting to a remote machine via SSH protocol (must be in the format: username@host). |
sshPort | integer | NULL | A port used by SSH Server on a remote machine (22 by default). |
sshProperties | string | NULL | SSH comma separated properties in CSV-like form: property1=<value1>,property2=<value2>. It's used to provide a timeout, cypher parameters, key parameters like used algorithms, a possibility to provide classes for custom algorithms and so on. |
password | string | NULL | A password used for simple authentication on the SSH server. |
passPhrase | string | NULL | A password phrase used in case of a secured private key and key authentication on the SSH server. |
privateKeyPath | string | NULL | A private key path or the private key itself as a string in case of key authentication on the SSH server. |
proxy | string | NULL | Possible values: NONE, HTTP, SOCKS4, SOCKS5 (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
proxyHost | string | NULL | Host address of the proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
proxyPort | integer | NULL | The port used by proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
proxyUser | string | NULL | The user at proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
proxyPassword | string | NULL | Password for proxy server (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
SYSADMIN_VDB.removeSSHTunnel stored procedure to remove an SSH Tunnel
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NOT NULL | A unique name of an SSH Tunnel intended for removal. |
SYSADMIN_VDB.importSSHTunnel stored procedure to import or refresh an SSH Tunnel
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NOT NULL | A unique name of an SSH Tunnel. |
localHost | string | NULL | A host of a client's machine (localhost by default). |
localPort | integer | NOT NULL | A port of a client's machine. |
remoteHost | string | NOT NULL | A host of a remote machine. |
remotePort | integer | NOT NULL | A port of a remote machine. |
host | string | NOT NULL | A username and host used for connecting to a remote machine via SSH protocol (must be in the format: username@host). |
portForwardingType | string | NULL | Type of SSH port forwarding (L by default). As Data Virtuality Server supports only local port forwarding at the moment the field can have only L value. |
sshPort | integer | NULL | A port used by SSH Server on a remote machine (22 by default). |
encryptedSshProperties | string | NULL | SSH comma separated properties in CSV-like form: property1=<value1>,property2=<value2>. It's used to provide a timeout, cypher parameters, key parameters like used algorithms, a possibility to provide classes for custom algorithms and so on. It can consist of sensitive data that's why the value should be encrypted. |
encryptedPassword | string | NULL | An encrypted password using for simple authentication on the SSH server. |
encryptedPassPhrase | string | NULL | An encrypted password phrase used in case of a secured private key and key authentication on the SSH server. |
encryptedPrivateKey | string | NULL | An encrypted private key in case of key authentication on the SSH server. |
proxy | string | NULL | Possible values: NONE, HTTP, SOCKS4, SOCKS5 (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
proxyHost | string | NULL | The address of the proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
proxyPort | string | NULL | The port used by proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
proxyUser | string | NULL | A user at the proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
encryptedProxyPassword | string | NULL | Encrypted password for the proxy server |
sshProperties | string | NULL | SSH comma separated properties in CSV-like form: property1=<value1>,property2=<value2>. It's used to provide a timeout, cypher parameters, key parameters like used algorithms, a possibility to provide classes for custom algorithms and so on. |
password | string | NULL | A password used for a simple authentication on the SSH server. |
passPhrase | string | NULL | A password phrase used in case of a secured private key and key authentication on the SSH server. |
privateKey | string | NULL | A private key in case of key authentication on the SSH server. |
proxyPassword | string | NULL | A password for the proxy server. |
DataVirtuality Server also supports SSH Tunnel history of changes, all history is being stored in the corresponding SYSLOG.SSHTunnelHistory table.
Examples
Let's create a local port forwarding via SSH protocol for a MySQL server installed on a remote machine. Let's assume that the MySQL server listening to standard 3306 port on the remote machine and we would like to use local 5000 port for connecting to the MySQL server.
create an SSH Tunnel between local 5000 port and 3306 port on a remote machine via SSH protocol:
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' );;
SQLcheck that the just created above SSH Tunnel has SUCCESS state running the following query:
select * from "SYSADMIN_VDB.SSHTunnel" where name = 'test1' and state = 'SUCCESS';;
SQLin case the SSH Tunnel has SUCCESS state run the following commands for creating MySQL data source using the local 5000 port which actually is 3306 on the remote machine:
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' );;
SQL