Database Management

SSL connections from DPA to Oracle monitored instance

This article addresses the issue where a user is unable to connect to DPA to monitor an Oracle instance that requires SSL connections.

First published date

11/2/2018 8:08 PM

Last published date

11/5/2021 8:13 PM

Overview

This article addresses the issue where a user is unable to connect to DPA to monitor an Oracle instance that requires SSL connections.

 

Product section

Database Performance Analyzer

Resolution

See the appropriate section below:

  • The Oracle monitored instance is running on Amazon Relational Database Service (RDS)
  • The Oracle monitored instance is not running on Amazon RDS

If the Oracle monitored instance is running on Amazon RDS 

Task 1 (RDS): Patch the Oracle JDBC driver (DPA 12.0 and earlier only)

If you are using DPA 12.0 or earlier and you need the TLS 1.1 or TLS 1.2 protocols, you must first patch the Oracle JDBC driver that DPA uses. 

Note: Do not perform this task if you are using DPA 12.1 or later.

  1. Log in to your Oracle support account.
  2. Download Patch 19030178: ADD TLSV1.1 AND TLSV1.2 IN JDBC THIN from the Oracle site, and select Release: Oracle 11.2.0.4.0.
  3. Shut down DPA.
  4. Extract p19030178_112040_Generic.zip into a temporary directory.
  5. Navigate to the <temp_dir>\19030178\files\jdbc\lib directory.
  6. Extract the ojdbc5.jar file, which will result in an oracle subdirectory.
  7. Copy the entire oracle directory to <DPA_install_dir>\iwc\tomcat\webapps\iwc\WEB-INF\classes.

    The result should be:

    <DPA_install_dir>\iwc\tomcat\webapps\iwc\WEB-INF\classes\oracle\net\nt\TcpsConfigure.class

  8. Start DPA.

Task 2 (RDS): Export the Oracle certificate from the Oracle server 

  1. Download the Amazon RDS root CA certificate.
  2. Convert it to DER format using openssl.
    1. For Windows, download openssl (© 2019 Slashdot Media, available at https://sourceforge.net, obtained on March 5, 2019).
    2. Open a command prompt as Administrator.
    3. Set  
      OPENSSL_CONF=<openssl_path>\bin\openssl.cfg
    4. In the <openssl path>\bin directory, issue the following command: 
      openssl x509 -outform der -in rds-ca-2015-root.pem -out rds-ca-2015-root.der
  3. In the <openssl path>\bin directory, verify that there is a file called:

    rds-ca-2015-root.pem-out rds-ca-2015-root.der

Task 3 (RDS): Make DPA trust the Oracle certificate by adding it to the cert trust store 

You have several options for importing the certificate.

Import the certificate using DPA

If you have DPA 2020.2 or later, use the DPA Trusted Certificate Management options to import the certificate to the DPA trust store. 

The imported certificate will be available globally. DPA will be able to connect to any other database instance that accepts this server certificate. No further configuration is required.

Configure DPA to use a server certificate for a single database connection

DPA allows having an SSL server certificate configured for a single database connection. To enable this functionality, set the following properties in the Advanced connection properties field of the Registration Wizard or Update Connection Wizard:

  • The path to a custom trust store where server certificate is stored
  • The password
  • The trust store type

For example: javax.net.ssl.trustStore=C:\certificates\oracle\oratruststore.jks;javax.net.ssl.trustStorePassword=changeit;javax.net.ssl.trustStoreType=JKS

Note: An SSL configuration for a single database connection overrides all default DPA SSL properties. To use the “Verify-Full” SSL mode, you must manually add the property oracle.net.ssl_server_dn_match=true. So, the full set of connection properties for enabling the “Verify-Full” SSL mode look like this:

javax.net.ssl.trustStore=C:\certificates\oracle\oratruststore.jks;javax.net.ssl.trustStorePassword=changeit;javax.net.ssl.trustStoreType=JKS;oracle.net.ssl_server_dn_match=true

Import the certificate using the Portecle utility

If you have DPA 2019.4 or earlier:
  1. Download and install Portecle (© 2019 Slashdot Media, available at https://sourceforge.net, obtained on March 5, 2019).

  2. Open the Portecle utility as an Administrator.

  3. Click File > Open keystore file, and then choose one of the following options:
    • If you have a custom trust store configured in system.properties in the javax.net.ssl.trustStoreproperty, locate this trust store file.
    • For an embedded java cacerts trust store, go to one of the following locations and use the password changeit.

      Note: If you use this option, you will have to reimport the certificate when you upgrade DPA.

      • Windows: <DPA home>/iwc/jre/lib/security/cacerts
      • Linux: <DPA home>/iwc/jre_linux/lib/security/cacerts

    • For a DPA standard trust store, go to the following location and use the password changeit:

      <DPA home>/iwc/tomcat/ignite_config/security/dpa-truststore.jks

  4. Click Tools -> Import Trusted Certificate -> Import server certificate, and locate your Oracle server certificate.
  5. Confirm that you trust the certificate and proceed with the import.
  6. After the certificate is imported, choose File > Save keystore.
  7. Close Portecle.
  8. Start DPA.

Import the certificate using the keytool command

If you have DPA 2019.4 or earlier:
  1. Use the keytool utility in the following directory:
    • Windows: <dpa_home>\iwc\jre\bin\
    • Linux: <dpa_home>/iwc/jre_linux/bin/

  2. Run the following command:

    <path_to_keytool>/keytool -import -keystore <path_to_trust_store> -alias <specify_alias_for_certificate> -file <path_to_Oracle_certificate> -storepass <password_to_trust_store>

    The <path_to_trust_store> and <password_to_trust_store> are the same as specified above, depending on the option you choose.

Task 4 (RDS): If you are using Java 1.8, set the Java policy jars to unlimited 

If you are using strong encryption (key size is 256 or greater), then the Java policy jars must be set to unlimited. If you are using Java 1.8, complete the following steps. (This is enabled by default for later versions.)

  1. Go to one of the following locations:
    • Windows: <DPA_home>\iwc\jre\lib\security
    • Linux: <DPA_home>/iwc/jre_linux/conf/security
  2. Open the java.security file in a text editor.
  3. Find the crypto.policy=unlimited row and uncomment it.
  4. Restart DPA for the changes to take effect.

Task 5 (RDS): Register the Oracle database instance in DPA 

When you register the database instance in DPA:

  1. On Step 2 of the database registration wizard, select the TNS Connect Descriptor option.

  2. In the Connect Descriptor field, specify tcps as the protocol. For example:

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=<host_name>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))

