SQL Developer OS authentication

Question

I have Oracle SQL Developer version 4.1.19 (64 bit) with JDK8 included.

This is working for Oracle connections which require a {username, password} combination - no problem there.

My issue is that I cannot connect with an OS Authentication configuration.

Please note that I can connect from the command line with OS authentication by using sqlplus /@MY_TNS_NAME and this works fine.

I have the SQL Developer application configured to use the OCI/Thick driver and also to use the x64 12.1.0.2.0 basic instant client. This seems to be configured ok in that if I check Help->About-Properties then sqldeveloper.oci.available has the value "true".

However, each time that I test the connection I receive the failure message "ORA-01017: invalid username/password; logon denied"

My OS is Windows 7 Enterprise x64 SP1

I have obviously checked the option "OS Authentication" :).

Is there any other configuration that I need to change/check in order to get SQL Developer to allow me to connect via OS Authentication? (Changing the authentication type is not an option for me).

Thanks in advance.

Answer 1

Had the same issue. The cause is that SQL Developer's lightweight JDBC does not support extended authentication. SQL*Plus, to the contrary, is compiled against native driver, that's why it has no problem.

Solution: in SQL Developer, go

Tools -> Preferences -> Database -> Advanced Parameters

If you have Use Oracle Client already checked, just check Use OCI/Thick driver. That's all. If not, check Use Oracle Client first, and specify driver path. Idea was taken from here

Speaking about clients, Oracle allows to have a zoo of versions. If you have problems selecting working client, the cleanest option would be to use path where your SQL*Plus resides. Just strip \bin from its end, and make sure its CPU architecture matches SQL Developer.

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