Stelo Technical Documents

SQDR and SQDR Plus: Configuring SSL

Last Update: 23 May 2023
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 StarQuest Data Replicator and source databases.

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

The documentation below is oriented towards running SQDR on Windows; contact StarQuest 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)
  
• StarSQL SSL details

Overview

The SQDR architecture defines the following tiers:

Tier 1 - source database system (e.g. DB2 for i, DB2 LUW, Informix, SQL Server)
Tier 2 - SQDR Plus (incremental staging agent) - Windows or Linux
Tier 3 - SQDR replication service - Windows
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 Windows 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 StarQuest 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.

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. Then export the CA certificate, import the certificate into the certificate store used by SQDR Plus, and modify the sourceDbUrl property to use SSL and the appropriate port.

To enforce TLS 1.2 in DB2 LUW, use

db2 update dbm cfg using ssl_versions TLSv12

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.

StarSQL SSL details

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