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!