Thin mode ORA-01017: invalid username/password with 12.1.0.2

[Arunosaur]

1. What versions are you using?

platform.platform: Darwin-20.6.0-x86_64-i386-64bit

sys.maxsize > 2**32: True

platform.python_version: 3.7.13

oracledb.version: 1.0.1

Database Version: 12.1.0.2.0

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

#test-2.py

import oracledb
import os

un = os.environ.get('USER_NAME')
pw = os.environ.get('PASSWORD')
cs = os.environ.get('CONNECT_STRING')
oracledb.init_oracle_client()
with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
   with connection.cursor() as cursor:
      sql = """select version
               from   v$instance"""
      for r in cursor.execute(sql):
         print(r)

python test-2.py
('12.1.0.2.0',)

#test-2.py

import oracledb
import os

un = os.environ.get('USER_NAME')
pw = os.environ.get('PASSWORD')
cs = os.environ.get('CONNECT_STRING')
#oracledb.init_oracle_client()
with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
   with connection.cursor() as cursor:
      sql = """select version
               from   v$instance"""
      for r in cursor.execute(sql):
         print(r)

python test-2.py
Traceback (most recent call last):
File "test-2.py", line 10, in
with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
File "/Users/asrajag/miniconda3/envs/oracle/lib/python3.7/site-packages/oracledb/connection.py", line 1000, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
File "/Users/asrajag/miniconda3/envs/oracle/lib/python3.7/site-packages/oracledb/connection.py", line 128, in init
impl.connect(params_impl)
File "src/oracledb/impl/thin/connection.pyx", line 345, in oracledb.thin_impl.ThinConnImpl.connect
File "src/oracledb/impl/thin/connection.pyx", line 163, in oracledb.thin_impl.ThinConnImpl._connect_with_params
File "src/oracledb/impl/thin/connection.pyx", line 129, in oracledb.thin_impl.ThinConnImpl._connect_with_description
File "src/oracledb/impl/thin/connection.pyx", line 250, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "src/oracledb/impl/thin/protocol.pyx", line 205, in oracledb.thin_impl.Protocol._connect_phase_two
File "src/oracledb/impl/thin/protocol.pyx", line 296, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-01017: invalid username/password; logon denied

4. Does your application call init_oracle_client()?

Thick mode works, thin mode does not.
5. Include a runnable Python script that shows the problem.

#test-2.py

import oracledb
import os

un = os.environ.get('USER_NAME')
pw = os.environ.get('PASSWORD')
cs = os.environ.get('CONNECT_STRING')
#oracledb.init_oracle_client()
with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
   with connection.cursor() as cursor:
      sql = """select version
               from   v$instance"""
      for r in cursor.execute(sql):
         print(r)

[anthony-tuininga]

I'll see if I can get a 12.1.0.2 database to test with. It may, however, not be the version directly but some configuration that the thin driver isn't supporting. Can you provide additional information on your configuration (contents of sqlnet.ora, connect string, etc.)?

[anthony-tuininga]

Ok. I set up a brand new 12.1.0.2 database and I am able to connect without any difficulties. Can you take a look at the contents of this column for your user?

select password_versions from dba_users where username = :user

[Arunosaur]

ASRAJAG@EBS_EBSP⏳ >select password_versions from dba_users where username = user;

PASSWORD_VERSIONS

---

10G 11G 12C

[Arunosaur]

sqlnet.ora
##WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/asrajag/Library/Oracle/Network/Admin")))
SSL_SERVER_DN_MATCH=yes

---

echo $CONNECT_STRING
EBS_EBSP

--
tnsnames.ora

EBS_EBSP=
(DESCRIPTION = (FAILOVER=ON) (LOAD_BALANCE=OFF)
(ADDRESS_LIST= (LOAD_BALANCE=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = lpdstora-scan.mclaneco.com)(PORT = 1521))
)
(ADDRESS_LIST= (LOAD_BALANCE=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = lpdstexa-scan.mclaneco.com)(PORT = 1521))
)
(ADDRESS_LIST= (LOAD_BALANCE=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = lddscora-scan.mclaneco.com)(PORT = 1521))
)
(ADDRESS_LIST= (LOAD_BALANCE=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = lddscexa-scan.mclaneco.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EBS_EBSP)
)
)

[cjbj]

@Arunosaur is there anything 'special' about the username or password? Do connections work if you use a different schema on the same DB? Is your password case sensitive? Are you using a proxy user e.g user="myproxyuser[mysessionuser]" (see doc)?

[Arunosaur]

I am not using a proxy user. My user is straight forward. And password is case sensitive. I have a special character and a number, upper and lower case letter. That is the only user I am allowed to login as.

[cjbj]

This will be tricky (but important) for us solve, so we'll have to ask more questions: What OS is the database running on?

[Arunosaur]

