Trying to connect to Oracle Always Free database using cx_Oracle

Question

I have an Always Free Anonymous Database from Oracle and I'm trying to connect to it in Python using cx_Oracle.

Here is my code:

import cx_Oracle as cx

#getting the instant client
currentpath = os.path.dirname(os.path.abspath(__file__))
clientpath = os.path.join(currentpath, "instantclient_19_11")
cx.init_oracle_client(lib_dir=clientpath)
#easy connect string
connect_string = "tcps://adb.uk-london-1.oraclecloud.com/t3ulea4zs7iolax_db202008251748_high.atp.oraclecloud.com?wallet_location=/Users/georg/Downloads/Wallet&retry_count=20&retry_delay=3"
userpwd = "(my password)"

def dbConnect():
    con = cx.connect("ADMIN", userpwd, connect_string, encoding="UTF-8")
    print("connection successful")
    return con

con = dbConnect()

The error message I get is: cx_Oracle.DatabaseError: ORA-12506: TNS:listener rejected connection based on service ACL filtering

This is confusing to me as my database is set to allow secure access from anywhere:

"Access Type: Allow secure access from everywhere"

I'm really not sure what I'm doing wrong. Any help is appreciated. If any more information is needed, please let me know.

You need to set TNS_ADMIN environment variable to the location of your wallet files, then use general TNS entry on connection. I've used it this way: db.connect(os.environ["ora_cloud_usr"], os.environ["ora_cloud_pwd"], "test_low", encoding="UTF-8" ). — astentx
Hi, thanks for your reply - I've set my TNS_ADMIN environment variable to my wallet folder and used your code but now I'm getting a KeyError. — gblawrence03
My code uses username and password that are set as environment variables ora_cloud_usr and ora_cloud_pwd respectively. Just replace them with actual username and password. — astentx
I've tried that, and then I reverted it back to the code I had originally, and now it's working? No idea what I did. Sorry if I wasted your time haha — gblawrence03
You are using the Easy Connect syntax so you don't need to set TNS_ADMIN. What I don't see in your question is the port number in the connection string; was it really the default 1521? For general reference here is a post showing how to create an Easy Connect string for Oracle ABD connections: blogs.oracle.com/opal/… and here is the cx_Oracle doc on cloud connections cx-oracle.readthedocs.io/en/latest/user_guide/… — Christopher Jones

Christopher Jones Christopher Jones · Accepted Answer · 2021-07-29T04:18:18

To give detail from my comment: If your tnsnames.ora file contains entries like:

cjjson_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=abc_cjjson_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

then you can connect using

connect_string, = 'tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&retry_count=20&retry_delay=3'
con = cx_Oracle.connect("ADMIN", userpwd, connect_string)

With this syntax, you only need the cwallet.sso file from the downloaded cloud wallet. You don't need tnsnames.ora or sqlnet.ora files. Also you don't need to set TNS_ADMIN. You just need to change /Users/cjones/Cloud/CJJSON in the connect string to the directory containing your cwallet.sso

See the blog post How to connect to Oracle Autonomous Cloud Databases which gives information about getting the wallet and the cx_Oracle documentation Connecting to Oracle Cloud Autononmous Databases.

Footnote: the encoding parameter defaults to UTF-8 in cx_Oracle 8 onwards.

Trying to connect to Oracle Always Free database using cx_Oracle

1 Answers