ORA-01017: invalid username/password; logon denied from sql developer but sqlplus logins

Question

I've a strange behaviour of Sql Developer with OS Authentication, 4.1.3 32/64bit, gives ORA-01017 while sqlplus is logging in without any problem.

This is the example answer sqlplus gives me:

~ sqlplus  /@XXXX

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 27 08:50:10 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

after that running SELECT user FROM DUAL; return my domain user.

While this is what i get from sql developer configured on Databases -> Advanced.

as it is shown it returns Status : Failure -Test failed: ORA-01017: invalid username/password; logon denied

And when I try to connect from the Connection section I get a little more detailed error:

An error was encountered performing the requested operation:

ORA-01017: invalid username/password; logon denied
01017. 00000 -  "invalid username/password; logon denied"
*Cause:    
*Action:
Vendor code 1017

Thank you in advance for any idea or help

Answer 1

The problem was that I had too many instant clients and Oracle clients on my machine (one oracle client 'x86').

The instant clients were too old.

The only working instant client for me is the '12.1.0.2.0'.

I downloaded:

• instant client basic lite win64 12.1.0.2.0
• instant client jdbc win64 12.1.0.2.0
• instant client sqlplus win64 12.1.0.2.0

fixed the PATH variable with the path I extracted the instant client '12.1.0.2.0' and removed all others from it.

In the end I set up the environment variable TNS_ADMIN pointing to my sqlnet.ora and tnsnames.ora. I think this TNS_ADMIN is not necessary.

Answer 2

Oracle Sql developer and sqlplus work with OS authentication in absolutely different ways.

Sql developer always tries to authorize by substituting to the user name "\". SLQPLUS substitutes only the user name. When adding a domain to the user name. OSAUTH_PREFIX_DOMAIN = TRUE (windows registry). Sqlplus appends the domain name to the name.

Sql developer always tries to authorize by substituting to the user name "\" without domain name.

example 1

CREATE USER "OPS$ORACLE.ADMIN" IDENTIFIED EXTERNALLY 
PROFILE DEFAULT 
DEFAULT TABLESPACE tablespace_oracle_admin 
TEMPORARY TABLESPACE TEMP 
QUOTA UNLIMITED ON tablespace_oracle_admin 
ACCOUNT UNLOCK ;

C:\ORA\DB\product\11.2.0\dbhome_1\bin\sqlplus / SQL> Select user from dual;

USER
_______________________
OPS$ORACLE.ADMIN

To connect from sql developer with the database, it is necessary to put the "/" symbol instead of the user name without specifying a password.

Select user from dual;

USER
_______________________
OPS$ORACLE.ADMIN

example 2

CREATE USER "OPS$\ORACLE.ADMIN" IDENTIFIED EXTERNALLY 
PROFILE DEFAULT 
DEFAULT TABLESPACE tablespace_oracle_admin 
TEMPORARY TABLESPACE TEMP 
QUOTA UNLIMITED ON tablespace_oracle_admin 
ACCOUNT UNLOCK ; 

When connecting to using Sql developer, OS authentication work!!!!

Select user from dual;

USER
____________________
OPS$\ORACLE.ADMIN

Answer 3

Go to command prompt sql plus plus and change the password. Command: connect [username]/[password]

after which you will be prompted to enter new password enter new password and whooo!!! you are done.

Answer 4

Actually I also encountered similar problem, I can login database server, but received the error ORA-01017. I changed my password and it worked.

Answer 5

I was having this same error message after I installed oracle SQL developer and trying to create system connection.

it was due to I was entering a different than the password that I set while installing OracleXE.

I solved it by entering the right password.

If you forget the password you need to uninstall and reinstall OracleXE.