2

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.

Sql Developer ORA-01017

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

6
  • try specifying a username and password in sqldeveloper, which isn't exactly sqlplus.
    – Raj
    Jul 27, 2016 at 11:51
  • 1
    What do you mean by specify username and password? do you mean to put them in connection properties? If so, the db user are working, domain users are not. Sorry I've to specify better that I'd like to use OS Authentication also with sqldeveloper and not only in sqlplus.
    – Cavva79
    Jul 27, 2016 at 12:04
  • If you read the help, there is a section 'Connections with Operating System (OS) Authentication' which might be helpful. Did you follow the instructions? if not, try them.
    – Raj
    Jul 27, 2016 at 13:13
  • There is a small help about OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT. Actually I don't know how to use them.
    – Cavva79
    Jul 27, 2016 at 13:33
  • you test with SQLPlus - is SQL Developer on the same machine as the SQLPlus you used for the test? Jul 27, 2016 at 15:24

5 Answers 5

3

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.

1
  • You had old connection files. Even if you entered the right user name & password, your connection details were probably point to the wrong server, service name, etc...
    – Sun
    Mar 14, 2019 at 18:09
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
1

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.

1

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.