How to connect Java a program to Oracle Autonomous Database over TLS without wallet

In this blog post, we will discuss how to connect Java a program using JDBC thin driver to Oracle Autonomous Database over TLS without a wallet.

With TLS support we can now connect to ADB without the credential wallet.

Part 1: We need to configure ADB for TLS to get the TLS connection string.

a) For the ADB we want to connect over TLS, In the Autonomous database details page, we need to set Mutual TLS authentication (mTLS) as not required. 

b) Then from the database connection page (we can navigate to it by clicking on DB connection button in the ADB console) chose "TLS" as TLS authentication and copy the connection string for desired TNS name. In this example, I had copied demodb_medium

Part 2: Java program which uses the above connect string in jdbc to execute sql statements.

Prereq : ojdbc8.jar and ucp.jar

I have used JDK 11 in this eample

package demo;

import java.sql.*;
import java.util.Properties;

public class ADBSharedTLSConnect {
    private static String atps_tls = "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=rks9000p5ge4_demodb_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)(ssl_server_cert_dn=\"CN=adb.ap-mumbai-1.oraclecloud.com, OU=Oracle ADB INDIA, O=Oracle Corporation, L=Redwood City, ST=California, C=US\")))";
    private static String db_url = "jdbc:oracle:thin:@" + atps_tls;
    private static String dbUser = "admin";
    private static String dbPwd = "test@ATP122245";

    public static void main(String[] args) {
        System.out.println("Connecting to ATPS over TLS...");
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Properties props = new Properties();
            props.setProperty("user", dbUser);
            props.setProperty("password", dbPwd);
            props.setProperty("oracle.jdbc.fanEnabled", "false");
            con = DriverManager.getConnection(db_url, props);
            stmt = con.createStatement();
            rs = stmt.executeQuery("select sysdate from dual");
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            System.out.println("Demo Over...");

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

atps_tls is the one that I had copied from part 1 step b.

I have used oracle.jdbc.fanEnabled property as false, without this configuration there was an error 

SEVERE: attempt to configure ONS in FanManager failed with oracle.ons.NoServersAvailable: Subscription time out

Although query was getting executed.

Some useful links

1. JDBC connection without wallet

2. Update your Autonomous Database Instance to Allow both TLS and mTLS Authentication

3. View TNS Names and Connection Strings for an Autonomous Database Instance

4. Source Code in git