Connect to Oracle Cloud Database with python oracledb

Question

I'm trying to connect to a newly created database in Oracle Cloud (https://cloud.oracle.com/db/adb/)

I've copied the connection string from DB Connection > Connection Strings > (One of the three listed.)

Which looks a little like this:

(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.uk-london-1.oraclecloud.com))(connect_data=(service_name=abc123xyzredacted.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

I'm authenticating using the "ADMIN" account that was created at DB creation along with its password.

Running the test.py script found here: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html#quickstart

import oracledb
import os

un = os.environ.get('PYTHON_USERNAME')
pw = os.environ.get('PYTHON_PASSWORD')
cs = os.environ.get('PYTHON_CONNECTSTRING')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):
            print(r)

I get

% python test.py
Traceback (most recent call last):
  File "src/oracledb/impl/thin/connection.pyx", line 227, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src/oracledb/impl/thin/crypto.pyx", line 125, in oracledb.thin_impl.get_ssl_socket
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 500, in wrap_socket
    return self.sslsocket_class._create(
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 1040, in _create
    self.do_handshake()
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 1309, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLError: [SSL: SSLV3_ALERT_HANDSHAKE_FAILURE] sslv3 alert handshake failure (_ssl.c:1129)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/test.py", line 15, in <module>
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
  File ".venv/lib/python3.9/site-packages/oracledb/connection.py", line 1000, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File ".venv/lib/python3.9/site-packages/oracledb/connection.py", line 128, in __init__
    impl.connect(params_impl)
  File "src/oracledb/impl/thin/connection.pyx", line 345, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 163, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src/oracledb/impl/thin/connection.pyx", line 129, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src/oracledb/impl/thin/connection.pyx", line 247, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File ".venv/lib/python3.9/site-packages/oracledb/errors.py", line 103, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database. Connection failed with "[SSL: SSLV3_ALERT_HANDSHAKE_FAILURE] sslv3 alert handshake failure (_ssl.c:1129)"

Am I using the wrong connection string? Should I create another user?

Additional:

I've just found this: https://blogs.oracle.com/opal/post/easy-way-to-connect-python-applications-to-oracle-autonomous-databases

So I now have "Network" showing:

Access Type: Allow secure access from specified IPs and VCNs
Access Control List: Enabled Mutual TLS (mTLS)
Authentication: Not Required

Where my IP address is entered in Access Control List.

You are connecting to the server; however the connection is being denied as the SSL handshake is not completing. Have you tried downgrading from SSLv3 to TLS1.2? — MT0
– MT0, Commented Jun 29, 2022 at 13:14
Are you using the Oracle Wallet provided by Oracle to access the database, and Oracle Instant Client? The specific SSL certificates in the wallet are required. — pmdba
– pmdba, Commented Jun 29, 2022 at 13:24
I'm not using the wallet. I'm trying to use "walletless" access. Question updated with some details. — powlo
– powlo, Commented Jun 29, 2022 at 13:38
Looks like you are using port 1522 which is generally reserved for mTLS (requires a wallet). You want to use port 1521 if you are using plain TLS (does not require a wallet). — Anthony Tuininga
– Anthony Tuininga, Commented Jun 29, 2022 at 15:49
Because of the use of SSL and PKI certificates, ADB can only be connected to using the provided wallet and predefined TNS names. There is no "walletless" connection. — pmdba
– pmdba, Commented Jun 29, 2022 at 19:33

Christopher Jones · Accepted Answer · 2022-06-29 22:05:49Z

2

You have:

Access Control List: Enabled Mutual TLS (mTLS)

which seems incorrect.

This is what my cloud console shows when I have 1-way (aka walletless) authentication enabled:

Network
Access Type: Allow secure access from specified IPs and VCNs
Access Control List: Enabled
Mutual TLS (mTLS) Authentication: Not Required

When you copy the connection string from the cloud console, make sure to select the correct TLS (not mTLS) value in the dropdown box just above, because the connection string changes.

Check your current IP address is in the ACL list, because IPs addresses are often not static !

answered Jun 29, 2022 at 22:05

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

powlo Over a year ago

"make sure to select the correct TLS (not mTLS)" - this is it! Thank you!

Collectives™ on Stack Overflow

Connect to Oracle Cloud Database with python oracledb

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related