Are you a current or future TM1 user interested in loading on premises data into IBM Planning Analytics Cloud?
This blog post describes how to set up a secure connection to safely transmit data to the IBM Planning Analytics Cloud from on premises databases.
As IBM describes here, the IBM Secure Gateway “provides a quick, easy, and secure solution for connecting anything to anything. By deploying the light-weight and natively installed Secure Gateway Client, you can establish a secure, persistent connection between your environment and the cloud. Once this is complete, you can safely connect all of your applications and resources regardless of their location. Rather than bridging your environments at the network level like a traditional VPN that begins with full access and must be limited from the top down, Secure Gateway provides granular access only to the resources that you have defined.”
Setting up the IBM Secure Gateway is accomplished in three steps:
- Create an IBM Gateway in the Planning Analytics Cloud Environments.
- Install the IBM Secure Gateway Client on premises.
- Create an IBM Data Source and Test with TM1 TurboIntegrator.
Step 1 – Create an IBM Gateway in the IBM Planning Analytics Cloud Environments.
1.1- Log into the IBM Planning Analytics Development Environment Control page using the link provided in the IBM Welcome Kit.
1.2- Click on the Padlock icon in the upper left, then click the ‘Create Secure Gateway’ tile.
1.3- Enter a desired Gateway Name, click Create, and then close the window.
A new red ‘Disconnected’ tile for the new Gateway with no data sources will appear on the Controls page. We will create a data source in Step 3.
1.4- Click on the more options icon ‘…’ in the upper right corner of the Gateway tile and select Edit / View. You will see a Gateway ID and Gateway Key strings. Copy paste these into a notepad.
1.5- Repeat the above steps as needed to create an additional Secure Gateway in the IBM Planning Analytics Production environment or if there are additional on premises databases residing in multiple stand alone corporate networks.
STEP 2 – Install the IBM Secure Gateway Client on premises.
eCapital Advisors recommends installing the IBM Secure Gateway Client on a stand alone on premises Windows 2012 Server machine or VM. (Linux, Docker, Mac OS X are also supported by IBM). This allows for ease of installation and maintenance of the IBM Secure Gateway Client, and co-locates the IBM Secure Gateway Client with optional out of the box IBM Planning Analytics components (Command Center, Integration Server). An existing mixed-use server or always powered on desktop machine is acceptable when budget constrained.
2.1- Download the appropriate IBM Secure Client version from the list in this .json file. Here is the direct link to download the Windows version.
2.2-Run the IBM Secure Gateway set up application to install on the premises machine. Choose the default installation path and click next. Choose your preferred language and click next. Check the box to install as a Windows Service and click next.
2.3- From the notepad in Step 1.4, copy paste the Gateway ID into the ‘Gateway Ids’ field. Multiple Gateway IDs must be separated by a single space. This assumes you want to load data from premises database into multiple Planning Analytics environments, for example dev and prod environments.
2.4- From the notepad in Step 1.4 copy paste the Gateway Key into the ‘Security tokens’ field. Multiple Gateway Keys must be separated by two consecutive dashes.
2.5- Enter ‘ACL.txt–ACL.txt’ for two Gateways in the third field. The Access Control List file controls access to the on premise databases. We will create and edit the file after installation. By default the PA Secure Gateway is set to no access for all. In the last field, enter the word ‘INFO’. (Twice separated by two dashes if setting up two Gateways.)
Results shown below, then click next.
2.6- Click Install. Click Close when complete, and check task manager for a process running called “IBM Bluemix Secure Gateway Service.” Go back to the IBM Planning Analytics control page and confirm the Gateway tile is now be green and “connected.” Data sources are zero but will be defined in Step 3.
2.7- On the machine where you installed the IBM Secure Client Gateway, use Windows Explorer to navigate to the Secure Gateway Client install path of: ‘C:\Program Files (x86)\Secure Gateway Client\ibm\securegateway\client’. Edit the ‘SampleACLFile.txt’ in word pad and observe the template provided. Follow the instructions in this file and enter your allowed machine and port# using the required format. Save this text file as ‘ACL’ so it matches what you entered in Step 2.5.
The ACL must contain enter either the <hostname> and <port> or both. This is the location of the server where the data source resides, and the port number required to access. A port number is a way to identify a specific process to which an Internet or other network message is to be forwarded when it arrives at a server. If the host name is omitted, all host names reachable by the client are affected, the same is true when you omit the port number. All port numbers are affected by the rule. For example, an ‘acl allow <hostname>:’ command allows connections to all ports on that host name and denies all other connections. Allow rules are mutually exclusive while deny rules are specific. Lines that are not understood or have an unrecognizable format are ignored.
Take a look at the “securegw_service” configuration file in this directory. You will edit this file to add or remove Gateway IDs and Keys to make changes to the Secure Gateway Client connections. You can also view the log files in the log folder to troubleshoot connectivity issues. Restart the Secure Gateway service using Windows Services (“IBM Bluemix Secure Gateway Service”) so the changes take effect.
STEP 3 – Create an IBM Data Source and Test with a TM1 TurboIntegrator Process.
To create an IBM Planning Analytics data source, you will need to specify the connection information.
3.1- On the Planning Analytics controls page, click the green Gateway tile from the previous step, then click the “Add Data Source” tile.
The Data Source window opens. Input the appropriate parameters:
3.2- Enter the following required parameters for the Data Source and click Add when done.
-
- Data Source Name: Create a name for your data source connection. This is the data source name you browse for in the TurboIntegrator process to move data.
- Host Name or IP Address: Use the IP address of the on premises machine running the RDBMS database.
- Port: The Port on the on premises machine where the database resides. SQL Server typically uses Port 1433.
- Protocol: The Protocol by which you want to communicate with the database (TCP, HTTP, HTTPS, TLS). Typically it is TCP but may vary.
3.3- In the ODBC Data Source Configuration section, click the drop down for Driver and select the ODBC driver required by your on premises data source. Enter the exact name of the on premises Database, enter an optional description, and leave Trusted Connection as No. Click Create DSN.
3.4- In the Test DSN section, enter the database username and password and click Test DSN. If the IP, Port, ODBC driver, Database name, and ACL file are correct, your test should be successful as shown below. It may be helpful to test the parameters using a local ODBC driver before attempting the cloud ODBC test.
3.5- With a successful DSN test of your on premises data source, go to your IBM Planning Analytics cloud environment and launch TM1 Architect. Create and run a Turbo Integrator process using the Data Source Name specified in Step 3.2 and load your on premises data!
There are additional uses cases for the IBM Secure Gateway in addition to loading on premises data. Please reach out to me at eCapital Advisors for more details.
Chris Stauffer, cstauffer@ecapitaladvisors.com, 612.280.3654.
