Unable to connect to Oracle Autonomous database Database version: 21c

[jatinder_sondhi]

I am trying to use DB Connector as well as Oracle Connector (after installation/configuration).

I am getting an error - Execute failed: ORA-17002: I/O error: Connection reset, connect lapse 9 ms., Authentication lapse 0 ms for both cases.

I have tried both ojdbc8.jar as well as ojdbc11.jar.

[takbb]

Hi @jatinder_sondhi. Welcome to the KNIME community.

Are you able to connect ok to the database outside of KNIME e.g with SQL Developer or another tool?

When using the Oracle Connector, have you tried it with the supplied oracle driver rather than configuring your own?

[jatinder_sondhi]

Yes, I can connect from SQL Developer as well as other tools like Talend.

I have tried both own (downloaded from Oracle) as well as one from Knime as below:

[takbb]

Hi @jatinder_sondhi , for the purposes of trying this out, I’ve set myself up a free tier on Oracle Autonomous database and have been looking into how to connect.

Can I assume that your connection to Oracle is using mutual TLS (mTLS)? This is the default I believe for Oracle Autonomous DB. I’ve only just been reading up on it so I’m not an expert on Oracle Autonomous DB.

I haven’t got a connection myself, but I note you said that you have got it working with Talend. Was this connection done recently, or a while back? It looks like to get a connection working with mTLS, you need an Oracle Wallet and some additional jar files along with the jdbc driver.

See here for details of setting up with Talend Does any of that look familiar? I imagine that some similar steps are needed to make it work with KNIME since this is additional Oracle requirements for using mTLS.

Likewise with your SQL Developer connection… how have you got that configured. Has the connection been achieved using a “Basic” connection type, or is it configured to use a TNS entry or Cloud Wallet or something else?

More details on Oracle Cloud Wallet connections here

No solution yet for you, but continuing the discussion will hopefully lead to the answer

[jatinder_sondhi]

Hello there,
Thank you for all your efforts.

Yes, Oracle connection is using TLS. Talend is very recent as well and yes Talend setup as well as Oracle SQL Developer does use wallet (files within) alongside extra parameters like “javax.net.ssl.trustStoreType=SSO&javax.net.ssl.keyStoreType=SSO” as well as security=(wallet location) as part connect string / url.

Yes, again you are right - for SQL developer too, I have to use wallet. The default connection does not work otherwise (which works for non-cloud Oracle instances).

I am not sure where to put extra settings in Knime as in the cases of Talend or Oracle clients I have put. By the way, I have added my IP address to ACL on the Oracle side too, before which I got a different error.

Many Thanks

[jatinder_sondhi]

By the way, I am very new to Knime but have earlier worked with a lot of other ETL tools. Seems to be a very good tool so far. I am evaluating the tool for some new solutions as we are moving away from Informatica for cost reasons.

[takbb]

Hi @jatinder_sondhi , thanks for the extra info re Oracle connections. That does give pointers of where to look, and explains why KNIME isn’t connecting when other apps are. If I get some time later I’ll see if I can get a connection working by going looking through the steps for other tools such as Talend and SQL Developer. In the meantime, somebody who has done it before might chip in (fingers crossed!)

And welcome to the KNIME community. I hope you will get along with KNIME as it is a very powerful tool. Each of the different analytics/ETL tools has its strengths but KNIME is certainly the one that has worked best for me so far.

[jatinder_sondhi]

Thank you very much, Brian.

[takbb]

Hi @jatinder_sondhi,

I believe I have some good news. I managed to get KNIME to connect to an Oracle Autonomous Database with the wallet.

There may be another simpler way, but this worked for me:

Download Required Oracle Files

Download Cloud Wallet Client Credentials

Download the required Oracle Cloud Wallet Client Credentials file and unzip it to a local folder. Hopefully you already have this, but details on how to download that can be found here

Make a note of where this has been unzipped to
e.g.
C:\apps\oraclecloudwallet\client_credentials

Looking in this folder you should see files looking something like this:

Download jdbc and associated security files

Download the file ojdbc11-full.tar.gz from Oracle. This can be found here:

JDBC and UCP Downloads page

At time of writing, the direct link to the above file is https://download.oracle.com/otn-pub/otn_software/jdbc/233/ojdbc11-full.tar.gz

