System Administration - SMTP Wallet Setup

NOTE: This setup does not apply to Cloud customers.

TLS and Oracle Wallet Requirements

If you are trying to setup SMTP TLS connectivity and getting the error below, that means you have not setup the Oracle Wallet for the TLS connectivity.

Copy

ORA-20999: <CMIC_ERR_CODE>NO-ERROR-CODE</CMIC_ERR_CODE><CMIC_ERR_MSG>DBK_SYS_MAIL.begin_session.open SMTP connection
: :  Certificate validation failure
</CMIC_ERR_MSG>

Why is the wallet required for SMTP TLS communication?

CMiC uses the Oracle database to send out some of its email communications. The Oracle database uses internal Java to send these email notifications. Part of the Java security is that it is required to have what is known as a trust keystore/wallet or a specific location with a container of all SSL certificates that it can trust to communicate with. When the TLS connection is established, it is using an SSL certificate and Java needs to trust the SSL certificate that this TLS connection will be using. That is why this wallet needs to be setup.

What is second and third level trust certificates for the SMTP domain?

Each SSL certificate always has a hierarchy in the certification path that are issued/trusted/verified by these higher level SSL certificates. In the example below, you have i.s-microsoft.com SSL certificate of Level 1 and above them are Microsoft IT TLS CA 1 and DigiCert Baltimore Root Certificate. This is what is referred to as second and third level trust certificates. Often these are called CA (Certification Authority) or CA Root or Root SSL Certificates.

The wallet that you are creating needs to contain all of these SSL certificates, the main SSL certificate that lives on your SMTP Server and the full certification path above this SSL certificate (typically there will be two certificates above the main one).

Steps on Setting Up the Wallet

To create the Oracle Wallet, it has to be setup and saved on the database server.

You need to choose the location of the wallet carefully so that it won’t be deleted accidentally as this will stop all SMTP TLS communication from working.

The following are some of the common places for storing the wallet. The actual location of where it is stored is dependent on your server setup.

Linux OS

Copy

/u01/app/oracle/smtp_tls_wallet
/home/oracle/smtp_tls_wallet

Windows OS

Copy

D:\cm\ias\smtp_tls_wallet
D:\oracle\smtp_tls_wallet

General Steps

Copy

mkdir DIR_WALLET
orapki wallet create -wallet DIR_WALLET -pwd PWD
orapki wallet add -wallet DIR_WALLET -pwd PWD -trusted_cert -cert SOME_DIR\1.cer
orapki wallet add -wallet DIR_WALLET -pwd PWD -trusted_cert -cert SOME_DIR\2.cer
orapki wallet add -wallet DIR_WALLET -pwd PWD -trusted_cert -cert SOME_DIR\3.cer
orapki wallet display -wallet DIR_WALLET -pwd PWD

Below are examples with actual values:

Windows OS

Copy

set wallet_dir=D:\cm\ias\smtp_tls_wallet
set wallet_pwd=superStrongPassword123
mkdir %wallet_dir%
orapki wallet create -wallet %wallet_dir% -pwd %wallet_pwd%
orapki wallet add -wallet %wallet_dir% -pwd %wallet_pwd% -trusted_cert -cert D:\tls_smtp1.cer
orapki wallet add -wallet %wallet_dir% -pwd %wallet_pwd% -trusted_cert -cert D:\tls_smtp2.cer
orapki wallet add -wallet %wallet_dir% -pwd %wallet_pwd% -trusted_cert -cert D:\tls_smtp3.cer
orapki wallet display %wallet_dir% -pwd %wallet_pwd%

Where:

D:\cm\ias\smtp_tls_wallet is the folder where the Oracle Wallet will be stored.
superStrongPassword123 is the wallet password. It is suggested to keep it in a safe place so you always know what it is.
D:\tls_smtp1.cer is the SSL Certificate of the SMTP domain.
D:\tls_smtp2.cer: & D:\tls_smtp3.cer are the second and third level trust certificates for the SMTP domain.

Linux OS

Copy

export wallet_dir=/u01/app/oracle/smtp_tls_wallet
export wallet_pwd=superStrongPassword123
mkdir ${wallet_dir}
orapki wallet create -wallet ${wallet_dir} -pwd ${wallet_pwd}
orapki wallet add -wallet  ${wallet_dir} -pwd ${wallet_pwd} -trusted_cert -cert /tmp/tls_smtp1.cer
orapki wallet add -wallet  ${wallet_dir} -pwd ${wallet_pwd} -trusted_cert -cert /tmp/tls_smtp2.cer
orapki wallet add -wallet  ${wallet_dir} -pwd ${wallet_pwd} -trusted_cert -cert /tmp/tls_smtp3.cer
orapki wallet display %wallet_dir% -pwd %wallet_pwd%

Where:

/u01/app/oracle/smtp_tls_wallet is the folder where the Oracle Wallet will be stored.
superStrongPassword123 is the wallet password. It is suggested to keep it in a safe place so you always know what it is.
/tmp/tls_smtp1.cer is the SSL certificate of the SMTP domain.
/tmp/tls_smtp2.cer & /tmp/tls_smtp3.cer is the second and third level trust certificates for the SMTP domain.

Update the da.syswebservers with the path and password for the Oracle Wallet.

NOTE: As of right now, there is no interface to update these fields on the CMiC application. It can only be done using SQL code.

SWS_WALLET_PATH

The location of the path should be on the database server.

Windows OS

Copy

update da.syswebservers
set SWS_WALLET_PATH = 'file:D:\cm\ias\smtp_tls_wallet'
where SWS_SMTP_FLAG  = 'Y';
commit;

Linux OS

Copy

update da.syswebservers
set SWS_WALLET_PATH = 'file:/u01/app/oracle/smtp_tls_wallet'
where SWS_SMTP_FLAG  = 'Y';
commit;

SWS_WALLET_PASSWORD

Copy

update da.syswebservers
set SWS_WALLET_PASSWORD = da.dbk_sd_access_init.f_encrypt_string('superStrongPassword123')
where SWS_SMTP_FLAG  = 'Y';
commit;

Changing the TLS Protocol can be done inside the CMiC application or using the SQL code below.

You will need to set SWS_PROTOCOL to "TLS" for the entry SWS_SMTP_FLAG.

The SQL code will look like the following:
Copy
```
update da.syswebservers
set SWS_PROTOCOL = 'TLS'
where SWS_SMTP_FLAG  = 'Y';
commit;
```

Once all is setup, you can test if it is running by running in the DB:

Copy

begin
da.dbk_sys_mail.mail ('FROM_EMAIL'
,'YOUR_EMAIL'
,'TEST_DO_NOT_REPLY'
,'TLS_SMTP_Test'
);
end;
/

(Optional) Check all settings by verifying that all are correct, excluding the passwords.

Copy

select sws_URL,sws_user,sws_password,sws_port_num,SWS_protocol,SWS_WALLET_PATH,SWS_WALLET_PASSWORD from da.syswebservers where sws_smtp_flag='Y';

Once this has been tested successfully in one environment, you can promote this to other environments.

Maintenance After Setup

The problems with SSL certificates are that they expire and when they expire things will stop working. This is the main ongoing maintenance associated with this setup. When any of the SSL certificates expire or renew, the wallet needs to be recreated (or updated). This is for any SSL certificate in the wallet, either the main SMTP SSL Certificate or the second and third level Trust Certificates/CA and CA ROOT Certificates.