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.

Objective

Since this document was originally created, the "IBM Access Client Solutions – Linux Application Package" has been enhanced to support OpenSSL (starting with version 1.1.0.27).
For instructions to use OpenSSL instead of stunnel, please see document ACS ODBC driver for Linux now supports OpenSSL 

Environment

IBM i
Linux

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:
20 April 2023

UID

ibm10869822

Page Feedback