Unzip the odbc11-full.tar.gz file to a folder

Into a folder such as “C:\apps\oraclecloudwallet\downloaded jars”, (this can be any name you like) extract the above file:

You should see a set of files such as this:

The important files in here that will be required for KNIME are:

odbc11.jar
ucp11.jar
osdt_cert.jar
osdt_core.jar
oraclepki.jar

so make sure they are present!

recap
So at the end of the above you should have two folders:

• Cloud Wallet Client Config folder
  (e.g. C:\apps\oraclecloudwallet\client_credentials )
• Oracle jar files folder
  (e.g. C:\apps\oraclecloudwallet\downloaded jars )

Configure KNIME

Download the Oracle jdbc extension
I’m not sure this is strictly necessary as we will be setting up our own jdbc for Oracle, but I did it, so I’m including it here.

I think you have already done this anyway, in which case nothing to do for this part, but if not, complete the below dialog to install the extension, and restart KNIME

Add Oracle Cloud Database

From the preferences menu, select KNIME - Databases and then click the Add button

Enter the following information:

Notes
(1) Make sure you select “oracle” as the database type
(2) ID can be any name you want but mustn’t contain spaces. Just alphanumeric/underscores
(3) Name can be any name you want
(4) Description is anything you like, or can be left blank
(5) URL template is important, as this is what the Oracle Connector will use to find your wallet.

All of the following should be entered as a single line. Be sure to not lose any of the brackets! The only variable part of this is the location of the wallet directory. All other parts (e.g. <port>, <host>,<database> should be left exactly as shown as these will be replaced at runtime by the Oracle Connector.

jdbc:oracle:thin:@(description=(address=(protocol=tcps)(port=<port>)(host=<host>))(connect_data=(service_name=<database>))(security=(my_wallet_directory=C:\apps\oraclecloudwallet\oracle_client_credentials_wallet ) ) )

I don’t think it matters if you use \ or / in the folder entry for my_wallet_directory. I tried it both ways, but if in doubt you could replace it with /

(6) Click Add directory, and then choose the folder that contains the extracted files from odbc11-full.tar.gz

odbc11.jar
ucp11.jar
osdt_cert.jar
osdt_core.jar
oraclepki.jar

e.g. c:\apps\oraclecloudwallet\downloaded jars

(7) Finally click Find Driver Classes, and it should find oracle.jdbc.OracleDriver. If it doesn’t, it most likely would mean that the odbc11.jar file isn’t in the folder you specified, so check your selection.

Press OK on that dialog, and then Apply and Close.

Oracle Connector

Configure the Oracle Connector
Drop the Oracle Connector on your workflow

(1) Database dialect should be Oracle (as it is the Oracle Connector, I think that is the only option!)
(2) Make sure you have chosen the new Driver (e.g. OracleCloudDriver) as the Driver name.
(3) Set Host according to your database
(4) Set Port (e.g. 1522 for mTLS and 1521 for TLS)
(5) Set Database as your required Oracle Service name
(6) Set username and password

Now, according to Oracle the following JDBC parameters are required:

javax.net.ssl.trustStoreType=SSO
javax.net.ssl.keyStoreType=SSO

I found I didn’t need to add them and it worked. If you do find you need them, add them on the JDBC Parameters tab as follows:

(1) JDBC Parameters tab on Oracle Connector
(2) Click Add
(3) Set Name and Value
(repeat 2 and 3 for second parameter)
(4) Click Apply
(5) Click OK

That should be it if I haven’t forgotten any steps or mis-typed.
Good luck, and let us know how you get on.

[jatinder_sondhi]

Thank you very Brian, connecting is working now.

Very much appreciate your effort and a thorough response - very refreshing in the world of tech support :).

Last question - would there be any performance difference when choosing between two connection types?

[takbb]

You’re welcome @jatinder_sondhi. Glad it’s working.

There is a blog here discussing some of the differences between TLS and mTLS.

https://blogs.oracle.com/datawarehousing/post/connecting-your-autonomous-database-has-never-been-easier

which notes that TLS has better connection latency but I don’t have any knowledge of what that translates to in real world performance differences, nor whether there is any difference in performance other than the time it takes to connect. I suspect that once connected, both operate in a similar manner with the same levels of encryption and therefore offer similar performance.

[jatinder_sondhi]

thank you again, Brian.