StarQuest Technical Documents

SQDR & SQDR Plus: Security Best Practices

Last Update: 08 December 2020
Product: SQDR & SQDR Plus
Version:5.10 and later
Article ID: SQV00PL052

Abstract

This technical document contains Security Best Practices tips and suggestions for SQDR and SQDR Plus.

Contents:

• Overview of the SQDR Architecture
  
• Operating System Updates
  
• Passwords
  
• User Authorities (Source Databases)
  
• User Authorities (Services on SQDR Platform)
  
• Antivirus Exemptions
  
• Firewalls
  
• Encryption
  
• Underlying Technologies
  

Overview of the SQDR Architecture

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.

Operating System Updates

Installing the latest Microsoft Windows Updates on the SQDR platform is recommended.

Also examine whether operating system or DBMS updates are needed on the systems hosting source & target DBMS systems..

Passwords

Password complexity is a well-known Security Best Practice and outside the scope of this document.

To avoid downtime and the need to reconfigure SQDR, we recommend that user ID's created for the purpose of SQDR be exempted from password expiration.

The password used for the SQDR Control Center (Derby) is stored in plain text on the SQDR system. We recommend using a non-sensitive password for this function. If desired, you can limit access to the plain text files where the password is stored, but determining the exact permissions needed may require some effort.

User Authorities (Source Databases)

Depending on the platform and the method of configuration, SQDR creates new or utilizes existing userID's on the source system. These userID's should be configured to have the minimum authorities required to perform their tasks and no more. Users created by the SQDR Plus "Add Agent" wizard will be created with the appropriate authorities. These requirements are documented in the following technical documents or Quick Start Guides:

• SQDR Plus IBM i User Authorities
  
• SQDR Plus IBM DB2 LUW Resource Utilitization
  
• SQDR Plus SQL Server User Authorities
  
• SQDR Plus Oracle User Authorities
  
• Refer to the appropriate Quick Start Guide for MySQL and Informix sources.

User Authorities (Services on SQDR Platform)

The default installation of SQDR typically runs the SQDR services as Local System Account and Db2 LUW as a local user db2admin. In addition, a local Windows user "sqdr" is used for access to the local Db2 LUW staging database. In some scenarios, you may choose to use domain user accounts instead of local user accounts.

Antivirus Exemptions

If an antivirus tool is installed on the SQDR platform, we recommend exempting the following directories, where antivirus tools have been known to interfere with the operations of SQDR and underlying technologies such as Db2 LUW:

C:\Program Files\StarQuest
C:\Program Files\IBM
C:\ProgramData\StarQuest
C:\ProgramData\IBM
S:\DB2
L:\DB2

Firewalls

The use of firewalls is recommended, limiting traffic to only the ports that are required.

Refer to the TCP/IP Port Usage table at the end of Quick Start Guide to Using SQDR Plus v4 - Worksheet. Select the Print button on this technical document and save as PDF to create a formatted table.

You should also evaluate what remote management functions are required. For example, if remote access to the SQDR Control Center (jetty) is not necessary, then you can block the ports used by the Control Center.

Encryption

Analyze traffic paths and decide where encryption is necessary, since it has a cost on performance & configuration overhead.

For instance, if tier 2 and 3 are colocated, it is not necessary to encrypt traffic between them since it does not leave the machine.

Refer to SQDR and SQDR Plus: Configuring SSL for an 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.

Underlying Technologies

The technical document SQDR Plus Product FAQs - Open Source and Proprietary Technologies used by StarQuest SQDR/SQDR Plus lists the technologies used by SQDR. The vendors of each of these technologies may have their own security recommendations, including configuration and updates. Contact StarQuest Support for advice before updating any of these items, as recent updates may not yet be certified for use with SQDR.