Qlik Replicate: LINUX SSL setup for DB2i connection

[David_Fergen]

TLS communication is not natively supported by IBM's ODBC iSeries driver. This however can be accomplished using Stunnel. 

Index:

• Overview
• Prerequisites
• Setup
• Configure /etc/hosts
• Configure stunnel
• Configure the odbc.ini
• Validation
• Trace outbound communications from Linux server
• Display iSeries job details

Overview

Stunnel is a proxy designed to add TLS encryption functionality to existing clients and servers without changing the programs' code. Its architecture is optimized for security, portability, and scalability (including load balancing), making it suitable for large deployments.

Once the Stunnel is installed, its time to configure different system components. The steps can be summarized as below: 

1. /etc/hosts: define local hosts for stunnel to listen to (one for each source Database. If the same DB is used with different journals, you will need only one connection.
2. iSeries.stunnel.config: define the port forwarding on the local machine. The target port should always be 9471. The local port can be configured as desired.
3. Configure ODBC.INI to define DNSs for every connection/DB.
4. Configure the Qlik Replicate task.

Note: You can always test from Linux with isql delivered with the unixODBC.

Prerequisites

The following prerequisites must be met: 

1. Secure Connection ports must be open between the Replicate Server and iSeries partition of interest: http://www-01.ibm.com/support/docview.wss?uid=nas8N1018654.
   
   
2. SSL is enabled on iSeries side to allow secure connections.
   
   
3. Service accounts with corresponding privileges should be defined on iSeries to allow connection from Qlik Replicate. 
   
   
4. The user needs to obtain an SSL certificate in PEM format. If provided in a different format, a certificate can be converted to the desired one. See this page for instructions.
   
   Sample command: 
   
   $ openssl x509 -inform der -in certificate.cer -outform pem -out certificate.pem
   
5. The stunnel software needs to be installed on a Linux machine where Qlik Replicate is running
   
   Sample command:
   
   $ yum install stunnel​

Setup

Configure /etc/hosts

Define local IPs and corresponding DSNs in the /etc/hosts, as shown in the example below:

127.0.0.1   localhost localhost.domain
127.0.0.11  DBSSL1.localhost
127.0.0.12  DBSSL2.localhost
127.0.0.13  DBSSL3.localhost

Configure stunnel

1. Convert the PEM certificate received from iSeries administrator to text file.
   

   
   Do this by cat.. command and copy the contents to a text file:
   
   cat certificate.pem > certificate.txt

2. Edit the iaccess.stunnel.config file:
   
   
   1. Modify each accept= line to contain a unique local address and port, as defined in the /etc/hosts and the standard non-secure connection port (8471) 
      
      
   2. Modify each connect= line with your IBMi system name pointing to a secure port 9471
      
   3. Set CAFile to the path of the certificate text file.
      
      Example: 
      
      CAFile = /tmp/certificate.txt
      # Used for ODBC (isql, ...)
      [as-database]
      accept = DBSSL1.localhost:8471
      connect = remotedb1.domain:9471
      [as-database]
      accept = DBSSL2.localhost:8471
      connect = remotedb2.domain:9471
      [as-database]
      accept = DBSSL3.localhost:8471
      connect = remotedb3.domain:9471
      
      
   4. Run stunnel with root user: stunnel <CONFIG_FILE_PATH>
   5. May need to be registered as a service to start up with the server. Without the stunnel software, the connections to iSeries will not work.

Configure the odbc.ini

Configure odbc.ini for the SSL-enabled instances. See the example below:

[DBSSL1]
DRIVER=IBM i Access ODBC Driver 64-bit
SYSTEM=DBSSL1.localhost       *The local address defined in odbc.ini
QRYSTGLMT=*NOMAX
QUERYTIMEOUT=0
BLOCKFETCH=1
BLOCKSIZE=8192
COMPRESSION=1
TRACE=0
SSL=1                   **So far appears to do nothing but document the fact that the connection is secure

Configuring the Qlik Replicate Endpoint

The DB2i endpoint should be configured in Qlik Replicate. The option discussed in this document is to use the odbc.ini defined in the DSN, nevertheless, a “Use connection properties” option could also be used pointing to DBSSAL1.localhost, just like in odbc.ini:

Validation

Several steps can be taken to validate whether the connection is secure:

Trace outbound communications from Linux server

A tcpdump command can be used to determine whether the communication is successfully leaving the server to the correct destination port:

$ tcpdump -i eth2 `port 9471`

Display iSeries job details

Run the following command on the iSeries:

WRKOBJLCK userid *USRPRF ​

This will display the jobs using the service account userid.

You may need to scroll down to see a QSQSRVR which is the initial connection job, there is not much information to be extracted from this job.

Below this, you will see a QZDASOINIT and/or QZDASSINIT   

O = non-SSL connection (Port 8471) 
S = SSL connection (Port 9471)