Stelo Technical Documents

SSL/TLS Hints for StarSQL/UNIX


Last Update: 26 December 2022
Product: StarSQL/UNIX
Version: 6.4x & later
Article ID: SQV00SQ066

Abstract

This technical document provides details and hints regarding the SSL (Secure Sockets Layer) implementation used by StarSQL/UNIX. The implementation uses OpenSSL, a standard part of the Linux operating system that facilitates the use of Secure Sockets Layer (SSL) and/or Transport Layer Security (TLS) encryption.

Information on configuring SSL on the host computer can be found in the Host Preparation chapter of the StarSQL for UNIX User Guide (PDF); refer to the subsections Configuring SSL for Db2 for z/OS, Configuring SSL for Db2 for i, and Configuring SSL for Db2 for LUW.

See below for detailed information on the following topics:

Solution

Prerequisites

  • StarSQL 6.44: Red Hat/CentOS/Oracle Linux 7.7 & OpenSSL 1.0.2. If you are using a later version (e.g. Red Hat/Oracle Linux 8.x) install the compat-openssl10 compatibility package.or later
  • StarSQL 6.5x & later: Red Hat/Oracle Linux 8.7 & OpenSSL 1.1. If you are using a later version (e.g. Red Hat/Oracle Linux 9.x) install the compat-openssl11 compatibility package.
  • Other Linux distributions may work.
  • p11-kit-trust
  • TLS 1.2 & later
  • Host server and Certificate Authority (CA) certificates signed with SHA-2 algorithm (SHA-1 certificates not supported)

Configuring StarSQL for SSL

To create a StarSQL ODBC data source utilizing SSL, edit $HOME/.odbc.ini or /etc/odbc.ini: add SSL=Y and enter the appropriate host SSL port. e.g.

[MyDSN]
Server=MYRDB
Driver=StarSQL (64-bit)
HostName=myhost
Port=448
PkgColId=STARSQL
SSL=Y

In addition, you can use the optional property SSLOptions in the ODBC data source. The value of this parameter is a 64-bit hexadecimal string that can be used to supply advanced options for SSL/TLS communications. Contact Stelo Support for assistance. For details, see SSL_CTX_set_options in the OpenSSL documentation.

Importing the Certificate Authority (CA) certificate

If the Db2 host is using a server certificate issued by a public Certificate Authority known by OpenSSL, you can skip this section.

However, if the the certificate is self-signed or issued by an internal Certificate Authority, you will need to export the CA certificate from the host or CA and import it into the OpenSSL certificate store. Failure to do this will result in the following error:

SQLState = 08001, [unixODBC][StarSQL][StarSQL CLI Driver][OPENSSL][ERR_LIB_SSL][SSA_SSL_R_CERTIFICATE_VERIFY_FAILED] 0x114000086

Note that this error may be the result of other certificate-related conditions; see Troubleshooting..

 

Exporting the CA certificate (Db2 for i)

To configure a System i host system to use the Secure Sockets Layer (SSL) protocol you must have the following components:

  • Digital Certificate Manager - option 34 of 5722-SS1 (v5r4), 5761-SS1 (6.1), or 5770-SS1(7.x)
  • TCP/IP Connectivity Utilities - 5722-TC1(v5r4), 5761-TC1 (6.1), or 5770-SS1 (7.x)
  • IBM HTTP Server - 5722-DG1 (v5r4), 5761-DG1 (6.1) or 5770-DG1 (7.x)

Following are general procedures for configuring SSL on the IBM i host. Refer to your IBM documentation for details..

  1. Start the Admin HTTP instance. To verify that it is running, enter WRKACTJOB JOB(ADMIN). If it is not running, start it with STRTCPSVR SERVER(*HTTP) HTTPSVR(*ADMIN).
  2. Use a browser and the URL https://myas400:2001 to connect to the Digital Certificate Manager. On i 6.1 and later, this URL will redirect you to IBM Navigator for i, running on port 2005; from there, select IBM i Tasks Page to see the previous version of the 2001 port tasks, which includes the Digital Certificate Manager. You can also connect directly to DCM at http://myas400:2001/QIBM/ICSS/Cert/Admin/qycucm1.ndm/main0.
  3. Create a local Certificate Authority or obtain a certificate from a public Internet Certificate Authority.
  4. Create a *SYSTEM certificate store.
  5. Use Manage Applications to assign a server certificate to the OS/400 DDM/DRDA server.
  6. Select Install Local CA Certificate on Your PC from the left column of tasks. You may need to return to the main IBM Navigator for i page and re-enter DCM before Install Local CA Certificate to your PC is visible.
  7. Select Copy and paste certificate; this will display the CA certificate in Base64-encoded ASCII data format. Select the contents of the certificate (all of the text from -----BEGIN CERTIFICATE----- through -----END CERTIFICATE-----) and save it in a text file with a .cer extension.

