ORA-01017: invalid username/password; logon denied on Windows Server 2016

[bergen288]

1. What versions are you using?

platform.platform: Windows-10-10.0.14393-SP0 sys.maxsize > 2**32: True platform.python_version: 3.11.3 oracledb.version: 1.3.1

I found the same issue here: https://github.com/oracle/python-oracledb/issues/26. It should be addressed by oracledb v1.0.3. Why do I get the same error with oracledb v1.3.1?

2. Is it an error or a hang or a crash? Error.
3. What error(s) or behavior you are seeing?

See attached file. oracledb_logon_denied.txt

sqlplus can logon successfully with the same username/password. sqlplus FORP_C016_SAS_QUERY/xxxxxxxx@CCFRDEN_PROD

4. Does your application call init_oracle_client()? No.

   import oracledb
   import pandas as pd
   import os
   from sqlalchemy import create_engine
   
   class OracleEngine:
       def __init__(self, config) -> None:
           self.configuration = config
       def __enter__(self) -> "engine":
           tns_dir = os.environ.get("TNS_ADMIN")
           print('tns dir is:', tns_dir)
           #thick_mode = None
           thick_mode = {"lib_dir": r"E:\Software\Oracle\instantclient_21_10"}
           try:
               engine_str = "oracle+oracledb://{user}:{password}@{tns}".format(**self.configuration)
               self._engine = create_engine(engine_str, thick_mode=thick_mode)
               print("Oracle Engine is establishing:", self._engine)
               return self._engine
           except oracledb.InterfaceError as err:
               raise Exception(err) from err
           except oracledb.ProgrammingError as err:
               raise Exception(err) from err
       def __exit__(self, exc_type, exc_value, exc_trace) -> None:
           self._engine.connect().close()
           self._engine.dispose()
           if exc_type is oracledb.ProgrammingError:
               raise Exception(exc_value)
           if exc_type:
               raise exc_type(exc_value)
   
   ora_config = {
               'tns':          'CCFRDEN_PROD',
               'user':         'FORP_C016_SAS_QUERY',
               'password':     'xxxxxxxx'
               }
   ora_sql = """
           SELECT owner, table_name FROM all_tables
       """
   with OracleEngine(ora_config) as conn:
       df = pd.read_sql(ora_sql, conn)
       print(df)

   TNS 'CCFRDEN_PROD' is defined in E:\Software\Oracle\instantclient_21_10\network\admin\tnsnames.ora. I also tried thick_mode = None with the same error.

[anthony-tuininga]

Can you try performing the same connection without SQLAlchemy? In other words:

import oracledb

# oracledb.init_oracle_client(lib_dir=r"E:\Software\Oracle\instantclient_21_10")

conn = oracledb.connect(user="FORP_C016_SAS_QUERY", password="XXXXXXX", dsn="ccfrden_prod")

That will eliminate any potential parsing issues with SQLAlchemy. Based on your reference to the older issue are you saying that you are trying to connect to a 12.1 database? Can you be very precise on the database version? Also, you are claiming that it fails in thick mode as well -- which was not the case with the older issue!

[bergen288]

I tried conn = oracledb.connect(user="FORP_C016_SAS_QUERY", password="XXXXXXX", dsn="ccfrden_prod"), it worked. I then retried SQLAlchemy.create_engine and it also worked. Not sure what's wrong with my previous tries.

Thanks anyway.