If the Oracle monitored instance is NOT running on Amazon RDS 

Task 1 (non-RDS): Create a self-signed wallet and export it as a certificate 

  1. On the Oracle server, create an empty wallet with auto-login enabled: 
    >orapki wallet create -wallet "<wallet_location_path>" -auto_login

    Two files are created in the specified directory: cwallet.sso and ewallet.p12

  2. Add a self-signed certificate to the wallet (a new pair of private/public keys will be created):

    >orapki wallet add -wallet "<wallet_location_path>" -dn "CN=<your_server_name>" -keysize 2048 -self_signed -sign_alg sha -validity 3650 -pwd <your_wallet_password>

  3. Export the wallet as a certificate:

    >orapki wallet export -wallet "<wallet_location_path>”  –dn "CN=< your_server_name >" –cert "<certificate_location_path>\<your_certificate_name>.cert"

Task 2 (non-RDS): Configure your Oracle server to enable SSL connection 

  1. Enter the following settings in the listener.ora file:

    SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = <wallet_location_path>)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <your_host>)(PORT = <your_tcp_port>))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = <your_host>)(PORT = <your_tcps_port>))
    )

  2. Enter the following settings in the sqlnet.ora file:

    SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, NTS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = <wallet_location_path>)
    )
  )
SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_128_GCM_SHA256, SSL_RSA_WITH_AES_128_CBC_SHA256, SSL_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256, SSL_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256)
NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME)

  3. Enter the following settings in the tnsnames.ora file: 

    ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <your_host>)(PORT = <your_tcp_port>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = <your_host>)(PORT = <your_tcp_port>))
 
ORCLSSL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = <your_host>)(PORT = <your_tcps_port>))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
LISTENER_ORCLSSL =
  (ADDRESS = (PROTOCOL = TCPS)(HOST = <your_host>)(PORT = <your_tcps_port>))

  4. Test your SSL connection to the Oracle instance using sqlplus client:

    >sqlplus "<login>/<password>@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = <your_host>)(PORT = <your_tcps_port>)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ORCL)))"

Task 3 (non-RDS): Change the DPA ojdbc5 driver 

To use TLS 1.2 protocol, you must first change the standard DPA ojdbc5 driver.

  1. To be able to connect to an Oracle 12.2 instance, download ojdbc8.jar driver from:

    https://www.oracle.com/technetwork/d...ex-091264.html (© 2019 Oracle, available at https://www.oracle.com, obtained on March 5, 2019)

  2. Follow the instructions in Replace the Oracle JDBC driver with a different version in DPA 

