Can not connect via Thin mode.

[mikekenn]

While trying to connect via thin mode, I keep getting the following error:

`Traceback (most recent call last):
File "src/oracledb/impl/thin/connection.pyx", line 101, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "src/oracledb/impl/thin/protocol.pyx", line 150, in oracledb.thin_impl.Protocol._connect_phase_one
File "src/oracledb/impl/thin/protocol.pyx", line 287, in oracledb.thin_impl.Protocol._connect_tcp
File "src/oracledb/impl/thin/crypto.pyx", line 139, in oracledb.thin_impl.get_ssl_socket
File "src/oracledb/impl/thin/crypto.pyx", line 149, in oracledb.thin_impl.perform_tls_negotiation
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/ssl.py", line 517, in wrap_socket
return self.sslsocket_class._create(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/ssl.py", line 1075, in _create
self.do_handshake()
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/ssl.py", line 1346, in do_handshake
self._sslobj.do_handshake()
ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:992)

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

Traceback (most recent call last):
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/manage.py", line 22, in
main()
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/manage.py", line 18, in main
execute_from_command_line(sys.argv)
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/venv/lib/python3.11/site-packages/django/core/management/init.py", line 446, in execute_from_command_line
utility.execute()
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/venv/lib/python3.11/site-packages/django/core/management/init.py", line 386, in execute
settings.INSTALLED_APPS
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/venv/lib/python3.11/site-packages/django/conf/init.py", line 92, in getattr
self._setup(name)
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/venv/lib/python3.11/site-packages/django/conf/init.py", line 79, in _setup
self._wrapped = Settings(settings_module)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/venv/lib/python3.11/site-packages/django/conf/init.py", line 190, in init
mod = importlib.import_module(self.SETTINGS_MODULE)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/importlib/init.py", line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "", line 1206, in _gcd_import
File "", line 1178, in _find_and_load
File "", line 1149, in _find_and_load_unlocked
File "", line 690, in _load_unlocked
File "", line 940, in exec_module
File "", line 241, in _call_with_frames_removed
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/config/settings.py", line 25, in
connection = oracledb.connect(user=os.environ.get('DB_USER'), password=os.environ.get('DB_PASSWORD'), dsn=os.environ.get('DB_DSN'))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/venv/lib/python3.11/site-packages/oracledb/connection.py", line 1013, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/venv/lib/python3.11/site-packages/oracledb/connection.py", line 135, in init
impl.connect(params_impl)
File "src/oracledb/impl/thin/connection.pyx", line 318, in oracledb.thin_impl.ThinConnImpl.connect
File "src/oracledb/impl/thin/connection.pyx", line 206, in oracledb.thin_impl.ThinConnImpl._connect_with_params
File "src/oracledb/impl/thin/connection.pyx", line 177, in oracledb.thin_impl.ThinConnImpl._connect_with_description
File "src/oracledb/impl/thin/connection.pyx", line 113, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "/Users/michael/Projects/ExamplesBeforeGitHub/myproject/venv/lib/python3.11/site-packages/oracledb/errors.py", line 111, in _raise_err
raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database. Connection failed with "[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:992)"`

I have followed this guide: https://docs.oracle.com/en-us/iaas/autonomous-database-shared/doc/connecting-python-tls.html#GUID-CA446B91-BC48-4A66-BF69-B8D54B9CBAD4

and this guide: https://blogs.oracle.com/opal/post/easy-way-to-connect-python-applications-to-oracle-autonomous-databases

And still am getting the above error.

I can confirm the following are my settings on the database.

I can also confirm I am using the TLS connection string on port 1521 and not the mTLS connection string on port 1522.

Would someone be able to assist in resolving this?

[anthony-tuininga]

What server are you actually connecting to? Is this server using a self-signed certificate as the error suggests? If so, you'll need to add the certificate authority (of the server) to the certificate authorities recognized by your OS -- or you need to set up a "wallet" that contains the ceritificates needed to authenticate the server.

[anthony-tuininga]

What server are you trying to connect to? In which domain if you don't want to give the full server name? That might help us help you with helping the client recognize the self-signed certificate.

[mikekenn]

What server are you trying to connect to? In which domain if you don't want to give the full server name? That might help us help you with helping the client recognize the self-signed certificate.

Are you looking for the host name for the database?

[anthony-tuininga]

Yes.

[mikekenn]

Yes.

host=adb.ca-montreal-1.oraclecloud.com

[anthony-tuininga]

Ok. I tried connecting to that server and it accepted my connection (but refused it ultimately because I didn't have a valid service name). I was able to connect successfully on both Linux and macOS. This was with Python 3.11.1 on macOS and Python 3.11.2 on Linux. Can you confirm that the connection string is what you think it is?

[tcbenkhard]

I am running into this same issue and testing my configuration using the instantclient shows that it is correct.

[cjbj]

Is this the first time you've tried python-oracledb Thin mode, or has something that used to work broken for you? Also keep an eye on the connect strings, since the cloud config has been changing regarding DN matching. I had no issue connecting today the Cloud with Thin mode.

[tcbenkhard]

after some investigation it appears that only thick mode will use sqlnet.ora. Thin mode will require you to specify the wallet_location in the connection string ?wallet_location=/some/directory and the wallet password.

[cjbj]

Yes. Doc is at https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#connecting-to-oracle-cloud-autonomous-databases

[cjbj]

There is no capability to pass some of that config info from the driver down to OCI to the Oracle Net layer in Thick mode.

[mikekenn]

The answer for me was the following stack overflow article.

The command I had to run was "/Applications/Python\ 3.11/install\ Certificates.command”. Change the [3.11] to whatever version of python you are using in your virtual env.

Thank you very much @cjbj and @anthony-tuininga for all your assistance in troubleshooting the issue offline!

[cjbj]

Thanks for sharing the solution! For ease of findability, other macOS users may need to run pip install certifi before running the above certificate installation command