The proliferation of the cloud has largely reduced the initial general concerns around security when it comes to data, files and applications in the cloud. But in reality, TM1 architects and administrators still need to factor in how to keep data secure when building and maintaining a hybrid IBM Cognos TM1 environment.
I recently worked on an exciting client project where we built a cloud gateway that adhered to on-premise security prerequisites and ultimately moved data securely from a physical location to the cloud. The gateway functioned just like a traditional ODBC connection would. We’ve documented the steps we took to create that secure gateway and have detailed them for you.
Setting up the Gateways
IBM Secure Gateway, using underlying tunneling protocols, provides a secure method to access on-premise data from within IBM Planning Analytics. It does not require the networks to open their source and destination addresses and protocols, thus allowing you to avoid configuring firewalls. The client can accept a request from a specific tunnel without exposing network information. It also provides an encrypted shell for information transfer via the gateway tunnel so that even unencrypted data gets re-packaged into an encrypted tunnel before being sent. IBM Secure Gateway supports the following protocols for the cloud to destination data connection: TCP, HTTP, HTTPS, TLS4.
IBM Secure Gateway can support various databases, including SQL, Oracle and DB2. This client used SQL Server so we started by configuring the SQL database to use a static IP address.
Through SQL configuration manager, select the Database → TCP/IP → Properties → IPAddress
If you follow this approach, next you need to assign a TCP port (64900). Leave the TCP Dynamic Ports blank. Make sure the TCP/IP is enabled.
Now you need to set up the cloud gateway. First, create a gateway within IBM Planning Analytics Control. Name the gateway and select “create,” which creates the Gateway ID and Secure Token. The Gateway ID and Secure Token generated will be used during the on-premise set-up and configuration.
We used IBM’s native Windows client for the on-premise set-up and configuration. The Windows Executable Wizard will guide you through the configuration parameters.
The Gateway ID and Security Token are the values generated in IBM Analytics Control. The Access Control List (ACL) “opens” the Database Server destinations to the Gateway Service on the cloud. The Gateway Service on the cloud is denied access to all by default. This means that while the tunnel is not established, the gateway service cannot get anywhere from here. You can leave this blank during your initial set up since it may require some testing. The default log level is Info. Note that all configuration values can be changed using the client command line utility.
Configuring the ACL (Access Control List)
The Secure Gateway client can use an ACL file upon start up. In order for the client to use a file, it must be saved in the default directory. The default location is: install dir/ProgramFiles (x86)\Secure GatewayClient/ibm/securegateway/client/. The ACL files need to contain the ACL “allow” or “deny” entries as per your destination requirements and security needs. They should be formatted like this:
acl allow <hostname>:<port>
acl deny <hostname>:<port>
no acl <hostname>:<port>
Testing the Connection & Configuring the Cloud Data Source
To test the connection, you want to open the client. It will ask you if you want to use the default configuration settings. Select “Y” and a connection will be established to your Gateway on Planning Analytics. Upon running the client, the Gateway within Planning Analytics will show as enabled (green).
Now you can add your data sources. Simply double-click the Gateway to open the data source set-up window. Add New Data Source. You must pick a new Data Source Name each time as the name you select in this section will be the name of the DSN used by Turbo Integrator to access your on-premise data. The Host or IP address is the Hostname or IP of the server that your SQL database resides on. The port is the port configured for SQL to use (in our case it was 64900) and the protocol is TCP. Click “Add” and you should receive a message that “Data source [name] has been created.”
Next you need to configure your ODBC Data Source. The Driver is SQL Server. The Database name is the Database created in SQL Server holding records. Fill out the source description. The trusted connection should be set to “No” or “Yes” depending on the authentication set in your ODBC driver configuration.
Once you select “Create DSN” you should receive a message saying “DSN [name] has been created.”
Testing the Configuration
Test the DSN by entering a Username and Password for SQL Server. You should receive a messaging saying DSN [name] test is successful.” When you return to the command line you will see that the communication line was established.
You now have a secure gateway between your on-premise database and your TM1 Cloud or IBM Planning Analytics environment. By selecting the DSN just created as the data source within Turbo Integrator, you can securely access and load data from an on premise source directly to your TM1 cloud environment. Please feel free to reach out to our technical services team at Revelwood if you have any questions. You can email us at email@example.com.