IBM Support

Make SSL ODBC connections from Linux to Db2 for i

How To


Summary

This document outlines how to get the "IBM Access Client Solutions – Linux Application Package" to make SSL ODBC connections to Db2 for i. This example uses the open source "stunnel" package to provide the encryption.

Steps

Server-side steps (by using Digital Certificate Manager):

__ Ensure a CA Certificate exists on Server system. Create one if it does not.
__ Ensure a server certificate signed by the CA exists. Create one if it does not.
__ Assign the server certificate to the following IBM i server applications (host servers):

   Central Server
   Database Server
   Signon Server


__ Export the CA certificate from the IBM i server and copy to the client linux system. If you use FTP, ascii-mode conversion must be set (“ascii” command).

image-20190130165415-1

These steps are detailed in:

Digital Certificate Manager Getting Started

Client-side steps:
1. Install the stunnel package in Linux:
$ sudo yum install stunnel
Or
$ sudo apt install stunnel4
2. Store the exported CA certificate from the IBM i in file /etc/stunnel/YourSystemCA.txt.
$ cat /etc/stunnel/YourSystemCA.txt   
…should return:
-----BEGIN CERTIFICATE-----
[ actual certificate data here ]
-----END CERTIFICATE-----


3. Create an stunnel config file.
A sample is provided at /opt/ibm/iaccess/doc/iaccess.stunnel.config. This file can be placed anywhere if you want to start it manually. Otherwise, it can be placed under /etc/stunnel with a file name that ends in .conf (for example, /etc/stunnel/ibmi.conf). This approach allows it to be automatically started with systemd.

In our example, /etc/stunnel/stunnel.conf has the following:

# Enable client mode
client = yes
# Debug and Foreground are for testing / Uncomment for debugging stunnel problems
#debug = 5
#foreground = yes

# enable CA validation
CAFile = /etc/stunnel/YourSystemCA.txt
verify = 2

# The following sections contain the port maps for IBM i Access connections.
# Services:

# Used for conversion tables (cwbnltbl)
[as-central]
accept = 127.0.0.2:8470
connect = myibmi.example.com:9470 # Can also use IP addresses instead of hostnames

# Used for ODBC (isql, ...)
[as-database]
accept = 127.0.0.2:8471
connect = myibmi.example.com:9471

# Used for changing passwords (NEWPWD)
[as-signon]
accept = 127.0.0.2:8476
connect = myibmi.example.com:9476

odbc.ini (DSN config):

[ theODBC_SSLconnection ]
Description             = SSL-enabled IBM i DSN
Driver                  = IBM i Access ODBC Driver
System                  = 127.0.0.2

# other options may follow

Note: The System IP address must match that in the stunnel.conf file

Start the SSL tunnel with:

$ sudo stunnel /etc/stunnel/stunnel.conf
To verify it was successful, execute:
$ ps -eH | grep stunnel
  408 ?        00:00:00       stunnel

Test SSL connectivity with:

$ isql theODBC_SSLconnection YourUSRPRF YourUSRPWD
Test an SQL statement:
SQL> SELECT REMOTE_ADDRESS, REMOTE_PORT, LOCAL_PORT_NAME, LOCAL_PORT, JOB_NAME FROM QSYS2.NETSTAT_INFO Where LOCAL_PORT = 9471
+----------------------------------------------+------------+----------------+------------+-----------------------------+
| REMOTE_ADDRESS                               | REMOTE_PORT| LOCAL_PORT_NAME| LOCAL_PORT | JOB_NAME                    |
+----------------------------------------------+------------+----------------+------------+-----------------------------+
| 0.0.0.0                                      | 0          | as-database-s  | 9471       | 202620/QUSER/QZDASSINIT     |
| 9.160.8.167                                  | 50364      | as-database-s  | 9471       | 202620/QUSER/QZDASSINIT     |
| ::                                           | 0          | as-database-s  | 9471       | 202620/QUSER/QZDASSINIT     |
+----------------------------------------------+------------+----------------+------------+-----------------------------+
SQLRowCount returns -1
3 rows fetched
SQL>
 
The job allocated on the IBM i to handle the ODBC connection is QZDASSINIT. This is the Secure Sockets database host server prestart job.

Document Location

Worldwide

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Access Client Solutions - Linux Application Package","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Document Information

Modified date:
05 May 2022

UID

ibm10869822