Using one way TLS in thin mode with python oracledb.connect() function params.

[bella-ad]

Hi everyone,

I have seen similar questions, but I feel no question coudl answer my specific case. Sorry If I have overlooked a matching quesiton.

I am using oracledb in version 2.10 and I try to use one way TLS with it. I have read about one way TLS here but I wondered if one way TLS can be established with using the python function parameters instead of a connection string.

I use this context manager of a class "Oracle" to provice a connection to the db:

 @contextmanager
     def connect(self):
         with oracledb.connect(
             user=self.user_name, password=self.password, host=self.host, service_name=self.service_name, port=self.port
         ) as connection:
             connection.autocommit = True
             with connection.cursor() as cursor:
                 yield cursor

This works perectly, but from my understanding this provides a not secure tcp protocol connection (please correct me if I am wrong here)

If I use the parameter protocol="tcps" to enable tsl I get an FileNotFoundError: [Errno 2] No such file or directory. I guess due to the fact that I have not created any wallet or provided any certificates. However, from my understanding for one way TLS we do not need a wallet or any files.

So my questions are:

1. Using the code provided, am I using insecure tcp?
2. How can I adjust my code so that I use one way TLS without having to create a wallet or any pem files for that? Ideally I would use additional parameters in the oracledb.connect function.

[anthony-tuininga]

Can you supply the full traceback that you get instead of just the exception message? That would be helpful.

[bella-ad]

of course!!

