Oracle TLS Support

by Kevin Roldos

To configure both JDBC and ODBC to support the Transport Layer Security (TLS) protocol, there are additional configuration steps that are needed when compared to this previous article. This article is not a replacement but to add additional information about the steps needed for TLS connections.

Once the connections are established, this the graphical IRI Workbench IDE and the CoSort SortCL engine can combine to perform: data integration and migration, data wrangling and reporting, data discovery and auditing, data masking and cleansing, data synthesis and subsetting, and more, over an encrypted connection to the Oracle database.

Oracle has a wide range of configurations when it comes to connecting clients to the database server. This article will explain important concepts related to Oracle, required files to download for TLS connections, and a YouTube tutorial on how to set everything up in IRI Workbench. Other configuration options are outside the scope of this article.

Required Information and Files

Oracle uses several files and utilities to set up different configuration options to connect to the server. In the following sections, background information will be provided on the Oracle wallet, orapki utility, sqlnet.ora and tnsnames.ora files required to set up the TLS connection.

Oracle Wallet

Oracle uses a “wallet” to contain the certificates needed for the TLS handshake to work. The oracle wallet is not automatically created, so this will have to be provided by the Database Administrator (DBA) or created on the client machine .

For the TLS handshake to work, there are two certificates that are needed:

The server certificate
The client certificate

A Client Wallet needs to import the Server Certificate in order for the client machine to validate and trust the server. The server wallet also needs to import the client certificate to validate and trust the client. If the DBA provides the client wallet then the importing of certificates should already be done.

Orapki Utility

To create the wallet, the client machine needs to have the CLI tool called orapki. This utility will allow the client to create and manage the wallet. Orapki is included in the Oracle Database (version) Client, but is not included in the instant client version.

*** Note: the previous link is for Oracle 19c. Google “Oracle database (your database version) client download” for your specific Oracle version.

Having orapki downloaded onto the client machine is recommended since it will help manage and address any issues connecting to the database server using the wallet. If the DBA is providing the client wallet, the password to the wallet is needed to manipulate said wallet.

Useful orapki commands (replace the bold sections):

Display certificates inside the wallet
- orapki wallet display -wallet pathToWallet -pwd pwdForWallet
Import certificates to the wallet’s trusted certificates
- orapki wallet add -wallet wallet_location -trusted_cert -cert certificate_location
Create a wallet
- orapki wallet create -wallet wallet_location

*** Note: pwd stands for password

Tnsnames.ora

Since the client machine can host multiple applications that will need to connect to different databases, the use of sqlnet.ora and tnsnames.ora files are used to centralize the configuration parameters.

Tnsnames.ora file is a SQL*Net configuration file that defines database addresses for establishing connections to them. This will allow the user to centralize all the connection information needed to interact with databases and specify what protocols to use.

Common directories to find the tnsnames.ora file are:

ORACLE_HOME/network/admin
oracle/instantclient_[database_version]/network/admin

For tnsname.ora file, provide the following information:

The IP address of the server or the host name
The port number
The protocol used
- Transmission Control Protocol Secures (TCPS) for TLS
- Transmission Control Protocol (TCP) for an unencrypted connection
Oracle wallet directory
- Only needed for TLS
The database service name.

The example entry below is connecting to a database server using the protocol TCPS and is providing the directory to the client wallet. Oracle is particular about the placement of certain information and uses parentheses to group related information together, which can be the cause of some errors when first setting up a connection.

FREE_SSL=
      (DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.3.170)(PORT= 2484))
            (SECURITY=(WALLET_LOCATION=C:\Oracle\instantclient_21_11\network\admin\clientWallet))
            (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=FREE)))

Sqlnet.ora

The sqlnet.ora file is the profile configuration file which resides on the client machines and the database server. The file enables you to do the following:

Specify the client domain to append to unqualified names
Prioritize naming methods
Enable logging and tracing features
Route connections through specific processes
Configure Oracle Advanced Security
Use protocol-specific parameters to restrict access to the database

Sqlnet.ora file can be found in the same directories as the tnsnames.ora file. In this file, the only two parameters needed are the WALLET_LOCATION and SSL_CLIENT_AUTHENTICATION.

The example entry below points to the directory of the wallet and sets the client authentication parameter to false. Authentication will still be performed in the JDBC/ODBC connections since the user credentials will be provided.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SSL_CLIENT_AUTHENTICATION=FALSE

WALLET_LOCATION=C:\Oracle\instantclient_21_11\network\admin\clientWallet

Required JAR Files

Additional JAR file(s) are needed for the JDBC JAR file to use the Oracle wallet for the TLS connection. Depending on the Oracle database version, the required JAR files are:

Oraclepki.jar
Osdt_core.jar
Osdt_cert.jar

Use the provided link to download the “Zipped JDBC Driver and Companion Jars” for the specific database version required and JDK17 supported.

*** Note: Newer versions of Oracle will only need the oraclepki.jar file and will not provide the other two jar files as part of the zipped file.

JDBC and ODBC

ODBC connection is simple to set up because the only thing that changes is providing the TNS name in the tnsnames.ora file that is connecting to the desired database with the TCPS protocol.

The JDBC URL has the biggest change and depending on what additional configurations are set up, IRI Workbench now includes a custom URL parameter to allow the user to paste the URL and its additional parameters. Since all JDBC URLs have to start with the same prefix (jdbc:oracle:thin:@), only include the additional URL parameters in the text box.

The image below shows an example of the changes in IRI Workbench. The URL is using the Oracle wallet and the TCPS protocol to connect to the database.

The syntax used in the Custom URL is:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=ip_address/hostName)(PORT=portNumber))
(SECURITY=(WALLET_LOCATION=directoryToWallet))(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=placeHolder)))

*** Note: In a future update, the use of the TNS name for JDBC URL will be supported.

With all the required files downloaded and an understanding of how the files work to set up the database connections, watch the YouTube tutorial which will go step by step on setting up the drivers for IRI Workbench and SortCL: