Hello,
I am newbie learning Python and one of the first things I am trying to learn is about how to connect to Oracle database and do some of the basic things. I am struggling to see whether it is possible to connect to Oracle Database using one-way TLS in "Thin" mode. So far below are the things that I have tried. Can someone please help me to figure out what I am doing wrong and more importantly how can I figure out/diagnose this issue?
Python Version => 3.12
oracledb version => 2.4.1
Oracle Database Version => 19c (not sure if it makes any difference but this is ExaC@C setup)
- Connect to Oracle Database using TCPS protocol using “Thick” mode in Python <= Success
- Connect to Oracle Database using TCPS protocol using SQL*Plus and SQL*Developer < = Success
- Connect to Oracle Database using TCPS protocol using “Thin” mode in Python < = Failed
Test Code:
import getpass
import oracledb
pw = getpass.getpass("Enter password: ")
# Does not work
# connection = oracledb.connect(user="MY_USER1", password=pw, dsn="ORCL_SSL", config_dir="C:\\Users\\MYUSER\\owallet", wallet_location="C:\\Users\\MYUSER\\owallet")
# Does not work
connection = oracledb.connect(user="MY_USER1", password=pw, dsn="ORCL_SSL", config_dir="C:\\Users\\MYUSER\\owallet")
# Does not work
# connection = oracledb.connect(user="MY_USER1", password=pw, protocol='tcps', host='<DB Host>', port=2484, service_name='ORCLDB.test')
print("Successfully connected to Oracle Database")
input("Press Enter to continue...")
cur = connection.cursor()
cur.execute("select COUNTRY_ID, COUNTRY_NAME, REGION_ID from MY_COUNTRIES")
for cid, cname, reg in cur:
print("Country ID: ", cid)
print("Country name: ", cname)
print("Region ID:", reg)
cur.close()
connection.close()
Output of failed attempt:
C:\Users\MYUSER\My Drive\My Documents\PythonTutorials>python QueryOracleDemoTCPSThin.py
Enter password:
Traceback (most recent call last):
File "src\\oracledb\\impl/thin/connection.pyx", line 322, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "src\\oracledb\\impl/thin/protocol.pyx", line 225, in oracledb.thin_impl.Protocol._connect_phase_one
File "src\\oracledb\\impl/thin/protocol.pyx", line 380, in oracledb.thin_impl.Protocol._connect_tcp
File "src\\oracledb\\impl/thin/transport.pyx", line 244, in oracledb.thin_impl.Transport.negotiate_tls
File "C:\Users\MYUSER\AppData\Local\Programs\Python\Python312\Lib\ssl.py", line 455, in wrap_socket
return self.sslsocket_class._create(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\MYUSER\AppData\Local\Programs\Python\Python312\Lib\ssl.py", line 1042, in _create
self.do_handshake()
File "C:\Users\MYUSER\AppData\Local\Programs\Python\Python312\Lib\ssl.py", line 1320, in do_handshake
self._sslobj.do_handshake()
ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for '<DB Host>'. (_ssl.c:1000)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\MYUSER\My Drive\My Documents\PythonTutorials\QueryOracleDemoTCPSThin.py", line 10, in <module>
connection = oracledb.connect(user="MY_USER1", password=pw, dsn="ORCL_SSL", config_dir="C:\\Users\\MYUSER\\owallet")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\MYUSER\AppData\Local\Programs\Python\Python312\Lib\site-packages\oracledb\connection.py", line 1169, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\MYUSER\AppData\Local\Programs\Python\Python312\Lib\site-packages\oracledb\connection.py", line 551, in __init__
impl.connect(params_impl)
File "src\\oracledb\\impl/thin/connection.pyx", line 424, in oracledb.thin_impl.ThinConnImpl.connect
File "src\\oracledb\\impl/thin/connection.pyx", line 420, in oracledb.thin_impl.ThinConnImpl.connect
File "src\\oracledb\\impl/thin/protocol.pyx", line 380, in oracledb.thin_impl.Protocol._connect_tcp
File "src\\oracledb\\impl/thin/connection.pyx", line 361, in oracledb.thin_impl.ThinConnImpl._connect_with_description
File "src\\oracledb\\impl/thin/connection.pyx", line 331, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "C:\Users\MYUSER\AppData\Local\Programs\Python\Python312\Lib\site-packages\oracledb\errors.py", line 195, in _raise_err
raise error.exc_type(error) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=fYCjE4xFXIcerX4CuMVWXQ==).
[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for '<DB Host>'. (_ssl.c:1000)
I did search around on www but could only find limited resources like TLS connection to Oracle DB using node-oracledb with PEM file - Oracle Forums and a couple of posts on GitHub but I am unable to get my head around what am I doing wrong.
Thanks in advance