Certificate error while using TLS and SCAN · Issue #415 · oracle/python-oracledb (original) (raw)

What versions are you using?

Oracle 19c database
oracledb 2.4.1

import oracledb as cx

dsn_tns='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = cluster-scan)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = db_service))(security=(ssl_server_dn_match=yes)))'

sql = """SELECT sys_context('USERENV', 'NETWORK_PROTOCOL'), ora_database_name FROM dual"""

conn = cx.connect(user='myuser', password='mypass', dsn=dsn_tns)
cursor = conn.cursor()
result = cursor.execute(sql)

for row in result:
print(row)

Output:

PS C:\Users\cn131304\OneDrive - Centene Corporation\Documents\learning\python> & "C:/Program Files/Python311/python.exe" "c:/Users/cn131304/OneDrive - Centene Corporation/Documents/learning/python/ora2.py"
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:\Program Files\Python311\Lib\ssl.py", line 517, in wrap_socket
return self.sslsocket_class._create(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Program Files\Python311\Lib\ssl.py", line 1075, in _create
self.do_handshake()
File "C:\Program Files\Python311\Lib\ssl.py", line 1346, in do_handshake
self._sslobj.do_handshake()
ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for 'cluster_scan'. (_ssl.c:992)

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

Traceback (most recent call last):
File "c:\Users\cn131304\OneDrive - Centene Corporation\Documents\learning\python\ora2.py", line 7, in
conn = cx.connect(user='a_cn131304', password='mypass', dsn=dsn_tns)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\cn131304\AppData\Roaming\Python\Python311\site-packages\oracledb\connection.py", line 1169, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\cn131304\AppData\Roaming\Python\Python311\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\cn131304\AppData\Roaming\Python\Python311\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=mF4qb0/Xnv/m66jKv3Lz1w==).
[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for 'cluster_scan'. (_ssl.c:992)

Is it an error or a hang or a crash?

Error

What error(s) or behavior you are seeing?

We only get the error if using the cluster_scan (to which the SSL certificate is issued). The cluster_scan has cnames that are defined as subject alternate names in the certificate request. The code completes successfully if using the cname for cluster_scan.

We are not getting any errors while using JDBC or Oracle thick client configuration using either the cluster_scan or its cnames. So, we know the certificate is valid.

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

Traceback (most recent call last):
File "c:\Users\cn131304\OneDrive - Centene Corporation\Documents\learning\python\ora2.py", line 7, in
conn = cx.connect(user='a_cn131304', password='%0qwxAwFj3M).Jt9)}2oIz<p', dsn=dsn_tns)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\cn131304\AppData\Roaming\Python\Python311\site-packages\oracledb\connection.py", line 1169, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\cn131304\AppData\Roaming\Python\Python311\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\cn131304\AppData\Roaming\Python\Python311\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=mF4qb0/Xnv/m66jKv3Lz1w==).
[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for 'cluster_scan'. (_ssl.c:992)

Does your application call init_oracle_client()?

No. Using thin mode

Include a runnable Python script that shows the problem.

import oracledb as cx

dsn_tns='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = cluster_scan)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = db_service))(security=(ssl_server_dn_match=yes)))'

sql = """SELECT sys_context('USERENV', 'NETWORK_PROTOCOL'), ora_database_name FROM dual"""

conn = cx.connect(user='myuser', password='mypass', dsn=dsn_tns)
cursor = conn.cursor()
result = cursor.execute(sql)

for row in result:
print(row)