SQL Gateway SSH Tunneling Capabilities

The modern company has data in fifteen to twenty on-premises sources, ranging from traditional databases to applications (like QuickBooks and Microsoft Excel) to flat files, and at least as many Web-based Software-as-a-Service applications. The vast majority of these applications are accessible through native or 3rd party ODBC drivers. Accessing data through the on-premises ODBC drivers externally (from a Web-based BI tool, for instance) often requires the IT team to open ports in the corporate firewall, which most IT teams are loath to do.

The SSH Tunnel feature of the SQL Gateway allows you to generate publicly accessible SQL Server or MySQL interfaces for any CData ODBC data source by creating a reverse SSH tunnel to a publicly accessible SSH host, protecting the machine(s) hosting the SQL Gateway and the on-premises data behind the firewall. This article walks through configuring the SSH Tunnel on the SQL Gateway and demonstrates connecting to ODBC data externally.

Set Up the Remote SSH Host

In order to establish a reverse SSH tunnel to the SQL Gateway, you will need to have or create a hosted (or Web-facing) SSH host. Client applications and devices wishing to connect to the SQL Gateway need know only the public address of the SSH host and the port used to forward incoming traffic to the SQL Gateway.

Configure the SSH Tunnel on SQL Gateway

The next step is establishing the reverse SSH tunnel.

1. Right-click the SQL Gateway icon in the system tray and select Server Options.
2. Navigate to the SSH Tunnel tab.
3. Set the host and port values for the SSH host.
4. Select an authentication type and enter the appropriate credentials. You can find a guide to configuring SSH authentication in the SQL Gateway help documentation.
5. Click the Test SSH Connection button to confirm your connection to the SSH host.
6. Click to save the changes.
7. Restart the SQL Gateway.

Expose Services on the SSH Tunnel

Next, you will expose new and existing services on the gateway.

1. Navigate to the Services tab.
2. If you have not already done so, create a MySQL remoting service based on a DSN. (For this article, we use a MySQL Remoting Service for HubSpot.)
3. Select a service and click Edit.
4. Click the box to expose the service on the SSH Tunnel. The SQL Gateway will automatically assign a remote port for connecting to the service through the SSH host.
5. Click to save the changes.
6. Restart the SQL Gateway.

Connecting to the SQL Gateway through the SSH Tunnel

With the SSH Tunnel configured, you can now connect to the ODBC data sources exposed by the SQL Gateway through the SSH Tunnel. To do so, simply send requests from a client to the SSH host, specifying the port to be used for forwarding. In this article, we are using MySQL Workbench to connect to the data, but you will be able to connect using any BI, reporting, or ETL tool that can communicate with a SQL Server or MySQL database. Any requests sent by tools to the SSH server and port are forwarded to the SQL Gateway, which then requests the data from the ODBC data source and returns the data through the tunnel to the client.

Reliable Connection Management

Connection management for SSH tunnels is typically handled manually or by 3rd party software. The SSH Tunnel for SQL Gateway has built-in functionality that will manage your connection automatically. If the connection is lost or interrupted, the SSH Tunnel will attempt to re-establish the connection.

Free Trial & More Information

Download a free, 30-day trial of the SQL Gateway to start connecting to your ODBC data sources (including any of the 90+ sources supported by CData Software) externally through a reverse SSH tunnel. As always, our world-class support team is ready to answer any questions you may have.