thick mode connect isn't honoring TNS_ADMIN or using ldap connection

[slords]

1. What versions are you using?

platform.platform: macOS-15.0-arm64-arm-64bit
sys.maxsize > 2**32: True
platform.python_version: 3.12.6
oracledb.version: 2.4.1

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

Error

3. What error(s) or behavior you are seeing?

When trying to connect to a database I'm getting the following error:

$ env | grep -E 'ORA|TNS|PATH'
TNS_ADMIN=/opt/oracle
PATH=/opt/oracle/instantclient_23_3:...
INFOPATH=/opt/homebrew/share/info:
ORACLE_HOME=/opt/oracle/instantclient_23_3

$ python test.py
Traceback (most recent call last):
File "/Users/slords/test.py", line 8, in
c=oracledb.connect(user=user, password=password, dsn=dsn)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/slords/.venv/lib/python3.12/site-packages/oracledb/connection.py", line 1169, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/slords/.venv/lib/python3.12/site-packages/oracledb/connection.py", line 554, in init
impl.connect(params_impl, pool_impl)
File "src/oracledb/impl/thick/connection.pyx", line 494, in oracledb.thick_impl.ThickConnImpl.connect
File "src/oracledb/impl/thick/utils.pyx", line 446, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-12154: Cannot connect to database. Could not find alias whse_dev in /opt/oracle/instantclient_23_3/network/admin/tnsnames.ora.
Help: https://docs.oracle.com/error-help/db/ora-12154/

If I run the same connect using sqlplus it connects fine to the database. I've got sqlnet.ora and ldap.ora in /opt/oracle. We don't use tnsnames.ora and that file has never existed in any of our installs.

4. Does your application call init_oracle_client()?

Yes

5. Include a runnable Python script that shows the problem.

$ cat test.py
import oracledb
oracledb.init_oracle_client(lib_dir='/opt/oracle/instantclient_23_3')

user='user'
password='pass'
dsn='ldap_alias'

c=oracledb.connect(user=user, password=password, dsn=dsn)

[cjbj]

A couple of things to try:

• unset ORACLE_HOME - this should never be set when using Instant Client

• move the sqlnet.ora & ldap.ora files to default location /opt/oracle/instantclient_23_3/network/admin/ and also unset TNS_ADMIN

• Enable SQL*Net tracing and check how it is loading the sqlnet.ora / ldap.ora files. I create $HOME/.sqlnet.ora (note the leading period) with something like:

  adr_base=/tmp
  diag_adr_enabled=on
  trace_level_client=16
  

  You can have multiple sqlnet.ora / .sqlnet.ora files: they all get read.

  Then check files in /tmp/oradiag_*/diag/clients/user_cjones/host_*/trace/ora_*.trc

  Compare with the traces for SQL*Plus.

• Try the new LDAP URL syntax which removes the need for sqlnet.ora and ldap.ora, see Oracle Client 23ai
  LDAP URL Syntax.

[slords]

I've unset the variables you suggested, copied the .ora files to the admin directory and enabled tracing but it hasn't helped the situation at all. There is something different between oracledb using the oci layer and sqlplus. They are both reading the sqlnet.ora and ldap.ora files correctly and I can see the attempt to connect to the ldap server. However the oracledb connection is failing.

This is the part of the trc where they differ.

sqlplus:

