Connection Oracle ADW

dev support
1

I am looking to connect an Oracle database in the Cloud. For that I created an account with oracle and an Autonomous (free) type database.

Without a problem, I can connect using SQL Developer and the Wallet (see printscreen)

2021-10-18_10-15-58
2021-10-18_10-15-581366×828 62.9 KB

2021-10-18_10-16-26
2021-10-18_10-16-261441×672 83.8 KB

I followed the Oracle connection instructions according to this: JDBC Thin Connections with a Wallet (mTLS)

I create my connection with Application.cfc like this:

    this.name = "CloudOracleConnection"
    this.datasources["cloud"] = {
        class: 'oracle.jdbc.OracleDriver'
      , bundleName: 'org.lucee.oracle'
      , bundleVersion: '19.12.0.0000L'
      , connectionString: 'jdbc:oracle:thin:@test1_medium?TNS_ADMIN=A:\\testOracleCloud\\wallet_test'
      , username: 'admin'
      , password: "07XJSYer28FGHZHKqypn"
  };

I deployed the files .jar in the lib folder and the configuration in wallet_test folder. Modify the sqlname.ora configuration files.

sqlnet.ora :

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="A:\\testOracleCloud\\wallet_test")))
SSL_SERVER_DN_MATCH=yes

When I try to reach my base with a query, I just have a time out.

But if I change the path of TNS_ADMIN to a wrong path, I have an error. (TNS_ADMIN = a:\wrongdirectory)

Which makes me think my setup is correct.

Any ideas ?

Don’t forget to tell us about your stack!

OS: Windows 10
Java Version: 11.0.11 (AdoptOpenJDK) 64bit
Tomcat Version: commandBox environement
Lucee Version: 5.3.7.47

2

This video was extremely helpful in sorting this issue out: Episode 2: Getting Started with Java/JDBC and Autonomous database

  1. Enable Access Control List (ACL) on your Oracle Autonomous Database;
  2. Add your IP address to the ACL;
  3. Uncheck required mutual TLS (mTLS) authentication;
  4. Go to your Connection Strings on your Oracle Autonomous Database and switch TLS Authentication to TLS;
  5. Copy the connection string exactly as is;
  6. Lucee datasource should look like this …
    this.datasources["oracle_adb"] = {
    class: 'oracle.jdbc.OracleDriver'
    , bundleName: 'org.lucee.oracle'
    , bundleVersion: '21.8.0.0-ojdbc11' //anything v11+
    , connectionString: 'jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=<<PORT>>)(host=<<HOST>>))(connect_data=(service_name=<<SERVICE_NAME>>))(security=(ssl_server_dn_match=yes)))'
    , username: '<<YOUR_USERNAME>>'
    , password: "<<YOUR_PASSWORD>>"
    };
1 Like