dbt-oracle with LDAP connection support

[VenkatArra]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

I did not find any documentation on how to establish connection using LDAP.

Expected Behavior

Share documentation and samples on how to establish connection using LDAP. Like any additional setup needed, how profiles.yml look etc.

Steps To Reproduce

Mac/Linux.

Relevant log output using --debug flag enabled

NA

Environment

- OS:Mac/Linux
- Python:3.10.9
- dbt: Core 1.4.99
12:04:46  oracle adapter: Running in thin mode

What Oracle database version are you using dbt with?

19c, 21c

Additional Context

No response

[aosingh]

dbt-oracle uses python-oracledb for Connecting to Database. We will check if the Python driver supports LDAP authentication

[aosingh]

@VenkatArra

Could you give some details ?

• is the database On-prem or Autonomous Database in Cloud ?
• is the LDAP server in OCI ?
• Have you tried the thick mode of the python driver with dbt-oracle? In thick mode the Python driver will read sqlnet.ora and ldap.ora

To enable the thick mode, you can set ORA_PYTHON_DRIVER_TYPE and run dbt:

export ORA_PYTHON_DRIVER_TYPE=thick && dbt <command>

Note that thick mode needs access to the Oracle Client Libraries installed. You can follow instructions here : https://docs.getdbt.com/docs/core/connect-data-platform/oracle-setup#install-oracle-instant-client-libraries

[VenkatArra]

@aosingh Thanks for looking into this.

Please find my answers below.

• Database is not on-prem but remote either in public or private cloud.
• I don't think LDAP server in OCI
• I did not try thick driver mode yet. After I install Oracle client libs and setup thick mode, what configuration is needed?
  For example how profiles.yml look, do I need to put sqlnet.ora and ldap.ora in some folder etc.

Please share any other information and may be samples with LDAP if available. Thanks again.

[aosingh]

Hi @VenkatArra

For LDAP connection, Could you try the following:

• Install 23ai Oracle Client
• Use the following dbt profile template

dbt_test:
   target: "{{ env_var('DBT_TARGET', 'dev') }}"
   outputs:
      dev:
         type: oracle
         user: "{{ env_var('DBT_ORACLE_USER') }}"
         pass: "{{ env_var('DBT_ORACLE_PASSWORD') }}"
         database: "{{ env_var('DBT_ORACLE_DATABASE') }}"
         schema: "{{ env_var('DBT_ORACLE_SCHEMA') }}"
         connection_string: "{{ env_var('DBT_ORACLE_CONNECT_STRING') }}"

• For LDAP connection string, below are a few examples

ldaps://ldapserver.example.com/cn=orcl,cn=OracleContext,dc=example,dc=com

and

ldaps://ldapserver.example.com/cn=orcl,cn=OracleContext,dc=example,dc=com?DIRECTORY_SERVER_TYPE=AD&WALLET_LOCATION=/app/wallet&AUTHENTICATE_BIND=true&AUTHENTICATE_BIND_METHOD=LDAPS_SIMPLE_AUTH

• Finally, make sure to use the thick mode

export ORA_PYTHON_DRIVER_TYPE=thick && dbt <command>

[aosingh]

@VenkatArra Were you able to verify LDAP connection ?

[VenkatArra]

@aosingh Thanks for the instructions for LDAP connection. Since it involves thick driver mode and other setup we are not planning to do this currently. We'd like to continue with thin mode for now without LDAP.

[cjbj]

python-oracledb 2.5 has a register_protocol() feature that can help in Thin mode. There is an example in https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#ldap-directory-naming of registering a method to handle LDAP lookup when the ldap:// connection protocol is ued.