I:2024-09-25 08:21:56.364901 : nnflilc: Setting non-blocking LDAP connect timeout to 5
C:2024-09-25 08:21:56.364907 : nnflilc: Opening sync conn to dwsora.state.ut.us:389
S:2024-09-25 08:21:56.403250 : nnflalc:entry
S:2024-09-25 08:21:56.403301 : nnflalc:doing ldap_bind_s
I:2024-09-25 08:21:56.430170 : nnflalc:bind(128) <null> with <null> returns 0x0
S:2024-09-25 08:21:56.430354 : nnflalc:exit
S:2024-09-25 08:21:56.430395 : nnflilc:exit
S:2024-09-25 08:21:56.430426 : nnflrlc:exit
S:2024-09-25 08:21:56.430452 : nnflobc:exit
S:2024-09-25 08:21:56.430495 : nnflcgc:exit
S:2024-09-25 08:21:56.430518 : nnflboot:exit
S:2024-09-25 08:21:56.430545 : nnfln2a:entry
S:2024-09-25 08:21:56.430567 : nnflcgc:entry
S:2024-09-25 08:21:56.430601 : nnflcgc:exit
S:2024-09-25 08:21:56.430698 : nnflrne1:entry
S:2024-09-25 08:21:56.430762 : nnfln2x:entry
S:2024-09-25 08:21:56.430803 : nnfln2x:exit
M:2024-09-25 08:21:56.430825 : nnflrne1:Quering the directory for dn: cn=playground,cn=OracleContext,dc=WORLD

oracledb:

I:2024-09-25 08:17:38.312662 : nnflilc: Setting non-blocking LDAP connect timeout to 5
C:2024-09-25 08:17:38.312688 : nnflilc: Opening sync conn to dwsora.state.ut.us:389
S:2024-09-25 08:17:38.341298 : nnflalc:entry
S:2024-09-25 08:17:38.341322 : nnflalc:doing ldap_bind_s
I:2024-09-25 08:17:38.367182 : nnflalc:bind(128) <null> with <null> returns 0x2
S:2024-09-25 08:17:38.367207 : nnflalc:exit
S:2024-09-25 08:17:38.367223 : nnflilc:exit
S:2024-09-25 08:17:38.367239 : nnflpsl:entry
S:2024-09-25 08:17:38.367254 : nnflgcp:entry
M:2024-09-25 08:17:38.367270 : nnflgcp:can't get config parameter DIRECTORY_CONNECT_METHOD
S:2024-09-25 08:17:38.367286 : nnflgcp:exit
S:2024-09-25 08:17:38.367302 : nnflgcp:entry
M:2024-09-25 08:17:38.367317 : nnflgcp:can't get config parameter DIRECTORY_SERVERS
S:2024-09-25 08:17:38.367332 : nnflgcp:exit
S:2024-09-25 08:17:38.367346 : nnflpsl:can't get server'
S:2024-09-25 08:17:38.367360 : nnflpsl:exit
S:2024-09-25 08:17:38.367374 : nnflrlc:exit

I'm not sure what the difference is or why oracledb is getting an 0x2 return and then failing to get the server.

[anthony-tuininga]

Can you confirm that both SQL*Plus and python-oracledb are using the same Oracle Client (/opt/oracle/instantclient_23_3)?

[slords]

Yes they are. It is the only client installed.

[anthony-tuininga]

Some more questions from the network experts at Oracle:

• Have you installed OpenLDAP?
• Can you check the libraries loaded in the Python process by setting DYLD_PRINT_LIBRARIES=YES and see if libldap is loaded? (https://stackoverflow.com/questions/38549523/how-to-find-which-shared-library-is-loaded-by-a-process-on-osx)

[slords]

Yes I have openldap installed. Setting DYLD_PRINT_LIBRARIES=YES does not show it trying to load openldap.

$ brew list | grep -i ldap
openldap
$ DYLD_PRINT_LIBRARIES=YES python test.py 2>&1 | grep -i ldap
dyld[13208]: <0F91824C-19C9-3A36-AA83-E2CFE0FF5FB3> /System/Library/Frameworks/LDAP.framework/Versions/A/LDAP
dyld[13208]: <0F91824C-19C9-3A36-AA83-E2CFE0FF5FB3> /System/Library/Frameworks/LDAP.framework/Versions/A/LDAP
oracledb.exceptions.DatabaseError: ORA-12154: Cannot connect to database. Could not find alias ldap_alias in /opt/oracle/instantclient_23_3/network/admin/tnsnames.ora.

[sreguna]

If possible can you uninstall openldap and run the application? Oracle client libraries come bundled with its own ldap library and there could be some interference with openldap. Could be worth a shot.

[slords]

There are not changes between having openldap installed and not.