Configure Oracle Client for SSL/TLS (TCPS) Connection

To enable secure communication between SnapLogic and an Oracle database using SSL/TLS (TCPS), the Oracle client must trust the server certificate and be configured with wallet or truststore in the deployment model.

Before you begin, ensure you have the following:

The Oracle Database server is configured for TCPS (commonly on port 2484).
Server’s root and intermediate CA certificates (.crt or .pem).
Oracle Client software is installed.
orapki utility is available (included with full Oracle Client/DB install, not with Instant Client Basic packages).

Create a client wallet.
This is required when using Oracle Wallet authentication or TCPS with wallet validation. Replace <WALLET_PASSWORD> with a secure password.
```
mkdir -p /opt/snaplogic/oracle_wallet
orapki wallet create \
  -wallet "/opt/snaplogic/oracle_wallet" \
  -pwd <WALLET_PASSWORD> \
  -auto_login_local
```
Import the trusted server certificate.
This step allows you to validate the Oracle server certificate.
```
orapki wallet add \
  -wallet "/opt/snaplogic/oracle_wallet" \
  -trusted_cert \
  -cert /path/to/server_ca.crt \
  -pwd <WALLET_PASSWORD>
```
Alternative (when orapki is not available):

If using Oracle Instant Client (Basic/Light), where orapki is unavailable:
1. Create the wallet directory:
```
mkdir -p /opt/snaplogic/oracle_wallet
```
2. Copy wallet files from the database server:
  - ewallet.p12
  - cwallet.sso
Note: Snaplex runs as snapuser and must have read access to the wallet files.

Configure client network files.

Edit the files in $ORACLE_HOME/network/admin or the directory specified by TNS_ADMIN.

You have two supported approaches:

Configure sqlnet.ora.

WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
  (METHOD_DATA = (DIRECTORY = /opt/oracle/client_wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE   # One-way TLS
# SSL_VERSION = 1.2                 # Optional

Configure tnsnames.ora.

ORCLPDB1_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = <server_hostname>)(PORT = 2484))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <service_name>)
    )
    (SECURITY = (MY_WALLET_DIRECTORY = /opt/oracle/client_wallet))
  )

Replace the following placeholders:

<server_hostname> - Your Oracle server hostname
<service_name> - Your Oracle service name

Add the server certificate to the Java truststore.

You can import the server/CA certificate into either the default JVM truststore (cacerts) or a custom truststore.
- Import into default JVM Truststore (cacerts):
```
sudo keytool -import -trustcacerts \
  -alias oracle-db \
  -file /path/to/server_ca.crt \
  -keystore $JAVA_HOME/lib/security/cacerts \
  -storepass changeit
```
  Verify:
```
keytool -list \
  -keystore $JAVA_HOME/lib/security/cacerts \
  -storepass changeit | grep oracle-db
```
  Note: This modifies the global Java truststore and requires root access.
- Import into a custom truststore:
```
keytool -import -trustcacerts \
  -alias oracle-db \
  -file /path/to/server_ca.crt \
  -keystore /path/to/custom-truststore.jks \
  -storepass changeit
```
  Verify:
```
keytool -list \
  -keystore /path/to/custom-truststore.jks \
  -storepass changeit | grep oracle-db
```
  If using a custom truststore, configure the JVM options:
```
-Djavax.net.ssl.trustStore=/path/to/custom-truststore.jks
-Djavax.net.ssl.trustStorePassword=changeit
```
  Important: If you configure a custom truststore, it overrides the default cacerts truststore. Ensure that all required database certificates are imported into the custom truststore, as it is explicitly specified in the JVM options.
Test the connection.
Using SQL*Loader:

After configuring the wallet and network files, verify the TCPS connection using Oracle SQL*Loader. If the wallet and SSL configuration are correct, SQL*Loader connects successfully and begins processing the control file.
```
sqlldr username/password@ORCLPDB1_SSL control=test.ctl
```
Using SnapLogic:

Run the Bulk Load Snap or any Oracle Snap configured with SSL enabled. If SSL is configured correctly, validation and pipeline execution succeed.