Exporting the CA certificate (Db2 for LUW)

Assuming that the GSK certificate store has been created and populated as described in Configuring Db2 LUW for SSL: enable SSL (certificate request), then export the CA certificate to a text file using:
"%GSK%\gsk8capicmd_64" -cert -extract -db "%SERVER%.kdb" -pw "%PASSWORD%" -label "%CALABEL%" -target %SERVER%.arm -format ascii -fips

Important: make sure that the certificates are created using SHA-2 (SHA256) algorithm; older certificates created with SHA-1 will be rejected.

Exporting the CA certificate (Db2 for z/OS)

To generate and export a CA certificate in RACF, use the RACDCERT GENCERT and EXPORT commands with the CERTAUTH option:

RACDCERT CERTAUTH GENCERT etc.
RACDCERT CERTAUTH EXPORT etc.

Copy the resulting ASCII file (.ARM format) to the Linux system.

For more information, see Chapter 11.2 Protecting data through DB2 SSL with digital certification of the IBM Redbook Security Functions of IBM DB2 10 for z/OS SG24-7959.

 

Importing the CA certificate into the OpenSSL certificate store

  1. If necessary, convert the CA certificate to PEM format e.g.

For .CER format (exported from IBM i DCM):

$ openssl x509 -in cert.cer -out cert.pem

For .ARM format (exported from Db2 LUW or z/OS):

$ openssl x509 -in cert.arm -out cert.pem

  1. Copy the file to /etc/pki/ca-trust/source/anchors/ and run update-ca-trust

# cp /tmp/cert.pem /etc/pki/ca-trust/source/anchors/
# update-ca-trust extract

To verify that the CA has been added to the trusted CA list, run the following command:

$ trust list | grep 'myCA'

Troubleshooting

General troubleshooting:

Make sure that you are using the correct values in the ODBC data source - e.g. hostname or IP, Port, Server name (RDB).

A hostname that doesn't resolve will result in:

SQLState = 08S01, [unixODBC][StarSQL][StarSQL CLI Driver]Communications link failure.

Specifying the wrong value for SERVER will result in:

SQLState = 52017, [unixODBC][StarSQL][StarSQL CLI Driver]Database server XXX not found.

Connecting to the SSL port without specifying SSL=Y will result in:

SQLState = 08001, [unixODBC][StarSQL][StarSQL CLI Driver]Non-DRDA datastream received from host.

Connecting to the non-encrypted port while specifying SSL=Y may result in a hang.

Certificate troubleshooting:

Most certificate-related issues will result in this general error message:

SQLState = 08001, [unixODBC][StarSQL][StarSQL CLI Driver][OPENSSL][ERR_LIB_SSL][SSA_SSL_R_CERTIFICATE_VERIFY_FAILED] 0x114000086

Check for the following:

  • Verify that the Certificate Authority's certificate has been added to the OpenSSL certificate store. Issue the following command to list the trusted authorities; recently-added certificate authorities will appear at the beginning:

$ trust list

  • Use the openssl command to examine the CA certificate and the server certificate offered by the host. Verify that they have not expired, and that they are using SHA256 or better. Also confirm that there are at least 2 certificates in the chain and the Depth is greater than 0; a self-signed certificate with a Depth=0 will not be accepted.

To examine the CA certificate that you used above when adding it to the trust list:

$ openssl x509 -in cert.pem -text

To examine the certificate offered by the host:

$ openssl s_client -connect myhost.mydomain.com:448

This will display information about the server certificate, including the depth and the chain. It will also display an error message explaining why the certification validation failed. Some of the messages we have encountered are

error:num=18:self signed certificate
error:num=19:self signed certificate in certificate chain
error:num=20:unable to get local issuer certificate
error:num=21:unable to verify the first certificate

The output also contains the server certificate in Base64-encoded ASCII data format. To display the server certificate, send the resulting output to the openssl x509 command:

$ openssl s_client -connect myhost.mydomain.com:448 | openssl x509 -text

Using openssl s_client -connect waits for user input; enter Q or control-C to terminate the connection, or pipe Q into the command:

$ echo "Q" | openssl s_client -connect myhost.mydomain.com:448