We are on exadata, recently upgraded to X9.

ASRAJAG@EBS_EBSP⏳ >SELECT platform_id, platform_name FROM v$database
2* ;

PLATFORM_ID PLATFORM_NAME

---

        13 Linux x86 64-bit

ASRAJAG@EBS_EBSP⏳ >SELECT dbms_utility.port_string FROM DUAL;

PORT_STRING

---

x86_64/Linux 2.4.xx

[E179522]

I'm seeing this exact same issue. Thick client connects, thin does not.

Oracle version 12.1.0.2.0

import oracledb
#oracledb.init_oracle_client()
ora_connection = oracledb.connect("connection string")

oracledb.exceptions.DatabaseError: ORA-01017: invalid username/password; logon denied

[vstavskyi]

@Arunosaur @E179522
Try change password to update hashes in sys.user$ table.

[E179522]

@Arunosaur @E179522 Try change password to update hashes in sys.user$ table.

Unfortunately I have zero control over the database. Its managed by another group in the company and those folks are completely unwilling to help troubleshoot.

[Arunosaur]

@Arunosaur @E179522
Try change password to update hashes in sys.user$ table.

It is not something quick from my side as well, as I have to maintain the passwords to be the same across multiple databases (somewhere between 30 and 50) for some db links to work. I will let you know as soon as I can. Thanks

[cjbj]

Try change password to update hashes in sys.user$ table.

@E179522 good idea. Can you confirm this resolved the error for you?

[cjbj]

@Arunosaur: here's something to look forward to: Gradual Password rollover.

[Arunosaur]

@Arunosaur: here's something to look forward to: Gradual Password rollover.

But, this is Oracle 12.

[cjbj]

@Arunosaur that's why I said "look forward to"

[Arunosaur]

@Arunosaur that's why I said "look forward to"

Got it. Thank you. Now that password rollover has been back ported, I need to check on our non ebs databases.

I wish e-business suite could be moved to 19c sooner.

[jdennis]

I seem to running into the same problem with invalid username and password. The first reasonable assumption is the credentials or connection string are wrong, but that's not the case. To prove this and illustrate the problem is identical on more than one platform I wrote a simple test that either loads the older cx_Oracle or the new oracledb library and ran it both on Mac and Windows. Here is the simple test I used (I have obscured the credentials and connection string for security reasons).

The username and password are mixed case alphanumeric in the ASCII range, no special characters.

import oracledb as db
#import cx_Oracle as db

username = "xxx"
password = "yyy"
service = "fqdn:port/service_name"

db.connect(user=username, password=password, dsn=service)

Simply changing which library is imported by editing the comment on the import shows cx_Oracle works as expected but oracledb fails with:

oracledb.exceptions.DatabaseError: ORA-01017: invalid username/password; logon denied

FWIW the behavior the same when used with SQLAlchemy, albeit with some different error reporting.

The Oracle database I'm connecting to is version 19c.

