Enabling TLS server certificate validation to external database

The local and remote ETL Engine Servers of TrueSight Capacity Optimization can communicate with the following external databases:

  • Oracle
  • PostgreSQL
  • SQL Server
Info

The connection between Perl and SQL Server database does not support TLS 1.2. Hence, communication from the following Perl-based ETLs to SQL Server is not TLS 1.2 compliant:

  • Generic - Database  Extractor
  • Generic - Columnar database  Extractor
  • Generic – Events SQL extractor
  • Generic – Object Relationship SQL extractor

You can use Transport Layer Security (TLS) 1.2 with server certificate validation to secure communication between these components. 

Before you begin

Ensure that you use the database versions that support TLS 1.2. For more information, see TLS-considerations-for-TrueSight-Capacity-Optimization.

For external Oracle database

Before you begin

  • Ensure that the Oracle database is configured in TLS 1.2 mode.
  • Ensure that TLS-compatible ojdbc7.jar  file exists in the  <Oracle client home>/jdbc/lib  directory. If not, you can copy the file from the Oracle Website.

Enable TLS 1.2 with server certificate validation

  1. Procure the Oracle database certificate from the system administrator of your organization if a self-signed certificate is used. Ensure that the certificate is in x509 format. For example, oracle.crt.
    If a certificate that is signed for the Oracle database by an enterprise certificate authority (CA) or a third-party CA as a trusted root authority is used, import this signed certificate to avoid importing of multiple signed certificates for all TLS connections.

  2. Save the procured certificate file in the following locations:

    Component

    Location

    Local ETL Engine Server

    <Local ETL Engine Server Installation Directory>/secure

    Remote ETL Engine Server

    <Remote ETL Engine Server Installation Directory>/secure

  3. Log on to the computer where the Server is installed. The keytool utility that is used to import the certificates is present in the <Server Installation Directory>/jre/bin directory. Add this directory path to the PATH environment variable by running the following command:

    export PATH=<Server Installation Directory>/jre/bin:$PATH

  4. Go to <Server Installation Directory>/secure directory and import the procured certificates by running the following command:

    keytool -importcert -trustcacerts -file <path>/oracle.crt -keystore cotruststore.ts -alias <CertificateName>
    Parameter reference
    • oracle.crt is the name of the procured Oracle certificate. If the name of this certificate is different, use the relevant file name in the keytool command.
    • Replace all instances of <CertificateName> by the appropriate certificate name.
  5. When you are prompted, enter the password for the keystore.
  6. When you are prompted to trust the certificate enter Yes.

The communication between the external Oracle database and the ETL Engine Servers is now TLS 1.2 enabled with server certificate validation.

For external PostgreSQL database

  1. Procure the PostgreSQL database certificate from the system administrator of your organization if a self-signed certificate is used. Ensure that the certificate is in x509 format. For example, postgresql.crt.
    If a certificate that is signed for the PostgreSQL database by an enterprise certificate authority (CA) or a third-party CA as a trusted root authority is used, import this signed certificate to avoid importing of multiple signed certificates for all TLS connections.

  2. Save the procured certificate file in the following locations:

    Component

    Location

    Local ETL Engine Server

    <Local ETL Engine Server Installation Directory>/secure

    Remote ETL Engine Server

    <Remote ETL Engine Server Installation Directory>/secure

  3. Log on to the computer where the Server is installed. The keytool utility that is used to import the certificates is present in the <Server Installation Directory>/jre/bin  directory. Add this directory path to the PATH environment variable by running the following command:

    export PATH= <Server Installation Directory>/jre/bin:$PATH

  4. Go to <Server Installation Directory>/secure directory and import the procured certificates by running the following command:

    keytool -importcert -trustcacerts -file <path>/extdb.crt -keystore cotruststore.ts -alias <CertificateName>
    Parameter reference
    • postgresql.crt is the name of the procured PostgreSQL database certificate. If the name of this certificate is different, use the relevant file name in the keytool command.
    • Replace all instances of <CertificateName> by the appropriate certificate name.
  5. When you are prompted, enter the password to access the keystore.
  6. When you are prompted to trust the certificate enter Yes.

The communication between the external PostgreSQL database and the ETL Engine Servers is now TLS 1.2 enabled with server certificate validation.

For external SQL Server database

You can configure only Java-based database extractors to be TLS compliant.

  1. Procure the Certificate Authority (CA) signed certificate from the system administrator of your organization. Ensure that the certificate is in x509 format. For example, extdatabase.crt.

  2. Save the procured certificate file in the following locations:

    Component

    Location

    Local ETL Engine Server

    <Local ETL Engine Server Installation Directory>/secure

    Remote ETL Engine Server

    <Remote ETL Engine Server Installation Directory>/secure

  3. Log on to the computer where the Server is installed. The keytool  utility that is used to import the certificates is present in the  <Server Installation Directory>/jre/bin  directory. Add this directory path to the PATH environment variable by running the following command:

    export PATH= <Server Installation Directory>/jre/bin:$PATH

  4. Go to <Server Installation Directory>/secure directory and import the procured certificates by running the following command:

    keytool -importcert -trustcacerts -file <path>/extdb.crt -keystore cotruststore.ts -alias <CertificateName>
    Parameter reference
    • extdb.crt is the name of the procured SQL Server database certificate. If the name of this certificate is different, use the relevant file name in the keytool command.
    • Replace all instances of <CertificateName> by the appropriate certificate name.
  5. When you are prompted, enter the password to access the keystore.
  6. When you are prompted to trust the certificate enter Yes.

The communication between the external SQL Server database and the ETL Engine Servers is now TLS 1.2 enabled with server certificate validation.

Where to go from here

Adding-external-database-connections 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*