Task 4 (non-RDS): Make DPA trust the Oracle certificate by adding it to the cert trust store 

You have several options for importing the certificate.

Import the certificate using DPA

If you have DPA 2020.2 or later, use the DPA Trusted Certificate Management options to import the certificate to the DPA trust store. 

The imported certificate will be available globally. DPA will be able to connect to any other database instance that accepts this server certificate. No further configuration is required.

Configure DPA to use a server certificate for a single database connection

DPA allows having an SSL server certificate configured for a single database connection. To enable this functionality, set the following properties in the Advanced connection properties field of the Registration Wizard or Update Connection Wizard:

  • The path to a custom trust store where server certificate is stored
  • The password
  • The trust store type

For example: javax.net.ssl.trustStore=C:\certificates\oracle\oratruststore.jks;javax.net.ssl.trustStorePassword=changeit;javax.net.ssl.trustStoreType=JKS

Note: An SSL configuration for a single database connection overrides all default DPA SSL properties. To use the “Verify-Full” SSL mode, you must manually add the property oracle.net.ssl_server_dn_match=true. So, the full set of connection properties for enabling the “Verify-Full” SSL mode look like this:

javax.net.ssl.trustStore=C:\certificates\oracle\oratruststore.jks;javax.net.ssl.trustStorePassword=changeit;javax.net.ssl.trustStoreType=JKS;oracle.net.ssl_server_dn_match=true

Import the certificate using the Portecle utility

If you have DPA 2019.4 or earlier:
  1. Download and install Portecle (© 2019 Slashdot Media, available at https://sourceforge.net, obtained on March 5, 2019).

  2. Open the Portecle utility as an Administrator.

  3. Click File > Open keystore file, and then choose one of the following options:
    • If you have a custom trust store configured in system.properties in the javax.net.ssl.trustStoreproperty, locate this trust store file.
    • For an embedded java cacerts trust store, go to one of the following locations and use the password changeit.

      Note: If you use this option, you will have to reimport the certificate when you upgrade DPA.

      • Windows: <DPA home>/iwc/jre/lib/security/cacerts
      • Linux: <DPA home>/iwc/jre_linux/lib/security/cacerts

    • For a DPA standard trust store, go to the following location and use the password changeit:

      <DPA home>/iwc/tomcat/ignite_config/security/dpa-truststore.jks

  4. Click Tools -> Import Trusted Certificate -> Import server certificate, and locate your Oracle server certificate.
  5. Confirm that you trust the certificate and proceed with the import.
  6. After the certificate is imported, choose File > Save keystore.
  7. Close Portecle.
  8. Start DPA.

Import the certificate using the keytool command

If you have DPA 2019.4 or earlier:
  1. Use the keytool utility in the following directory:
    • Windows: <dpa_home>\iwc\jre\bin\
    • Linux: <dpa_home>/iwc/jre_linux/bin/

  2. Run the following command:

    <path_to_keytool>/keytool -import -keystore <path_to_trust_store> -alias <specify_alias_for_certificate> -file <path_to_Oracle_certificate> -storepass <password_to_trust_store>

    The <path_to_trust_store> and <password_to_trust_store> are the same as specified above, depending on the option you choose.

Task 5 (non-RDS): If you are using Java 1.8, set the Java policy jars to unlimited 

If you are using strong encryption (key size is 256 or greater), then the Java policy jars must be set to unlimited. If you are using Java 1.8, complete the following steps. (This is enabled by default for later versions.)

  1. Go to one of the following locations:
    • Windows: <DPA_home>\iwc\jre\lib\security
    • Linux: <DPA_home>/iwc/jre_linux/conf/security
  2. Open the java.security file in a text editor.
  3. Find the crypto.policy=unlimited row and uncomment it.
  4. Restart DPA for the changes to take effect.

Task 6 (non-RDS): Register the Oracle database instance in DPA 

When you register the database instance in DPA:

  1. On Step 2 of the database registration wizard, select the TNS Connect Descriptor option.
  2. In the Connect Descriptor field, specify tcps as the protocol. For example:

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=<host_name>)(PORT=<port>))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <service_name>)))

    Alternatively, you can use:

    (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = <your_host>)(PORT = <port>))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <service_name>)))

  3. If you imported an Oracle certificate to the DPA standard trust store, click the Advanced Connection Properties link and provide the path to dpa-truststore in the Connection Properties field:

    javax.net.ssl.trustStore=<DPA_install_dir>/iwc/tomcat/ignite_config/security/dpa-truststore.jks;javax.net.ssl.trustStoreType=JKS;javax.net.ssl.trustStorePassword=changeit 



    Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.