Here is version information for the Mac and Windows systems I tested on (caveat: I could not run with cx_Oracle on the Mac because it's an M1 which does not have the Oracle Client Libraries available for the M1):
Mac Monterey: python 3.8.13, oracledb 1.0.1, cx_Oracle 8.3.0
Windows 10: python 3.10.5, oracledb 1.0.1, cx_Oracle 8.3.0

[jdennis]

Oh, forgot to mention, this is with the thin client, or at least it should be since I did not explicitly enable thick client mode.

[cjbj]

@jdennis can you try changing the password to update the stored hashes as mentioned in #26 (comment) ?

[jdennis]

@cjbj I'm sorry, I'm not able to change the password because of the way the database is managed internally.

[jdennis]

@cjbj Out of curiosity, do you suspect the problem is you are hashing the password locally but the hash algorithm on the target database is different and hence the hashes do not match? Also, I'm not sure how cx_Python and the Oracle client libraries interact but there must be some difference between that and the new implementation in oracledb because the former works.

[anthony-tuininga]

cx_Oracle uses the Oracle Client libraries which have support for the older password hashes that were in use prior to 11g. Can you perform the same query noted above?

select password_versions from dba_users where username = :user

Do you have a server side sqlnet.ora file?

[vstavskyi]

@jdennis
If password was set in database 10g or 11g, hashes in sys.user$ have remained unchaged after upgrade database to 12c.

[notqualifiedforthis]

Similar issue here. I can connect to numerous Oracle databases using DBVisualizer or cx_oracle on my Windows machine but I cannot get oracledb to connect on my M1 MacBook Pro.

Oracle 19c
ORA-01017: invalid username/password; login denied.

I’ve reset my password and still connecting through other means but not oracledb

[anthony-tuininga]

You're saying that you are getting this issue connecting to a 19c database? Do you have a sqlnet.ora on the server?

[notqualifiedforthis]

@anthony-tuininga I am receiving ORA-01017 on my MacBook Pro M1 when using oracledb. Looks like Oracle 19.15.0.0. Upgraded from 12 a few months back. Password versions shows 10G 11G and 12C for my username.

I know it’s not an invalid username or password because…

• I connect from DBVis on Windows & MacBook M1
• I connect from cx_oracle on Windows

I’m not one of our DBAs so I’m not positive we have an sqlnet.ora but I believe we do. I can see a copy of one in a backup directory we’re using right now. I do not have permission to read it. I can chat with DBAs tomorrow. Am I looking for something specific?

[Arunosaur]

@cjbj I changed the password today and still the same, it works with thick client but not thin client.

[Arunosaur]

@anthony-tuininga yes we have a sqlnet.ora on the server side.
sec_case_sensitive_logon=false
logon_version_server=8

[antoniolucasnobar]

I am having this exact same problem in a 19c database (we came from 12.1 a few months ago. But they changed every password after the change).

I can connect trough dbeaver and the same progam works with cx_oracle 7.3 (I was going to upgrade to 8.3 when I found python-oracledb).

I am using Thin mode. Never tried Thick mode.

I ran the select: select password_versions from dba_users where username = :user
10G 11G 12C

I asked my colleagues about SQLNET.ORA and received a file with this:
AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
names.directory_path = (TNSNAMES)
names.default_domain = world
name.default_zone = world
sqlnet.authentication_services=(none)
DISABLE_OBB=ON

@anthony-tuininga Below I ran the program with PYO_DEBUG_PACKETS enabled. I changed some sensitive information (you will see a bunch aaaaa in the packets).
python-oracledb.log

Hope it helps. If you need anything else, just ask. But I am not dba and anything I need from them is hard to get, unfortunately.

[anthony-tuininga]

@antoniolucasnobar, the sqlnet.ora you supplied is the one for the client but I need to see the one on the server. Can you find out what that one contains?

Looking at the packet output it looks like the server is sending the regular 12c verifier challenge -- so this seems to be something different. I'll see if I can get any more information on how to debug this situation.

You should be able to do this:

import oracledb

oracledb.init_oracle_client()

conn = oracledb.connect(user="my_user", password="my_password", dsn="my_host/my_service_name")

The call to oracledb.init_oracle_client() will cause the thick client to be used (and is functionally the same as cx_Oracle 8.3 with a few additions). Commenting out that line will cause the thin driver to be used. That way you can flip back and forth between the modes without having to change any other part of your code. Hopefully we can get to the bottom of this issue!

[anthony-tuininga]

A bit of additional information. In the packet that contains the challenge is what is referred to as a "verifier type". The value in your packet @antoniolucasnobar, is not the one we are expecting. I am asking internally to see how that can be configured so I can address that issue. It would be useful if @Arunosaur and @notqualifiedforthis can also supply the output from setting the environment variable PYO_DEBUG_PACKETS to any value before running your script. I don't need the first few packets. I only need the last few packets (the longer ones before the error takes place).

[anthony-tuininga]

I've confirmed internally that the verifier type returned by the server is the 10g verifier type -- which is not supported by the thin driver at this time. Considering the age of the verifier type and the fact that it is relatively insecure (compared to more recent verifier types) I don't know if there will be an effort to implement the 10g verifier type. You can check the value of the parameter in SQL*Plus using the following command:

show parameter sec_case_sensitive_logon

If you all can verify using one of these techniques and discuss with your DBAs if/when this value might be changed, let me know those results and I can discuss internally whether we should support the 10g verifier or not.

[notqualifiedforthis]

@anthony-tuininga I would have to type my packet manually on my phone or personal machine. Is there any specific value I can provide? I asked DBAs for the sec_case_sensitive_logon value and they said it's set to FALSE

[antoniolucasnobar]

@anthony-tuininga about the sqlnet.ora, I will have to wait the dba's response. I do not know when they will do that. Sorry.

I did not want to use the thick connection, because it doubles the docker image size and the time to build is way bigger. But I will try it tomorrow (it is almost noon in here).

About the 10g verifier - you said it is relatively insecure. Would you have links so I can show them? If I just say that, I feel they will not believe me.

To the thin connection to work, we just need to change the sec_case_sensitive_logon param to true? Right now it is false, like you can see below (from a docker image, I do not have sqlplus installed in my machine):

sigep-scripts-sqlplus-sqlplus-1 | Connected to:
sigep-scripts-sqlplus-sqlplus-1 | Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
sigep-scripts-sqlplus-sqlplus-1 |
sigep-scripts-sqlplus-sqlplus-1 | Hello from sqlplus inside docker
sigep-scripts-sqlplus-sqlplus-1 | SQL>
sigep-scripts-sqlplus-sqlplus-1 | NAME TYPE
sigep-scripts-sqlplus-sqlplus-1 | ------------------------------------ ---------------------------------
sigep-scripts-sqlplus-sqlplus-1 | VALUE
sigep-scripts-sqlplus-sqlplus-1 | ------------------------------
sigep-scripts-sqlplus-sqlplus-1 | sec_case_sensitive_logon boolean
sigep-scripts-sqlplus-sqlplus-1 | FALSE
sigep-scripts-sqlplus-sqlplus-1 | SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

[anthony-tuininga]

@notqualifiedforthis, Interesting! The part that I need looks like this (in a packet received from the server, just before the server rejects the connection):

0096 : 01 0D 0D 41 55 54 48 5F |...AUTH_|
0104 : 56 46 52 5F 44 41 54 41 |VFR_DATA|
0112 : 01 20 20 42 41 35 30 35 |...BA505|
0120 : 35 35 39 37 36 36 34 35 |55976645|
0128 : 46 43 30 32 44 42 33 37 |FC02DB37|
0136 : 38 33 30 39 45 33 45 32 |8309E3E2|
0144 : 42 45 43 02 48 15 01 14 |BEC.H.

I only need to know what the part is just before the next key. In this example the value is 02 48 15 (on the last line). The value found in the log sent by @antoniolucasnobar was 02 09 39.

@antoniolucasnobar, the value of sec_case_sensitive_logon should indeed be TRUE. Setting it to FALSE along with also setting sqlnet.allowed_logon_server=10 in the sqlnet.ora on the server disables the newer password hash algorithms since both of those are case sensitive.

As for the relative insecurity: here is a presentation that discusses the different hash functions and very distinctly recommends the use of the 12c verifier: https://www.oradba.ch/2020/12/security-best-practice-oracle-passwords-but-secure/. I can probably provide an official reference if that is going to help?

[notqualifiedforthis]

@anthony-tuininga mine appears to be much shorter so I'm guessing these are the wrong lines of code

0112 : 00 02 09 39 01 14 14 41 |...9...A|

[anthony-tuininga]

Yes, that indicates you are using the 10g verifier. The 02 09 39 is the part that tells me that. See if you can convince your DBA to stop using the 10g verifier. :-)

