Oracle differences between SQLAlchemy and TNS integration.

[mdeakyne]

The below code connects to an Oracle Database - using tnsnames.ora, in TNS_ADMIN / ORACLE_HOME.

from os import getenv
from sqlalchemy import create_engine
import pandas as pd

con_string = f'oracle+cx_oracle://{getenv("user")}:{getenv("db_pwd")}@DEMISDW'
conn = create_engine(con_string)

test = pd.read_sql(query, conn)
print(test) #works!

Using connectorX

import connectorx as cx
test = cx.read_sql(f"oracle://{getenv('user')}:{getenv('db_pwd')}@DEMISDW", query = query)
#RuntimeError: timed out waiting for connection: OCI Error: ORA-12154: TNS:could not resolve the connect identifier specified

I've tried reconfiguring the host to various formats, and urls - even directly substituting the full string from the TNS File:

DEMISDW = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=<service>)))

I've also run SQL to try to get other values to try:

SELECT * FROM SYS.GLOBAL_NAME; --Gets Service Name
SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') FROM DUAL; --Gets host

Can you provide some more insight on how the TNS works? Is there a location the library looks to? Or an environment variable it pulls from?

Thanks for any guidance you can provide.

[wseaton]

@mdeakyne, the underlying library that connects to oracle is oracle-rs, here are the docs, I'm wondering if the feature you are asking about is: https://docs.rs/oracle/0.5.3/oracle/struct.Connector.html#method.external_auth

If so, some code could probably be added to the connection builder to dynamically enable this on the rust side here, it would just require passing in a custom oracle connection into the r2d2-oracle connection pool:

• https://github.com/wseaton/connector-x/blob/main/connectorx/src/sources/oracle/mod.rs

    /// Initialise the connection manager with the data needed to create new connections using `oracle::Connector`.
   /// This allows setting additional connection data.
   ///
   /// If a connection can be established only with a username, password and connect string, use `new` instead.
   ///
   /// # Example
   /// ```
   /// # use r2d2_oracle::OracleConnectionManager;
   /// // connect system/manager as sysdba
   /// let mut connector = oracle::Connector::new("system", "manager", "");
   /// connector.privilege(oracle::Privilege::Sysdba);
   /// let manager = OracleConnectionManager::from_connector(connector);
   /// ```
   pub fn from_connector(connector: oracle::Connector) -> OracleConnectionManager {
       OracleConnectionManager { connector }
   }

[mdeakyne]

Thanks for the quick response!

I'll review oracle-rs, and try to get connect directly using r2d2. It's taken a bit to get things working in Python using SQLAlchemy and pandas, and Rust is new to me. I'll try to get some more specifics, though.

-Matt

[wseaton]

Maybe one of the more seasoned maintainers can weigh in, but for configuring this, would it make sense to inject via environment variable? Something like: CONNECTORX_ORCALE_SYSTEM_AUTH

[wseaton]

@mdeakyne if you can build/compile connectorx locally you could verify that the proposed solution above would work on my fork: https://github.com/sfu-db/connector-x/pull/203, I'm not sure how to test it myself :slightly_smiling_face:

[wangxiaoying]

@mdeakyne , the solution from @wseaton is released in the newest alpha version: 0.2.4-alpha.1. Hopefully it could help in terms of testing it.

[mdeakyne]

Good morning,

This is going to be long : but Oracle connections are historically problematic for me - and I wanted to run through all possibilities and do a fair comparison between connectorx and sqlalchemy.

Thank you for creating 0.2.4-alpha.1. I get the same results as before: [2022-01-05T17:07:52Z ERROR r2d2] OCI Error: ORA-12154: TNS:could not resolve the connect identifier specified

I ran into a lot of unrelated issues with tnsnames.ora, specifically that my successful connections previously used LDAP, and not anything defined in the file itself. Additionally, my local Windows environment has some connections defined somewhere that make some other things magically work. To avoid relying on LDAP, or the magic connections (either SAS config or SQLDeveloper config) - I created a docker image.

The code snippets below are from a Jupyter Notebook running in that docker image, and don't rely on TNSNAMES.ora or LDAP.

from sqlalchemy import create_engine
from dotenv import load_dotenv
from os import getenv as ge
import pandas as pd

load_dotenv()
user, pwd, host, service_name = ge('user'), ge('db_pwd'), ge('host'), ge('service_name')

con_string = f'oracle+cx_oracle://{user}:{pwd}@{host}:1521/?service_name={service_name}'
conn = create_engine(con_string)

table = ge('table')
query = f"""
SELECT *
FROM {table}
FETCH FIRST 5 ROWS ONLY
"""

df = pd.read_sql(query, con=conn)
df.head() #Works - prints first 5 rows.

Also, using the host and service_name in a DSN string created by the cx_Oracle library:

import cx_Oracle
dsn = cx_Oracle.makedsn(host, 1521, service_name=service_name)
con_string = f'oracle+cx_oracle://{user}}:{pwd}@{dsn}'
conn = create_engine(con_string)

df = pd.read_sql(query, con=conn)
df.head() #Works - prints first 5 rows

To be fair, SQLAlchemy with cx_oracle converts the first string type to the second.

f'oracle+cx_oracle://{user}:{pwd}@{host}:1521/?service_name={service_name}'

f'oracle+cx_oracle://{user}:{pwd}@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service_name}))'

con_str = f'{host}:1521/?service_name={service_name}'
dsn_str = f'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service_name}))'

Using those same connection strings with connectorx

import connectorx as cx
test1 = cx.read_sql(f'oracle://{user}:{pwd}@{dsn_str}', query=query) 
# Fails 

test2 = cx.read_sql(f'oracle://{user}:{pwd}@{con_str}', query=query)
# Fails

CORRECTION: (Edited after checking more closely)

Test 1 fails with [2022-01-12T15:15:42Z ERROR r2d2] OCI Error: ORA-12154: TNS:could not resolve the connect identifier specified

Test 2 fails with [2022-01-12T15:17:25Z ERROR r2d2] OCI Error: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

The second is ... progress! So, the new question is : How do I specify the service_name in the connection string?

-Matt

[mdeakyne]

Sorry for the quick follow up, but here is the working connection string!

con_str = f'{host}:1521/{service_name}'
test = cx.read_sql(f'oracle://{user}:{pwd}@{con_str}', query=query)
test # WORKS!

This works in the alpha version provided.
For the non-alpha version of connectorx, I get this error:

PanicException: not implemented: Connection: oracle://{user}:{pwd}@{host}:1521/{service_name} not supported!

[wangxiaoying]

Hi @mdeakyne , great that it works. The connection string for username and password authentication should be like: oracle://{user}:{pwd}@{host}:{port}/{service_name}. This update only applies to versions that >= 0.2.4-alpha.1, so the newest non-alpha version 0.2.3 does not support it.

But base on my understanding, if you want to enable system auth you need to not specify the username and password, and also change the hostname to localhost (refer to https://github.com/sfu-db/connector-x/pull/203/files#diff-fede1f15b21cffd41c3240f5b8e43717dfeff1f17b3ec48ee684596d5ef5caf7R68 ). @wseaton can you help to confirm it?

[wseaton]

@wangxiaoying host is dynamically set to localhost if nothing is set to we compare against that to enable system auth, it'd probably be better to add a flag var that we toggle and compare against instead of checking string equality but I wanted to disrupt the least amount of surrounding code as a quick fix.

Basically if you leave username, password, and host blank it should delegate to system auth. This should be verifable by setting the log level to debug.