help with oracle TLS connection

[codeminkey]

Hi,

My org has been using cx_Oracle successfully for some time to talk to our Oracle database (19c) with username/password authentication. Our admin recently was given a new requirement to implement TLS1.2. They gave us a certificate file with a "p7b" extension. The subject of the cert is our database server hostname and it is signed by our org's internal CA. The p7b file does not contain a root cert as far as I can tell (but I do have a separate CA root cert file). "openssl pkcs7" cannot read the p7b file but "openssl x509" can (so what is it?); it appears to be in PEM format.

We were able to add this p7b cert to the Java trust store and that was all we needed to do to get our Java code working.

I'm trying to find the Python/cx_Oracle equivalent but have not had any luck. cx_Oracle fails with the error "ORA-28759: failure to open file".

Since the p7b file appears to not contain a root cert I'm confused as to why it worked with Java?

I experimented a bit with Python's ssl module (although I'm not sure if cx_Oracle uses it). I was able to get it to load the p7b file (but cx_Oracle still fails to connect). I also tried to load the separate CA cert but same result.

We are using cx_Oracle 8.3 on Python 3. Does it support this (TLS)? If not, will python-oracledb? I will upgrade if I have to but I'd rather not right now due to time constraints.

Would greatly appreciate any insight.

[anthony-tuininga]

The error ORA-28759: failure to open file implies that you have a wallet file configured in your sqlnet.ora configuration file, but the file you reference does not exist. Thick mode will only function with a wallet file so you will need to use the Oracle tools (like orapki) to generate such.

For thin mode you should be able to add your CA root cert file to the OS store used by openssl (which is what Python uses for its TLS implementation). If it is setup for one-way TLS that's all you need. If you have two-way TLS you will need to have the client certificate and private key in PEM format in ewallet.pem and pass that through to the connect() call with the wallet_location parameter (and supply a wallet_password parameter as well if it is encrypted).

There is a new ssl_context parameter available in unreleased code which you can use to perform whatever gymnastics are needed to get the TLS negotiation to complete -- the suggestions in the previous paragraph are preferred, though!

So to answer your question: yes, cx_Oracle has support for TLS but you will have to setup a wallet file that it recognizes and configure it correctly in sqlnet.ora. And yes, python-oracledb thick mode behaves exactly the same as cx_Oracle did. Thin mode is more flexible, though.

[codeminkey]

Thanks for your response and the help.

A couple quick things: we are using the thin client exclusively and we are not currently using a sqlnet.ora file (nor a tnsnames.ora file). We are using what I think is called an easy-connect string (the one with the Lisp-like syntax) and I'm parsing those out of JDBC connect strings (because we already have them in our configuration).

I've been wondering which component makes the actual socket connection to the database: Python, cx_Oracle, or the thin client? You seem to be saying that it is Python?

I did try placing the cert in the OS-level openssl trust store and it did not work but I don't recall now what the specific result was (it was a while ago already): my recollection is that either (1) it made it into the openssl key store but the connection still failed; or (2) it did not make it into the openssl keystore and I assumed that the key tool did not like it for that keystore. I gave up on that after it seemed to not work. But it's certainly possible I did something wrong and I'll revisit it if you think that's the right thing to do.

I don't know exactly what this key is supposed to be (nor whether it is one-way or two) and our database admins have not been very helpful (frankly, I don't think they understand it themselves -- someone else must have handed it to them). They only thing I know is that they told us the key would work for Java if we added it to the Java keystore, and indeed it did. I also know that it is not a root cert.

I recently found some documentation about using ewallet.pem and wallet_location= with the thin client. I tried this but got a Python exception saying the keyword argument wallet_location= is unknown (I also do not see it in the documentation). So I assumed that cx_Oracle does not support this and that I would have to upgrade to the newer python-oracledb (is this correct?). When I tried to install it (under Cygwin) the installation failed with a compile error. My Cygwin installation is out of date (long story) and I assumed that was the reason it failed. However, I decided not to invest time in updating Cygwin and to instead move ahead with the plan to establish a Linux VM (again, long story) which will make Cygwin obsolete. I'm presently side-tracked with this (nearly 20 years of data to move over from my old laptop, etc) but I hope to move forward with this plan shortly.

Regarding ewallet.pem, given that the cert I was given is in PEM format, I'm thinking that I only need to rename it and it should work. Does that sound right?

Thanks again for your help!

~ray

[anthony-tuininga]

Apologies for the delay in getting back to you. To answer your questions:

• Easy Connect syntax looks like this: host:port/service_name; the "Lisp-like" syntax with the many parentheses is the full connect descriptor
• in cx_Oracle and python-oracled thick mode the socket is established by the Oracle Client libraries but in python-oracledb thin mode the socket is established directly by Python in the driver code
• the wallet_location parameter is new to python-oracledb and will not work with cx_Oracle
• only python-oracledb thin mode uses ewallet.pem and it can contain a private key as well as certificates; you can also use the ssl_context parameter (which is now part of a public release) to do whatever you need to in order to get the server validated