Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Certificate error while using TLS and SCAN #415

Open
shaunmugam opened this issue Nov 1, 2024 · 18 comments
Open

Certificate error while using TLS and SCAN #415

shaunmugam opened this issue Nov 1, 2024 · 18 comments
Labels
enhancement New feature or request patch available

Comments

@shaunmugam
Copy link

shaunmugam commented Nov 1, 2024 •
edited by anthony-tuininga
Loading

  1. 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)

  1. Is it an error or a hang or a crash?

Error

  1. 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.

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='%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)

  1. Does your application call init_oracle_client()?

No. Using thin mode

  1. 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)
@shaunmugam shaunmugam added the bug Something isn't working label Nov 1, 2024
@anthony-tuininga anthony-tuininga added question Further information is requested and removed bug Something isn't working labels Nov 1, 2024
@anthony-tuininga
Copy link
Member

I don't believe this is a bug.

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 code is currently making use of Python's SSL context server name validation. The fact that JDBC and Oracle thick client are happy with using the alternative name doesn't really say that Python's approach is wrong! Can you explain further why you believe that Python's SSL context server name validation is incorrect? And why do you not want to use the officially registered name?

@shaunmugam
Copy link
Author

shaunmugam commented Nov 1, 2024 via email

@anthony-tuininga
Copy link
Member

This is the relevant Python documentation. When you set ssl_server_dn_match, this enables hostname checking and that is the issue you are running into. The python-oracledb implementation does not do anything more than enable that flag. I understand that the JDBC and thick client do something different. I assume you are asking for thin mode to implement something different? You can also diasble ssl_server_dn_match or create your own ssl_context parameter which does what you wish it to do. If that isn't acceptable, we can make this an enhancement request instead. Thoughts?

@shaunmugam
Copy link
Author

shaunmugam commented Nov 1, 2024 via email

@anthony-tuininga anthony-tuininga added enhancement New feature or request and removed question Further information is requested labels Nov 1, 2024
@cjbj
Copy link
Member

cjbj commented Nov 1, 2024

@shaunmugam what's the exact 19c DB version you have?

@shaunmugam
Copy link
Author

shaunmugam commented Nov 1, 2024 via email

@shaunmugam
Copy link
Author

shaunmugam commented Nov 1, 2024 via email

@cjbj
Copy link
Member

cjbj commented Nov 2, 2024

cx_Oracle and python-oracledb Thick are really the same thing - all the DB connectivity is handled in Oracle Client libraries.

I get your point that if some environments work, other environments might also be expected to. However if those environments have different levels of support for some technologies or options, than it may not be possible.

Let us know what you find about adding the registered name as an alternate name.

We'll also check with the Oracle Net Services team and see what they might suggest.

@shaunmugam
Copy link
Author

shaunmugam commented Nov 2, 2024 via email

@cjbj
Copy link
Member

cjbj commented Nov 2, 2024

@shaunmugam to help us understand user requirements, I would be interested to know your specific reason(s) for preferring Thin mode over Thick mode. I am imagining you have a large Oracle environment. In particular do you not want to use AQ or Application Continuity/TAC, which aren't available in Thin mode? Or do you already use Thick mode where it has features you need?

@shaunmugam
Copy link
Author

shaunmugam commented Nov 4, 2024 via email

anthony-tuininga added a commit that referenced this issue Nov 8, 2024
@anthony-tuininga
Perform TLS server matching in the driver instead of in the SSL library
f5f2cf7 
(#415).
@anthony-tuininga
Copy link
Member

I have pushed a patch that adds this support and have initated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer. If you can test your scenario and confirm the patch works as expected, that would be appreciated!

@shaunmugam
Copy link
Author

shaunmugam commented Nov 8, 2024 via email

@cjbj
Copy link
Member

cjbj commented Nov 25, 2024

@shaunmugam any update?

@shaunmugam
Copy link
Author

shaunmugam commented Nov 25, 2024 via email

@shaunmugam
Copy link
Author

shaunmugam commented Dec 26, 2024 via email

@anthony-tuininga
Copy link
Member

Glad to hear that it worked as you expected! Yes, it will be included in the next version that is released. The exact timing is still being determined yet, though!

@shaunmugam
Copy link
Author

shaunmugam commented Dec 26, 2024 via email

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request patch available
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@cjbj @anthony-tuininga @shaunmugam