Python connect to Oracle database with TCPS

Question

trying to connect Oracle database with TCPS connection from python but its failing with ORA-28759: failure to open file.

I am not sure what I need to be configured in wallet when connecting from local/other server to database on different server.

    import os
    import os.path
    import sys
    import cx_Oracle

    ORACLE_VERSION=19.3
    os.environ['ORACLE_BASE']=/Users/machild/Documents/oracle
    os.environ['ORACLE_HOME']='$ORACLE_BASE/product/instantclient/$ORACLE_VERSION'
    os.environ['DYLD_LIBRARY_PATH']='$ORACLE_HOME/lib'
    os.environ['OCI_DIR']='$DYLD_LIBRARY_PATH'
    os.environ['TNS_ADMIN']='$ORACLE_BASE/network/admin'


    Connection_String ='username/password@(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=orclconsapp.vci.map.com)(PORT=1587)))(CONNECT_DATA = (SERVICE_NAME=cons_app)))'

    connection = cx_Oracle.connect(Connection_String)


    cursor = connection.cursor()
    cursor.execute("""select to_char(sysdate,'mm/dd/yyyy') from dual""")
    for result in cursor:
        print(result)

**Its failing with below error**

Traceback (most recent call last):
File "/Users/machild/Documents/oracle_conn.py", line 16, in <module>
connection = cx_Oracle.connect(Connection_String)
cx_Oracle.DatabaseError: ORA-28759: failure to open file


I think it would need a wallet but not sure what exactly goes in wallet if we want to connect from external server( not where database is running). Any ideas?        

Answer 1

I experienced ORA-28759 also. I was using Python/SQLAlchemy to talk to an Oracle database requiring TCPS. My DBA gave me a trusted certificate, which I had to store in a wallet.

tl;dr: I created a wallet file using okapi, imported my certificate into that wallet, then edited sqlnet.ora to contain the location of the wallet. I used the TNS_ADMIN environment variable to point to sqlnet.ora. (Basically, what Christopher Jones suggests.) Since you've already got an existing TNS_ADMIN, you might want to just edit the sqlnet.ora in there to point to the wallet, after you've created it.

Below are the details for my situation, including how I created a wallet. What I did combines information from these sources:

1. https://docs.oracle.com/cd/E11882_01/network.112/e40393/asoappf.htm#ASOAG9831

2. https://docs.oracle.com/en/database/oracle/oracle-database/18/dbseg/configuring-authentication.html#GUID-3EA07020-A9F3-4FF9-9518-E1AEA3BDDBBE

3. https://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html#optnetfiles

Note: the details that follows is based on Windows!

The first thing I had to do is create a new wallet. The first URL from docs.oracle.com describe the command that worked for me: orapki. This tool came from my local installation of Oracle XE 18c (Windows).

To create a wallet, I typed:

orapki wallet -nologo create -wallet C:\Users\rick\wallet -auto_login

This created a folder C:\Users\rick\wallet that contained four files: cwallet.sso, cwallet.sso.lck, ewallet.p12, ewallet.p12.lck. The cwallet.sso is the 'auto-login wallet', and the ewallet.p12 is the PKCS#12 wallet. (Further details are in the documentation.) NOTE: -auto_login creates a wallet that does not need a password to open, though I was prompted to enter a password. I made up a throw-away password, and did not need to use it again.

The Oracle database I was trying to connect to required my wallet to contain a trusted certificate. orapki has the -trusted_cert switch, which allowed me to import it into my wallet.

orapki wallet -nologo add -wallet \Users\rick\wallet -cert \Users\rick\oracle-ca.pem -trusted_cert

I was able to confirm that the wallet contained the certificate, using:

orapki wallet -nologo display -wallet \Users\rick\wallet -complete

My output:

C:\Users\rick>orapki wallet -nologo display -wallet \Users\rick\wallet -complete
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=Redacted,OU=Oracle,O=Amazon Web Services\, Inc.,ST=Washington,L=Seattle,C=US
Issuer:         CN=Redacted,OU=Oracle,O=Amazon Web Services\, Inc.,ST=Washington,L=Seattle,C=US
Serial Number:  AA:C7:REDACTED:AE:75
Key Length      1024
MD5 digest:     66:EC:REDACTED:9F:0D
SHA digest:     D4:0D:REDACTED:3C:50:91

To make use of this wallet, you have to point to it. This involved two steps for me. The first step was to create a sqlnet.ora file that pointed to the wallet. The second step was to set the TNS_ADMIN environment variable to the directory containing the sqlnet.ora file. In my case, both the sqlnet.ora and the wallet files were in the same directory.

The second URL from docs.oracle.com shows how to specify the wallet location (directory) inside of the sqlnet.ora file. My sqlnet.ora file contained:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\Users\rick\wallet)))
SSL_CLIENT_AUTHENTICATION=FALSE

I obtained my sqlnet.ora from my Oracle server's network/admin directory. I copied this file into the same directory as my wallet, then I edited it. For good measure, I also copied the tnsnames.ora file from network/admin into the wallet directory, though I didn't need to edit this file.

Finally, the cx_Oracle documentation (the third URL) provides the last detail: how to point cx_Oracle to this wallet. From my understanding, you point cx_Oracle to the directory containing the sqlnet.ora, and sqlnet.ora points to the wallet directory.

The documentation says to use the config_dir named argument to cx_Oracle.init_oracle_client() to specify the location of the sqlnet.ora file, but since I was using SQLAlchemy I was not calling this directly. However, the documentation says by default the driver will look for the sqlnet.ora files in a few standard locations, including the directory specified by the TNS_ADMIN environment variable.

I set TNS_ADMIN using :

set TNS_ADMIN=C:\Users\rick\wallet

Then when I ran my Python program, it worked!