Stelo Technical Documents

SQDR and SQDR Plus: Configuring SSL

Last Update:17 July 2024
Product: SQDR & SQDR Plus
Version: 4.0 and later
Article ID: SQV00PL047

Abstract

This technical document contains an overview and documentation references for configuring SSL (Secure Sockets Layer) for encrypted communication between the various components of Stelo Data Replicator and source databases.

Some documentation may use the newer term TLS (Transport Layer Security); in the context of Stelo documentation, the terms may be used interchangeably.

The documentation below is oriented towards running SQDR on Windows; contact Stelo support for assistance when running SQDR on Linux.

Solution

• Overview
  
• Tier 2 (Agent) to Tier 1 (IBM i source)
  
• Tier 2 (Agent) to Tier 1 (Microsoft SQL Server source)
  
• Tier 2 (Agent) to Tier 1 (IBM DB2 LUW source)
• Tier 2 (Agent) to Tier 1 (PostgreSQL)
  
• Tier 3 (SQDR) to Tier 1 (IBM i source)
  
• Tier 3 (SQDR) to Tier 1 (Microsoft SQL Server source)
  
• Tier 3 (SQDR) to Tier 1 (DB2 LUW source)
  
• Tier 3 (SQDR) to Tier 1 (Oracle source)
• Tier 3 (SQDR) to Tier 1 (PostgreSQL)
• Tier 3 (SQDR) to Tier 2 (Agent and staging database)
  
• Tier 3 (SQDR) to Tier 4 (Destination)
  
• Administrator workstation (browser) to Tier 2 (Control Center)
• Databricks to Tier 2
  
• StarSQL SSL details
• Obtaining a Server Certificate from LetsEncrypt

Overview

The SQDR architecture defines the following tiers:

Tier 1 - source database system (e.g. DB2 for i, DB2 LUW, Informix, SQL Server, Oracle, MySQL, PostgreSQL)
Tier 2 - SQDR Plus (incremental staging agent)
Tier 3 - SQDR replication service
Tier 4 - destination database system
For details, see the Overview in the SQDR Plus Quick Start Guide.

Note that these tiers can be combined in various ways - for example, Tiers 2 and 3 are often run on the same VM, typically dedicated for SQDR. When WAN networks are involved, placing Tiers 3 and 4 on the same system or the same network often enhances performance. Encrypting communication is typically not a requirement when tiers are colocated on the same system.

This information applies to any SSL connection, including TLS1.2.. Restricting communication to TLS 1.2 is usually handled at the operating system level and negotiated when the client connects - i.e. it is transparent to Stelo software. We have documented configuring TLS 1.2 restriction where appropriate.

Tier 2 (Agent) to Tier 1 (IBM i source)

See these sections in the SQDR Control Center Help file, which is installed with the product and available in the Info Center:
Configuring SSL on DB2 for i
and
Configuring SSL to DB2 for i Source

Briefly:

• After configuring SSL on the IBM i server, export the CA (Certificate Authority) certificate to a text file.
• In SQDR Control Center, go to the Certificate Management screen and add the certificate.
• If you are creating a new agent, select the SSL checkbox in the Add Agent Wizard.
• To change an existing agent to use SSL, edit the agent configuration, add the property useSSL=true, and add secure=true to the sourceDbUrl property. Save the configuration, which restarts the agent.

To use TLS 1.2, see the IBM tech note IBM i Access Clients and TLS1.2 connections.

Additional information for Configuring SSL on Db2 for i

To access DCM (Digital Certificate Manager) from New Navigator:

• After logging in, double-click on the system name, resulting in about a dozen icons in the left column. Select Bookmarks (the next to last icon) and choose Digital Certificate Manager.

Alternately, you can log in directly to DCM by browsing to https://myhost:2006/dcm.

To verify that SSL has been configured, it is not sufficient to use netstat to verify that there is a listener on port 448. Perform these steps to confirm that a valid certificate exists and has been assigned to the appropriate services:

• Log on to DCM
• Open the *SYSTEM certificate store. If there is no *SYSTEM certificate store, then the system has not been configured for SSL.
• Verify that there is a valid (unexpired) Server/Client certificate.
• Select Manage Application Definitions and verify that the valid certificate has been assigned to the appropriate services (IBM i DDM/DRDA Server and the host servers used by iAccess and JTB - Central Server, Database Server, Data Queue Server, Remote Command Server, Signon Server, Host Servers, File Server)..

Tier 2 (Agent) to Tier 1 (Microsoft SQL Server source)

See Configuring SSL to SQL Server Source in the SQDR Control Center Help file.

For TLS 1.2 information, see the Microsoft document TLS 1.2 support for Microsoft SQL Server.

Tier 2 (Agent) to Tier 1 (IBM DB2 LUW source)

See SQDR Plus: Configuring Db2 LUW for SSL for instructions on configuring SSL on the source and enabling SSL on Tier 2.

Tier 2 (Agent) to Tier 1 (PostgreSQL)

See SSL from Tier 2 (Stelo Capture aka SQDR Plus) in the PostgreSQL Tips tech doc.

Tier 3 (SQDR) to Tier 1 (IBM i source)

See the section Configuring SSL on DB2 for i in the SQDR Control Center Help file,

Briefly:

If you are using StarSQL and connection strings, modify the Source and change the port & Netlib parameters. You will have to re-enter the credentials.

Server=MYRDB;HostName=myas400;Netlib=SQSSL.DLL;Port=448;IsolationLevel=2;PkgColID=SQDR

If you are using StarSQL and an ODBC data source:

1. Start ODBC Administrator.
   
2. Select the DSN.
   
3. Go to the Network panel.
   
