...

• JS7 has provision for two levels of integration with an Oracle DBMS:
  • JS7 supports use of Oracle as the JS7 - Database. For this scenario see the JS7 - How to make JOC Cockpit connect to an Oracle database using Wallet® article.
  • JS7 provides job templates from JS7 - JITL Database Jobs that can be used to access Oracle databases.
    • Such jobs are executed by Agents, therefore the explanations below apply to the servers which Agents are operated for.

• For both scenarios users might prefer not to provide a user account and password for authentication with the DBMS from readable files.

  • The use of passwords is considered insecure when passwords are stored in clear text in external files or in job parameters.
  • JS7 offers JS7 - Use of Credential Store with JITL Jobs as an alternative way to store and to retrieve passwords.
  • The Oracle Wallet® provides a credential store keystore to connect to an Oracle database without specifying a user account and password from parameters or from readable files.
• The following JITL Jobs can be used are prepared for use with Oracle Wallet®:
  • JS7 - JITL SQLExecutorJob: Standard JDBC Job for any DBMS
  • JS7 - JITL PLSQLJob: PL/SQL JDBC job for Oracle DBMS
  • JS7 - JITL SQLPLUSJob: SQL*Plus Command Line ClientJITL SQLPLUSJob: SQL*Plus Command Line Client

SOS does not accept any liability for use of JS7 with Oracle Wallet®. Configuration of Oracle Wallet® is the user's responsibility and can change based on the version of the DBMS. The following explanations offer an example how to integrate with Oracle 18c, the example is not authoritative and does not cover future versions of the DBMS. The database vendor's documentation offers authoritative instruction how to connect to Oracle Wallet® and how to analyze connection problems.

Oracle Wallet®

The Oracle Wallet® configuration is described in the Oracle documentation. At the time of writing the following links are available:

• Configuring clients to use the External Password Store see, for example, http://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm#CBHEHGCE
• An introduction to the technical configuration in https://www.oracle.com/technetwork/database/enterprise-edition/wp-oracle-jdbc-thin-ssl-130128.pdf
• Or as a more condensed version from the Oracle-Base web site e.g. in https://oracle-base.com/articles/10g/secure-external-password-store-10gr2
• The location of the docs depends on the specific Oracle version in use.

...

• Check the Oracle JDBC Driver version that ships with the JS7 release, see JS7 - Database, chapter: Individual JDBC Driver Versions. A newer JDBC Driver might be available for download from Oracle.
• Oracle JDBC Drivers that ship for release 18c of the DBMS are reported to work. Previous Oracle JDBC Driver releases, for example 12c, are reported not to work with Oracle Wallet® when used by JS7. If in doubt use the Oracle JDBC Driver version that matches the version of the DBMS.
• Users who want to use a specific version of the Oracle JDBC Driver can apply the following steps:
  • For on premises installations store the Oracle JDBC Driver's .jar file in the JS7_AGENT_HOME/lib/user_lib directory of the Agent installation directory.
  • When running JS7 Agent containers for Docker ® consider to store the Oracle JDBC Driver's .jar file in the JS7_AGENT_CONFIG_DIR/lib directory.

...

• The Oracle PKI libraries are required and have to match the version of the Oracle DBMS and Oracle JDBC Driver.
• The .jar files are provided by Oracle for download and are available from an Oracle Client installation, for example from:
  • ORACLE_HOME/jlib/oraclepki.jar
  • ORACLE_HOME/jlib/osdt_cert.jar
  • ORACLE_HOME/jlib/osdt_core.jar
• For on premises installations store the Oracle PKI libraries in the JS7_AGENT_HOME/lib/user_lib directory of the JS7 Agent installation directory.
• When running JS7 Agent containers for Docker ® consider storing the Oracle PKI libraries in the JS7_AGENT_CONFIG_DIR/lib directory.

...

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
  <property name="hibernate.connection.password"></property>   
  <property name="hibernate.connection.url">jdbc:oracle:thin:@/js7@js7?TNS_ADMIN=/home/js7/wallet</property>
  <property <property name="hibernate.connection.username"></property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle12cDialect</property>
  <property name="hibernate.show_sql">false</property>
  <property name="hibernate.connection.autocommit">false</property>
  <property name="hibernate.format_sql">true</property>
  <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
  <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
  <property name="hibernate.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

...

• Note the empty elements that are used for the account and password. Do not delete these elements from the Hibernate configuration file.
• The connection URL specifies js7 as as the key to an entry in the  tnsnames.ora configuration file and in the wallet wallet.
• The TNS_ADMIN URL parameter is used to specify the directory of the tnsnames.ora configuration file. JDBC Connections usually do not need this configuration file as connection details (Listener, Service Name, Service ID) are specified with the URL. However, due to use of the js7 key to the wallet in the URL it is preferable to manage connection details from a tnsnames.ora configuration file.
• In the above example this file is located in the /home/js7/wallet directory which is in fact the directory where the wallet is located. This location is not authoritative as the file can reside in any directory that is accessible to JOC Cockpit.
• Note that an sqlnet.ora configuration file is not used with the above setup of a JDBC connection.

...

The prerequisites for setting up the wallet are the same as explained above in the Prerequisites, Oracle Wallet® section.

• Add Add the location of the wallet to your sqlnet.ora configuration file, for example:
  
  • WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/js7/wallet)))
  • Additional entries will be required for this file, please check Oracle's documentation.
• This file is required by SQL*Plus and allows execution of the command line client like this: sqlplus /@js7. 
• js7 is the key for the tnsnames.ora configuration file which is used to identify the database connection settings. It is also used by the wallet to identify the matching credentials.

...