[notqualifiedforthis]

I'll send the link you provided earlier to my DBAs & Tech Lead and ask them to start conversations. Any official references you can provide would also help. Thanks for your help!

[anthony-tuininga]

@notqualifiedforthis, @antoniolucasnobar, I edited the comment above to indicate that if you set sec_case_sensitive_logon to the value FALSE and also set sqlnet.allowed_logon_version_server=10 in the server's sqlnet.ora, that this is what disables the newer password verifiers. My apologies for the confusion!

[notqualifiedforthis]

@anthony-tuininga would case sensitive logon=TRUE work with allowed login server version=10? It's just the combination of FALSE/10 causing problems?

[anthony-tuininga]

I was able to replicate this issue now by simply setting sec_case_sensitive_logon to the value FALSE. The value of sqlnet.allowed_logon_version_server does not seem to be important. I have been informed that the setting sec_case_sensitive_logon is deprecated and has been removed in 21c.

[anthony-tuininga]

@Arunosaur, @notqualifiedforthis, @antoniolucasnobar, I have pushed a patch that generates this error now:

DPY-3015: unsupported password verifier type: 0x939

It is unlikely that support will be added for the 10g verifier considering its age and its relative insecurity. So that leaves you with the following choice:

• use the thick driver (call init_oracle_client() before creating your first connection)
• remove the parameter sec_case_sensitive_logon from your init.ora (or spfile) or set its value to TRUE

Thanks for all of your help in getting to this point!

[notqualifiedforthis]

@anthony-tuininga we flipped the case sensitive logon parameter to TRUE in a test environment and I can confirm python-oracledb is connecting over the thin client. Now the fun piece of understanding impact and moving through our environments.

[rossov]

@anthony-tuininga I have a similar issue, there is a slight difference though

db: 19c
sec_case_sensitive_logon: TRUE

Can connect with the thick driver, but not with the thin version if it's Oracle LDAP user account.
For local db accounts, both thin and thick drivers work.

If you prefer I can open another issue rather than asking here.

Thanks,
Slava

P.S. This is strikingly similar to ODP.Net issue with Managed/Unmanaged drivers that I wasn't able to resolve for years.

[anthony-tuininga]

Yes, open a new issue (enhancement). If you're referring to external authentication using LDAP then that is known not to be supported. Provide details on your configuration (relevant items in sqlnet.ora, tnsnames.ora, etc.)

[rossov]

Thank you, will do.

[anthony-tuininga]

The patch I mentioned was just released in python-oracledb 1.0.3. If you need the 10g password verifier support, please open a new enhancement request -- but recognize that it will be unlikely to be implemented considering the age and relative insecurity of the verifier.