4. Change the port & select SSL/TLS from the dropdown for Network Options.
   
5. Select Summary to save the change.

If you are using iAccess rather than StarSQL, import the CA certificate with CWBCOSSL or the iKeyMan application and change the connection string or DSN - see the SQDR Control Center help for details.

In all the above cases, either restart the SQDR service or kill any existing (non-SSL) connections.

TLS 1.2 is enabled by default in newer versions of Windows (Windows Server 2012/Windows 8 & later). If you are using an older version of Windows (Windows Server 2008R2 or Windows 7), you can enable TLS 1.2 by using regedit to add the following registry key & entry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client
DWORD name: DisabledByDefault
DWORD value: 0

See the Microsoft document Update to enable TLS 1.1 and TLS 1.2 as default secure protocols in WinHTTP in Windows.

Tier 3 (SQDR) to Tier 1 (Microsoft SQL Server source)

See Configuring SSL to SQL Server Source in the SQDR Control Center Help file.

For TLS 1.2 information, see the Microsoft document TLS 1.2 support for Microsoft SQL Server.

Tier 3 (SQDR) to Tier 1 (DB2 LUW source)

If you are using StarSQL, configure an SSL connection to the source as described in the Tier 3 (SQDR) to Tier 1 (IBM i source) section.

If you are using the IBM DB2 ODBC driver, use IBM GSKit to create a keystore and import the CA certificate exported from the source system, as described in SQDR Plus: Configuring Db2 LUW for SSL. When configuring the source, use a connection string similar to:

Hostname=mytier1;port=50448;Database=mydb;Protocol=TCPIP;
Authentication=SERVER;SECURITY=SSL;
Ssl_client_keystoredb=C:\SSL\keystore.kdb; Ssl_client_keystash=C:\SSL\keystore.sth

Tier 3 (SQDR) to Tier 1 (Oracle source)

Configuring an SSL connection to Oracle is documented in the topic "Oracle SSL Connections" in the drmgr.chm Help file. The technique differs depending on whether you are using the "SQDR Oracle 8" driver or Oracle Instant Client.

Tier 3 (SQDR) to Tier 2 (Agent and staging database)

In many cases, these tiers are located on the same machine, so unencrypted communication is not a requirement..

If tier 2 & tier 3 are on separate machines, see SQDR Plus: Configuring Db2 LUW for SSL.

To enforce TLS 1.2 in DB2 LUW (for the tier 2 staging database), use

db2 update dbm cfg using ssl_versions TLSv12

Tier 3 (SQDR) to Tier 1 (PostgreSQL)

See SSL from Tier 3 (Stelo Apply aka SQDR) in the PostgreSQL Tips tech doc.

Tier 3 (SQDR) to Tier 4 (Destination)

Configuring encryption to the destination is dependent on the DBMS type of the destination and outside the scope of this document.

Administrator workstation (browser) to Tier 2 (Control Center)

Tto configure secure access to the Control Center (i.e. an administrator using a browser from another machine), see SQDR Plus: Configuring SSL for Jetty (SQDR Control Center)

This document includes a section on enforcing the use of TLS 1.2.

Databricks to Tier 2

See Using Stelo Data Lake Sink Connector for Azure Databricks.

StarSQL and StarPipes SSL details

To enable an SSL listener on StarPipes, you need to request and install a server certificate (similar to that used by web servers); see Certificate Management in the StarPipes documentation..

For technical details and additional configuration options for using StarSQL or StarPipes and SSL, see SSL hints for StarSQL and StarPipes on Windows.

Obtaining a Server Certificate from LetsEncrypt

A certificate authority (CA) is a trusted entity that issues Secure Sockets Layer (SSL) certificates. This could be either a public CA, such as GlobalSign, Sectigo, or DigiCert, or an in-house private CA, such as Windows Active Directory Certificate Services (Windows Certificate Server).

For applications such as StarPipes & Jetty and database servers such as Db2 LUW, it is necessary to use a server certificate (this is the same type of certificate used by web servers). When a client connects to the server, the server presents its server certficate and the client has to decide whether to trust the certificate. In the case of a certificate issued by a private CA, it is often necessary to import the CA certificate into the certificate store (aka keystore) of the client. In the case of a certificate issued by a public CA, no additional configuration is needed and setup is simplified.

Let's Encrypt is a non-profit certificate authority run by Internet Security Research Group (ISRG) that provides X.509 certificates for Transport Layer Security (TLS) encryption at no charge. While its automated tools are designed for issuing certificates for use on public web servers, manual mode using DNS challenge can be used to issue certificates for Stelo-related services such as StarPipes, Jetty, and Db2 LUW. However, manually-generated certificates are valid for only 90 days, so this approach is more appropriate for a trial or test environment than a production setup. The use of a commercial CA with longer certificate lifespans is recommended for production.

Procedure

Create a CSR (Certificate Signing Request) on your server system. See the appropriate documentation for details.

Install certbot; it does not have to be on the server that you are issuing a certificate for. In our case, we installed certbot on a Linux system using

# dnf install epel-release -y
# dnf install certbot

Invoke certbot in manual mode specifying the CSR:

$ cd SSLworkdir
$ cp my_csr.csr .
$ certbot certonly --csr my_csr.csr --manual --preferred-challenges dns --config-dir . --work-dir . --logs-dir .

When using the dns challenge, certbot will ask you to place a TXT DNS record with specific contents under the domain name consisting of the hostname for which you want a certificate issued, prepended by _acme-challenge.

After the certificate is issued, copy it to the server and import it into the appropriate certificate store.

certbot is just one of many applications that can be used to request certificates from Let's Encrypt, many others are listed at the ACME Client Implementations page, but Stelo support has not explored these options.