Implementation

• Implementation
  • Create empty wallet for credentials
  • Backup Environment
  • SQLNET.ORA Wallet Conflict
  • Update Wallet With Credentials From Old-Fashion DBDEFINE.SQL
  • Update Environment
  • Updating Jasperserver-tools Utilities
  • Removal of DBDEFINE.SQL from the Backup Folder

Create empty wallet for credentials

The default location for wallets on Windows is D:\cm\ias\wallet. This folder should never be deleted.

Example:

set Wallet_Location=D:\cm\ias\wallet
mkdir %Wallet_Location%

Auto Login Flag

There are 2 flags that oracle wallet takes:

• auto_login_local

• auto_login

By default, we recommend using the -auto_login_local flag as it is more secure. However, it has some flaws and does not work all the time. If it does not work, fall back to the -auto_login flag. In this guide, we will use the -auto_login flag, just substitute it with the -auto_login_local flag if that version is needed.

Exceptions when -auto_login needs to be used:

1. Personalized Accounts

   If you are using Personalized Accounts for each DBA: -auto_login needs to be used.

2. Mixed Case Windows OS Username

   There is a Windows bug where if the username has upper and lower case letters, the password store does not work on Windows OS. If this issue is encountered, use the -auto_login flag.

   Use the whoami command to check usernames in mixed case.

Manual Method

If the wallet is going to be created from scratch:

1. Create folder <CMIC_HOME>/ias/wallet.

2. Open the cmd console as Administrator and run the following:

   Copy

   Set Wallet_PWD=SecretPassword
   cd /d <CLIENT_ROOTDIR>/bin
   set JAVA_HOME=D:\Java\jdkXXX
   orapki wallet create -wallet <CMIC_HOME>/ias/wallet -auto_login

   Here CLIENT_ROOTDIR is the value from <CMIC_HOME>/ v12_install.txt (or install.txt) or simply the main Oracle client used.

   For example, if this is Windows OS and CLIENT_ROOTDIR=d:\oracle\18cclient, the commands are:

   Copy

   cd /d d:\oracle\18cclient\bin
   set JAVA_HOME=D:\Java\jdk1.8.0_191
   orapki.bat wallet create -wallet D:\cm\ias\wallet -auto_login

   Enter and re-enter the provided value again for WALLET_PSWD.

Scripting/Batch Method

This is same method if you want to run this as batch:

set Wallet_PWD=SecretPassword
set Wallet_Location=D:\cm\ias\wallet
mkdir %Wallet_Location%
orapki.bat wallet create -wallet %Wallet_Location% -auto_login -pwd %WalletPWD%

Backup Environment

You are required to backup the D:\cm\v12\ENV\ prior to moving on to the next step.

SQLNET.ORA Wallet Conflict

There might already be an Oracle Wallet defined in the sqlnet.ora of the Oracle client. This might be the case when Oracle TCPS configuration was setup. Open file <ORACLE_CLIENT>\network\admin\sqlnet.ora to validate if there is any wallet defined. If there is no WALLET_LOCATION entry, its safe to proceed. If there is a WALLET_LOCATION already and it is pointing to Oracle TCPS configuration, the following explains the options on how to resolve this conflict:

1. Have one wallet for all implementation.

2. Remove the older implementations if this is not used.

3. All other working implementation that is found is also possible.

For example, this can be removed:

SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
    (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
            (DIRECTORY = d:\oracle\wallet_tcps)
        )
    )

Note If there is a WALLET_LOCATION other than TCPS, please review what it is used for.

There is a way to setup multiple wallets on the APP Server but it's preferred not to do that. This would be done through individual TNSNAMES.ORA entries. If the environment requires this, please review following documentation, but take note that this is not common and not covered by this guide:

https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/configuring-the-network-environment.html#GUID-C1543ED3-0052-42F8-9FF0-7B21F0929DD4

Update Wallet With Credentials From Old-Fashion DBDEFINE.SQL

If the wallet is not updated for this <ENV>:

1. Use the provided script updateWalletFromDbdefine.pl. Run the cmd console as Administrator and enter:

   Copy

   cd /d <SCRIPT_PATH>

   The <SCRIPT_PATH> is the path to the folder where updateWalletFromDbdefine.pl is placed.

2. Run the command:

   Copy

   perl updateWalletFromDbdefine.pl <ENV> <CMIC_HOME> v12
   <CLIENT_ROOTDIR> <WALLET_DIR> <WALLET_PSWD>

   For example,

   Copy

   perl updateWalletFromDbdefine.pl prod D:\cm v12 d:\oracle\18cclient
   D:/cm/ias/wallet AbCd1234

   The value for <WALLET_PSWD> is the same that was used in the creation of the wallet.

Batch/Scripting Method

set Wallet_PWD=SecretPassword
set Wallet_Location=D:\cm\ias\wallet
perl updateWalletFromDbdefine.pl prod D:\cm v12 d:\oracle\18cclient %Wallet_Location% %WalletPWD%

How it should look like:

To check if the wallet was updated correctly, try to perform tnsping and connect to a few users with sqlplus. As an example for the prod environment (users da, uig):

tnsping da.prod
sqlplus /@da.prod
tnsping uig.prod
sqlplus /@uig.prod

It has to be a successful connection like the regular one with username and password. Check if the user is correct. For example,

SQL> show user
USER is "DA"
SQL>

Update Environment

This script will go through every single script inside the environment and parse the files for connection strings. If it finds it, it will replace them with the new format.

Use the provided script schemaDBupdateUtil.pl. Run the cmd console as Administrator and enter:

cd /d <SCRIPT_PATH>

The <SCRIPT_PATH> is the path to the folder where schemaDBupdateUtil.pl is placed.

If the connection strings and dbdefine.sql have to be updated for the environment <ENV>, run the command:

perl schemaDBupdateUtil.pl <ENV> <CMIC_HOME> v12

For example,

perl schemaDBupdateUtil.pl prod D:\cm v12

After execution, check that the dbdefine.sql file in the <CMIC_HOME>\v12\<ENV>\jspsql folder is updated. All passwords have to be defined as ′ ′.

Updating Jasperserver-tools Utilities

During this step, other tools are made to use the Oracle External Store Wallet. In the case, there are two tools in question: Jasper Import Tools (for loading Jasper Reports) or Jasper Export Tools.

1. Update cmic-js-delete.cmd and cmic-js-import.cmd in

   Copy

   <CMIC_HOME>\ias\j2ee\<ENV>\jasper\bin

2. Update js-import.bat and js-export.bat in

   Copy

   <CMIC_HOME>\v12\<ENV>\jasperserer\buildomatic\

All Sample Modified Files are provided for demonstration purposes.

Below is an example for cmic-js-delete.cmd.

Note There is a dot at the end of EXP_CLASSPATH; the dot needs to be there.

Content Before:

Content After:

Marked red lines are new added lines. The d:\oracle\18cclient is an example for the case of

CLIENT_ROOTDIR=d:\oracle\18cclient.

If the value of CLIENT_ROOTDIR is different, change JAVA_OPTS and EXP_CLASSPATH correspondingly.

IMPORTANT Do the same update for cmic-js-import.cmd, js-import.bat, and js-export.bat as the above example is only for cmic-js-delete.cmd.

Removal of DBDEFINE.SQL from the Backup Folder

One of the steps was that the environment was backed up. In that backup copy, there is a backup of dbdefine.sql that needs to be removed. Delete that file in the backup folder.