MacOS Python Oracle TCPS connection with Oracle instant client - ORA-29024: Certificate validation failure

Question

I am attempting to connect to an Oracle Database using Python cx_Oracle package and Oracle instantclient_19_8. I keep getting this error - OORA-29024: Certificate validation failure.

1. I downloaded and installed Oracle InstantClient_19_8.
2. Within the [...]instantclient_19_8/network/admin directory, I copied the cwallet.sso and ewallet.p12 files I received from a DBA.
3. I created a sqlnet.ora file in the network/admin directory:

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /instantclient_19_8/network/admin) ) ) SQLNET.WALLET_OVERRIDE = TRUE

4. I created a tnsnames.ora file in the network/admin diretory (though this might not be necessary):

ora_conn = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS) (HOST = {HOST}) (PORT = 1525) ) ) (CONNECT_DATA = (SERVICE_NAME = {SVC_NAME) ) )

5. In Pycharm, I set the TNS_ADMIN environment variable and pointed it to [...]/instantclient_19_8/network/admin.

My Python connection info is:

dsn=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST={HOST_ADDRESS})(PORT={PORT_NBR})))(CONNECT_DATA=(SERVICE_NAME={SVC_NAME})))

cx_Oracle.init_oracle_client(lib_dir='[...]/instantclient_19_8') 

ora_conn = cx_Oracle.connect(user=usr
                              , password=pw
                              , dsn=dsn
                              , encoding="UTF-8")

cursor = ora_conn.cursor()

I've seen several blog posts about using orapki, but it is not clear how to use orapki with instant client. If the DBA created the wallet, are they the only ones who can add a cert file to the wallet? Or, would I be able to if I got orapki to work?

Any suggestions/guidance would be great!

Thank you!

Answer 1

The setting DIRECTORY = /instantclient_19_8/network/admin looks suspicious. Do you really have an instantclient_19_8 directory in the root filesystem? I would expect to see something like /Users/you/instantclient_19_8/network/admin instead.

However, since you moved the files to a default location you didn't need to even edit sqlnet.ora. The default DIRECTORY path (starting with the ?) works automatically. You also don't need to set TNS_ADMIN when the files are in this default location.

With C language code like cx_Oracle you don't need the .p12 file. You just need the .sso file, sqlnet.ora and tnsnames.ora.

The cx_Oracle documentation Connecting to Oracle Cloud Autononmous Databases shows the two alternatives: when you have the wallet files in a default location, and when you don't.

You may also want to look at the blog post How to connect to Oracle Autonomous Cloud Databases.