The Data Sync tool provides the ability to extract from both on-premise, and cloud data sources, and to load that data into BI Cloud Service (BICS), Oracle Analytics Cloud Service (OACS), and other relational databases.
Last year I covered an approach that could be used to secure the connections used by Data Sync by using an SSH tunnel (link to the article)
In the 2.3 release of Data Sync, the ability to create and use one or multiple SSH tunnels was added natively to the tool. At the time of writing this functionality is still considered 'Beta', although it does appear to work well.
This article will walk through steps to set up the tool to use the native SSH tunnel functionality.
As a pre-requisite, you will need the IP address, port, and service name of the database that will be connected to via SSH, and a copy of the SSH private key for that host. In the case of a DBaaS database, the key would have been provided at the time of its creation, and the IP, port and service name can be viewed from the console. See step (a) in the previous blog for more help identifying those values (link to the article), and check with your database or cloud administrator to get a copy of the private SSH key.
Be sure to download and install the latest version of the Data Sync Tool from OTN through this link.
Data Sync requires JDK8. You can download that through this link.
(Note - customers still using BI Cloud Service (BICS) and loading into the schema service database, can not use this version of Data Sync. The version for BICS is available to download here, but does not have all the functionality of the OAC / OAAC version.)
For further instructions on configuring Data Sync, see this article. If a previous version of Data Sync is being upgraded, use the documentation on OTN.
If Data Sync is running, close the application and stop the data sync service from the menu bar.
Default JDKs do not come with the unlimited strength version of the Java Cryptography Extension (JCE) that is required. These must be downloaded and installed
a. Confirm whether the JDK is version 7 or 8. This can be done by opening the 'config.sh' or 'config.bat' file in the main data sync directory. In this example, the JDK is version 8:
Another method to obtain the Java version is to run the command 'java -version' from within the /bin directory of the java home
b. Download the correct version of the JCE.
For JDK 7, download from this link:
For JDK 8, download from this link:
c. Unzip the contents of the ZIP file, then replace the following 2 files: 'local_policy.jar' and 'US_export_policy.jar' in this path, with the versions in the zip.
a. Go to Views -> SSH Tunnels (Beta).
b. You will receive an Information Message. Click 'OK'.
c. Create a new entry, providing the following details:
Name: A descriptive name.
Remote Host: The IP address of the DBaaS host.
Remote SSH Port: The SSH Port on the remote host. Typically 22.
User Name: User name to the DBaaS host, typically 'opc'.
Private Key File: the path and file of the SSH private key that matches the public key associated with the DBaaS host deployment.
Passphrase: the passphrase specified for the SSH key
Port Forward: The target port on the DBaaS database, typically 1521.
Local Port: An unassigned port on the local machine which can be used for the tunnel. If you are not sure, click on "Find Available Port", and a port will be identified and automatically filled in.
d. Save the entry, and then test the connection.
The tunnel will remain open for as long as the Data Sync tool is running
In this case we set it up as a 'Target', but the same process could be used for a source. Multiple SSH tunnels can be created using these steps, although a different local port would be required for each SSH tunnel. For the target, select either the default 'TARGET' connection, or a new database connection.
a. Set up the connection as you would normally, but for the 'Host' field enter either 'localhost' or the IP or machine name of the computer where Data Sync is running.
b. For Port, enter the local port that was set up in step 3.
c. Test the connection.
If the tunnel gets closed for some reason, the failures seen in the job will reference 'IO Exceptions'. If this happens, go to the SSH Tunnels view and resolve any issues, then retry.
This article walked through the set up steps to use the native functionality of Data Sync to create and use SSH tunnels to secure database connections.
For other A-Team articles about BICS and Data Sync, click here.