> 
> test_oracle.py:27 (test_select[SELECT * FROM TEST_SCHEMA.EMPLOYEES WHERE ID = 4-0])
> >   ???
> 
> src\\oracledb\\impl/thin/connection.pyx:279: 
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> src\\oracledb\\impl/thin/protocol.pyx:221: in oracledb.thin_impl.Protocol._connect_phase_one
>     ???
> src\\oracledb\\impl/thin/protocol.pyx:375: in oracledb.thin_impl.Protocol._connect_tcp
>     ???
> src\\oracledb\\impl/thin/transport.pyx:231: in oracledb.thin_impl.Transport.negotiate_tls
>     ???
> ..\..\..\..\AppData\Local\Programs\Python\Python311\Lib\ssl.py:517: in wrap_socket
>     return self.sslsocket_class._create(
> ..\..\..\..\AppData\Local\Programs\Python\Python311\Lib\ssl.py:1075: in _create
>     self.do_handshake()
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> 
> self = <ssl.SSLSocket [closed] fd=-1, family=2, type=1, proto=0>, block = False
> 
>     @_sslcopydoc
>     def do_handshake(self, block=False):
>         self._check_connected()
>         timeout = self.gettimeout()
>         try:
>             if timeout == 0.0 and block:
>                 self.settimeout(None)
> >           self._sslobj.do_handshake()
> E           FileNotFoundError: [Errno 2] No such file or directory
> 
> ..\..\..\..\AppData\Local\Programs\Python\Python311\Lib\ssl.py:1346: FileNotFoundError
> 
> The above exception was the direct cause of the following exception:
> 
> oracle_connection = Oracle(user_name='****', password='****', host='****', service_name='****', port=1521)
> query = 'SELECT * FROM TEST_SCHEMA.EMPLOYEES WHERE ID = 4', expected_row_nr = 0
> 
>     @pytest.mark.parametrize(
>         "query, expected_row_nr",
>         [
>             ("SELECT * FROM TEST_SCHEMA.EMPLOYEES WHERE ID = 4", 0),
>             ("SELECT * FROM TEST_SCHEMA.EMPLOYEES", 3),
>         ],
>     )
>     def test_select(oracle_connection, query, expected_row_nr):
> >       result = oracle_connection.select(query)
> 
> test_oracle.py:36: 
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> ..\..\connectors\connectors\oracle.py:66: in select
>     with self.connect() as cursor:
> ..\..\..\..\AppData\Local\Programs\Python\Python311\Lib\contextlib.py:137: in __enter__
>     return next(self.gen)
> ..\..\connectors\connectors\oracle.py:42: in connect
>     with oracledb.connect(
> ..\..\..\..\AppData\Local\pypoetry\Cache\virtualenvs\connectors-8Qyuq_4x-py3.11\Lib\site-packages\oracledb\connection.py:1158: in connect
>     return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
> ..\..\..\..\AppData\Local\pypoetry\Cache\virtualenvs\connectors-8Qyuq_4x-py3.11\Lib\site-packages\oracledb\connection.py:541: in __init__
>     impl.connect(params_impl)
> src\\oracledb\\impl/thin/connection.pyx:381: in oracledb.thin_impl.ThinConnImpl.connect
>     ???
> src\\oracledb\\impl/thin/connection.pyx:377: in oracledb.thin_impl.ThinConnImpl.connect
>     ???
> src\\oracledb\\impl/thin/connection.pyx:337: in oracledb.thin_impl.ThinConnImpl._connect_with_params
>     ???
> src\\oracledb\\impl/thin/connection.pyx:318: in oracledb.thin_impl.ThinConnImpl._connect_with_description
>     ???
> src\\oracledb\\impl/thin/connection.pyx:288: in oracledb.thin_impl.ThinConnImpl._connect_with_address
>     ???
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> 
> error_num = 6005, context_error_message = '[Errno 2] No such file or directory'
> cause = FileNotFoundError(2, 'No such file or directory')
> args = {'connection_id': 'FYfGp8Xo/UiBQ7Qhp2TPfQ=='}
> message = 'DPY-6005: cannot connect to database (CONNECTION_ID=FYfGp8Xo/UiBQ7Qhp2TPfQ==).\n[Errno 2] No such file or directory'
> error = <oracledb.errors._Error object at 0x000002CB56ECE5D0>
> 
>     def _raise_err(
>         error_num: int,
>         context_error_message: str = None,
>         cause: Exception = None,
>         **args,
>     ) -> None:
>         """
>         Raises a driver specific exception from the specified error number and
>         supplied arguments.
>         """
>         message = _get_error_text(error_num, **args)
>         if context_error_message is None and cause is not None:
>             context_error_message = str(cause)
>         if context_error_message is not None:
>             message = f"{message}\n{context_error_message}"
>         error = _Error(message)
> >       raise error.exc_type(error) from cause
> E       oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=FYfGp8Xo/UiBQ7Qhp2TPfQ==).
> E       [Errno 2] No such file or directory
> 

[anthony-tuininga]

The error is occurring inside the ssl library. Can you take a look at the offending line (..\..\..\..\AppData\Local\Programs\Python\Python311\Lib\ssl.py:1346: FileNotFoundError) and see what file is missing? That might be enlightening. Since this is on Windows and I don't use Windows much I can't venture a guess as to what might be the issue.

[anthony-tuininga]

I just tried this code:

import oracledb

with oracledb.connect(
    user="my_user",
    password="my_password",
    protocol="tcps",
    host="my_host",
    service_name="my_service_name"
) as conn:
    with conn.cursor() as cursor:
        cursor.execute(
            "select sys_context('userenv', 'service_name') from dual"
        )
        for row in cursor:
            print(row)

That works as expected for connecting to a database that supports one-way TLS using the default port of 1521. If you remove the protocol="tcps" argument then you will use insecure TCP.

[bella-ad]

Ahh good to know! I wonder, maybe only mTLS is enabled on db side. Could that be a reason?

[anthony-tuininga]

Usually if that is the case you get the error ORA-12506: TNS:listener rejected connection based on service ACL filtering.

[anthony-tuininga]

Note as well that the database has to actually support TCPS. If it doesn't on Linux you get this: [SSL: UNEXPECTED_EOF_WHILE_READING] EOF occurred in violation of protocol (_ssl.c:1000) but perhaps on Windows you get No such file or directory?

[bella-ad]

This is very interessting.
I tried to find out which file was missing as you suggested. I could not find it. I then tried to debug into the code to see where exactly it fails and I could not go deeper then where the exception is thrown. However, when using the debuger I actually get this error message at the end of the test. It looks much more like the one you predicted for linux:

error_num = 6005
context_error_message = 'EOF occurred in violation of protocol (_ssl.c:992)'
cause = SSLEOFError(8, 'EOF occurred in violation of protocol (_ssl.c:992)')
args = {'connection_id': 'ypXJM8ggkU05cH/hAEM1gg=='}
message = 'DPY-6005: cannot connect to database (CONNECTION_ID=ypXJM8ggkU05cH/hAEM1gg==).\nEOF occurred in violation of protocol (_ssl.c:992)'
error = <oracledb.errors._Error object at 0x000001ABC8154F90>

    def _raise_err(
        error_num: int,
        context_error_message: str = None,
        cause: Exception = None,
        **args,
    ) -> None:
        """
        Raises a driver specific exception from the specified error number and
        supplied arguments.
        """
        message = _get_error_text(error_num, **args)
        if context_error_message is None and cause is not None:
            context_error_message = str(cause)
        if context_error_message is not None:
            message = f"{message}\n{context_error_message}"
        error = _Error(message)
>       raise error.exc_type(error) from cause
E       oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=ypXJM8ggkU05cH/hAEM1gg==).
E       EOF occurred in violation of protocol (_ssl.c:992)

[bella-ad]

I will retry on a linux machine tomorrow to see if I encounter the [SSL: UNEXPECTED_EOF_WHILE_READING] EOF occurred in violation of protocol (_ssl.c:1000). Thereby, I can verify if the db support one way tls at all. Maybe this has been the issue all along - but windows just likes to prank us with its error messages..

[anthony-tuininga]

The owners of the database have to explicitly set up TLS. If this is a local database it is unlikely that has happened! And yes, the EOF occurred in violation of protocol sounds exactly like TLS is not set up -- either one-way or mutual TLS.

[bella-ad]

Jep - runnign it on a Linxu maschine returns the expected error. So you were on the right track. TLS is not enabled. Thank you so much for your help!