X

Best Practices from Oracle Development's A‑Team

Using SSH for Data Sync Loads to BI Cloud Service (BICS)

For other A-Team articles about BICS and Data Sync, click here

Introduction

Since this article was written, the ability to create an SSH tunnel has been added as inbuilt functionality to the Data Sync tool.  For more details of how to use that, please see this article.  The method detailed in this article can be used for older versions of the Data Sync tool, or for a 'how to' on setting up an SSH tunnel to the DBaaS database.

The Data Sync tool can be used to load both on-premise, and cloud data sources, into BI Cloud Service (BICS).  When the target is the standard schema service database, then an HTTPS connection is used through the BICS API, and the data uploaded is encrypted via that.

While Oracle Compute allows for security lists to limit the IP addresses that can connect to the DBaaS database, the data itself that is transmitted between the host running Data Sync, and the DBaaS database, may not be encrypted and in theory could be intercepted.  Future functionality of Data Sync will provide the ability to use an inbuilt SSH connection to secure the data that is being passed.  This article will walk through a simple method to provide that functionality now.

The process involves creating an SSH tunnel from the environment running Data Sync, to the DBaaS environment, and pushing the data to be loaded through that encrypted connection.

 

Main Article

The pre-requists for this approach are:

  • A copy of the key created when the DBaaS environment was set up, and the passphrase used.  The administrator who created the DBaaS instance should have both of those.
  • An SSH tool capable of connecting with a Private Key file and creating the tunnel.  In this article Putty will be used, which is a free tool available for download from here, although any SSH tool capable of creating a tunnel could be used instead.

 

Steps

a. From within the DBaaS console, identify the target database, its IP address, and Service Name.

NOTE - if only a SID is available for the database in question, see this article for steps on how to make the SID available as a Service Name.  Follow those steps first.

Oracle_Database_Cloud_Service_Details

b. Open Putty and Set Up a Connection using the IP of the DBaaS database obtained in step (a) and port 22.

Cursor

c. Expand the 'Connection' / 'SSH' / 'Auth' menu item.  Browse in the 'Private key file for authentication' section to the key that the DBaaS administrator provided.

Windows7_x64

d. Select the 'Tunnels' sub-section within SSH.  This is where the tunnel is created.  A port on the local machine is entered in the 'Source Port'.  This must be a port that is not being used.  In the destination, enter the IP number of the DBaaS target database and the port.  This should be in the format:

host:port

Once entered, hit 'Add' to save the tunnel settings.

In this example, the local port of 9999 is used, and the destination being port 1521 on the DBaaS host.  Putty will take any traffic sent to port 9999 on the local machine, push that through the SSH tunnel, and then direct that traffic to port 1521 on the destination machine.

Screenshot_8_25_16__4_52_PM

e. Return to the 'Session' section, give the session a name and save it.  Then hit 'Open' to start the connection to the DBaaS host.

Screenshot_8_25_16__4_29_PM

f. For the 'Login as' user, enter 'opc' and when prompted for the 'Passphrase', use the passphrase for the SSH Key.  If the connection is successful, then a command prompt should appear after these have been entered:

Cursor

g. The Putty session must be left open for SSH communication to use the tunnel.

h. In Data Sync create a new connection.  Use the Service Name for the DBaaS database, but for the host, point to the machine where the Putty session is running (in this case localhost).  Use the local Port defined in step (d) above.

Screenshot_8_25_16__4_55_PM

i. Test the connection.

Cursor

j. Create a new Job in Data Sync by selecting 'New' under the 'Jobs' main menu.

Cursor

k. For the 'TARGET' Data Source, Override with the Connection created in step (h) so that the job will load data to the new DBaaS target using the SSH connection.

Cursor

 

Summary
This article walked through the steps to configure Data Sync to use an SSH tunnel to load data.

For other A-Team articles about BICS and Data Sync, click here

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha