Kerberos Support for On-prem Kerberized Oracle databases

[aadishsanghvi]

Describe the Feature

I am trying to use this adapter to connect to my Oracle database which only supports kerberos for authentication and username/password is not supported. Can we please add this feature to the adapter or show me how to leverage this if already implemented ?

Describe alternatives you've considered

No response

Who will this benefit?

All users of a kerberized Oracle database

Anything else?

No response

[aosingh]

We are investigating this. We use python-oracledb as the underlying database driver. So, any external authentication method needs to be supported by the driver

[aadishsanghvi]

As I understand python-oracledb does support kerberos auth in thick mode

[aosingh]

@aadishsanghvi

Could you let me know how do you connect to Oracle Database using SQLPlus and Kerberos Authentication ? I am interested in understanding the SQL net configuration used. Also, have you tried the thick mode ?

I am trying to setup Kerberos instance to test the setup but that might take a few days.

[aadishsanghvi]

I am not sure about sql plus. but I have an oracle instance which supports only kerberos authentication, I was able to connect to it using cx_Oracle which is also a python library very similar to python-oracledb, this requires an instant client which has a sqlnet.ora file configuration pointing to the location of your krb5.conf file and the keberos credential cache file, then you use the drivers provided by the cx_oracle and define your instance's dsn etc etc.

[aosingh]

@aadishsanghvi

I know of cx_Oracle and python-oracledb. Could you share the code using cx_Oracle to connect using Kerberos ?

[aadishsanghvi]

Hi Sorry for the delay.

firstly make sure you have a thick client installed on your system, then add a sqlnet.ora file with following configs in this directory of your client - oracle_client_21_basic\instantclient_21_9\network\admin

config of sqlnet.ora
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle
SQLNET.AUTHENTICATION_SERVICES= (KERBEROS5)
SQLNET.KERBEROS5_CC_NAME = ${KRB5CCNAME} // credential cache
SQLNET.KERBEROS5_CONF = ${KRB5_CONFIG} // krb5 config
sqlnet.kerberos5_conf_mit=true

set the env variable ORACLE_HOME= oracle_client_21_basic\instantclient_21_9

python set up-

ORA_SRVR = <your_host_name>'
ORA_PORT = '<port_number>'
ORA_SRVC = '<service_name>'

DB_DSN = cx_Oracle.makedsn(ORA_SRVR, ORA_PORT, service_name=ORA_SRVC)

Connect with cx_oracle or oracledb package for kerberos environment like this:-

conn = cx_Oracle.connect(dsn=DB_DSN)

[aadishsanghvi]

Hey @aosingh can I get a timeline on this, this integration would save us a lot of time, let me know if you need my org to go through proper Oracle vendor channels to expedite this feature.

[aosingh]

Hi @aadishsanghvi

We are working on it. The plan is to make this a part of dbt-oracle==1.9.0 which we plan to release in January 2025.

[aadishsanghvi]

Hi @aosingh are you planning to scope this in 1.9.0? By when can we expect this

[aosingh]

@aadishsanghvi

Yes. I hope to release in 2 weeks.

I will release a candidate version dbt-oracle==1.9.0rc1 before and it will be great if you could test with it and let me know the feedback.

Also, dbt-oracle is open sourced and licensed under Apache License 2.0. We try and deliver all fixes and features as soon as possible. We highly appreciate any help or contributions from the community. An example is, helping us test the rc versions or submitting a PR for urgent features which you want to see in the adapter. Please read the contributing guide here

[aadishsanghvi]

That is great news.

Yes I would be more than happy to test and possibly contribute too.

[aosingh]

@aadishsanghvi

Could you test with the release candidate version ? Please have the latest version of Oracle Instant Client libraries installed.

1. Install dbt-oracle

pip install dbt-oracle==1.9.0rc1

2. Place sqlnet.ora and tnsnames.ora in a directory and set the following environment variables

export TNS_ADMIN=/path/to/dir/containing/ora/net/files
export ORA_PYTHON_DRIVER_TYPE=thick
export DBT_ORACLE_USER=<username>
export DBT_ORACLE_SCHEMA=<schema>
export DBT_ORACLE_DATABASE=example_db2022adb
export DBT_ORACLE_TNS_NAME=db2022adb_high

3. Create dbt profile.yml file without password. The tnsnames.ora file should contain the tns_name mapped to connect descriptor.

   dbt_test:
   target: dev
   outputs:
      dev:
         type: oracle
         user: "{{ env_var('DBT_ORACLE_USER') }}"
         schema: "{{ env_var('DBT_ORACLE_SCHEMA') }}"
         database: "{{ env_var('DBT_ORACLE_DATABASE') }}"
         tns_name: "{{ env_var('DBT_ORACLE_TNS_NAME') }}"

4. Obtain a kerberos ticket

kinit

5. Run dbt and test connection

dbt --debug debug --profiles-dir ./

[aadishsanghvi]

Thanks @aosingh I will test and let you know