oracledb thin mode connect to oracle db using self signed certificate

[4kEEw1y4MSiDFW]

Hi.

I am trying to connect to an Oracle DB using a self signed certificate. The certificate is trusted in the system store already (verified using openssl s_client -connect).

However, it seems that I can't make the CA/cert trusted for oracledb libs.

import oracledb as cx_Oracle
cp = oracledb.ConnectParams(
    host="X",
    user="X",
    password="X",
    service_name="X",
    port=X,
    protocol="tcps",
    ssl_server_dn_match=False,
)
database_engine = oracledb.connect(params=cp)

Results in:

  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/connection.pyx", line 380, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  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 "/home/X/git/X/venv/lib/python3.12/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=X).
[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self-signed certificate in certificate chain (_ssl.c:1000)

I tried setting REQUESTS_CA_BUNDLE but that didn't work. I also installed certifi but that also had no effect.

[anthony-tuininga]

REQUESTS_CA_BUNDLE is only for the requests library and is not general purpose. The certifi package makes available commonly used certificate authorities -- since this is a self-signed certificate that won't help you any! It is possible that there is some configuration that is specific to your shell (where you run openssl) that differs when you are running your Python script. I don't know all of the ins and outs of SSL but perhaps someone else can help you daignose this issue for you. You can also create an Oracle wallet containing these certificates and pass the location to that file when creating your connection.

[ricklahaye]

REQUESTS_CA_BUNDLE is only for the requests library and is not general purpose. The certifi package makes available commonly used certificate authorities -- since this is a self-signed certificate that won't help you any! It is possible that there is some configuration that is specific to your shell (where you run openssl) that differs when you are running your Python script. I don't know all of the ins and outs of SSL but perhaps someone else can help you daignose this issue for you. You can also create an Oracle wallet containing these certificates and pass the location to that file when creating your connection.

Do you know how the library does the TLS validation ?

And you know how to create Oracle wallet using thin client ? I think you would need a full Oracle installation right to have access to orapki binary ?

[anthony-tuininga]

The library does TLS validation using this code. The negotiation can be found in this code. It is a very simple call to the SSL context wrap_socket() method.

There is no way to create an Oracle wallet using thin client today. I believe you do need a full Oracle client or database installation in order to create an Oracle wallet. You can do it on the machine hosting the database, though. It should be quite possible to connect to the database without doing so, but the configuration bit you happen to be missing is unknown to me, unfortunately.

[4kEEw1y4MSiDFW]

The library does TLS validation using this code. The negotiation can be found in this code. It is a very simple call to the SSL context wrap_socket() method.

There is no way to create an Oracle wallet using thin client today. I believe you do need a full Oracle client or database installation in order to create an Oracle wallet. You can do it on the machine hosting the database, though. It should be quite possible to connect to the database without doing so, but the configuration bit you happen to be missing is unknown to me, unfortunately.

Thanks. Creating a SSL context worked. Thank you!