SQL Broker 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 Gateway feature of the SQL Broker allows you to generate publicly accessible SQL Server or MySQL interfaces for your ODBC data sources by creating a reverse SSH tunnel to a publicly accessible SSH host, protecting the machine(s) hosting the SQL Broker and the on-premises data behind the firewall. This article walks through configuring the Gateway on the SQL Broker and demonstrates connecting to ODBC data externally.

Set Up the Remote SSH Host

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

Configure the Gateway on SQL Broker

The next step is establishing the reverse SSH tunnel.

  1. Right-click the SQL Broker icon in the system tray and select Server Options.
  2. Navigate to the Gateway 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 Broker 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 Broker.

Expose Services on the Gateway

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 Gateway. The SQL Broker 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 Broker.

Connecting to the SQL Broker through the Gateway

With the Gateway configured, you can now connect to the ODBC data sources exposed by the SQL Broker through the Gateway. 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 Broker, 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 Gateway for SQL Broker has built-in functionality that will manage your connection automatically. If the connection is lost or interrupted, the Gateway will attempt to re-establish the connection.

Free Trial & More Information

Download a free, 30-day trial of the SQL Broker 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.