Secure password storage for SQL*Plus

Create the C:\Program Files\instantclient\mkstore.bat

@echo off
rem inspiration: https://andriydmytrenko.wordpress.com/2013/07/01/using-the-secure-external-password-store-with-instant-client/

setlocal

rem get the command line arguments
set args=
:loop
  if !%1==! goto :done
  set args=%args% %1
  shift
  goto :loop
:done

rem set classpath for mkstore - align this to your local SQLcl installation
set sqlcl="C:\Program Files\sqldeveloper\sqldeveloper\lib"
set classpath=%sqlcl%\oraclepki.jar
set classpath=%classpath%;%sqlcl%\osdt_core.jar
set classpath=%classpath%;%sqlcl%\osdt_cert.jar

rem simulate mkstore command
java -classpath %classpath% oracle.security.pki.OracleSecretStoreTextUI %args%

endlocal

Create the wallet and store the credential

mdkir "C:\oracle_wallets"
mkstore -wrl "C:\oracle_wallets" -create
mkstore -wrl "C:\oracle_wallets" -createCredential paris chiup XXXXXXXX
mkstore -wrl "C:\oracle_wallets" -listCredential

Create C:\Program Files\instantclient\network\admin\sqlnet.ora

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = C:\oracle_wallets)
    )
  )
SQLNET.WALLET_OVERRIDE = TRUE

Test it with SQL*Plus

sqlplus /@paris

Configure jobStatusRepository in Oracle Reports 12c

Create the service account

CREATE USER RW_SERVER_SVC
  IDENTIFIED BY "password"
  HTTP DIGEST DISABLE
  DEFAULT TABLESPACE SMALL_DATA
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

-- 1 Role for RW_SERVER_SVC 
GRANT RESOURCE TO RW_SERVER_SVC;
ALTER USER RW_SERVER_SVC DEFAULT ROLE ALL;

-- 1 System Privilege for RW_SERVER_SVC 
GRANT CREATE SESSION TO RW_SERVER_SVC;

-- 2 Tablespace Quotas for RW_SERVER_SVC 
ALTER USER RW_SERVER_SVC QUOTA UNLIMITED ON SMALL_DATA;
ALTER USER RW_SERVER_SVC QUOTA UNLIMITED ON USERS;

On Application Server

cd E:\oracle\penfax_app\Oracle_Home\reports\admin\sql
SQL> connect RW_SERVER_SVC/<password>@DEV
SQL> @rw_server.sql
SQL> grant select on rw_server_svc.rw_server_job_queue to optrust,rpa_api;

On Application Server

Edit this file E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration\rwserver.conf

Add this XML under the <server> tag

<jobStatusRepository class="oracle.reports.server.JobRepositoryDB">
    <property name="dbpassword" value="password"/>
    <property name="dbconn" value="xxxxxxxx.yyyyyy.local:1521:DEV"/>
    <property name="dbuser" value="rw_server_svc"/>
</jobStatusRepository>

Check the output after running a report

  SELECT *
    FROM RW_SERVER_SVC.RW_SERVER_JOB_QUEUE
   WHERE server = 'rep_wls_reports_parisas01p'
ORDER BY job_id DESC;