Saturday, 27 February 2010

Using Oracle Client with SQLDeveloper

SQLDeveloper by default has limited connection configuration possibilities (thin jdbc driver) so 'edge cases' may need the Oracle (instant) Client install and full *.ora configuration

- sqldeveloper
- Tools preferences -> database -> advanced -> use oci/thick driver
- oracle client
- connection type TNS->connect identifier
- click kerberos to allow null username/password (or OS authentication but I think that has more consequences)

(and effectively kick the config back to the oracle thick client *.ora files and 'sqlplus' C code (where kerberos may not be configured))


Turloch O'Tierney said...

Oracle sqldeveloper uses Oracle thin driver which for edge cases needs to be explicitly configured through sqldeveloper code, most networking features are available through the (thick/oci/oracle client/instant client) code configured through the *.ora files.

This blog was in response to an external forum request for wallet support which I have not tried (recently anyway) but the thick root has a fair chance of success - I have verified for example that it works with radius authentication.

Turloch O'Tierney said...

This comment regards Enterprise Authentication using thin - but if you are trying to use thick because of ORA-01017 you might find this.

use of enterprise user (Oracle Internet Directory) - initially gives ORA-01017 (invalid username/password)

solution/workaround for thin:
On thin you may be hitting:
Bug 8254139 - ora-01017 when connecting by jdbc thin 11g to eus secured database.
Which is marked as fixed in 11.2 thin (which SQLDeveloper has not moved to yet).

workaround from Note 793673.1 - use a different option
java -Doracle.jdbc.thinLogonCapability=o3
From the note: 'There is no loss of security when following this workaround'

which for SQLDeveloper would be add
AddVMOption -Doracle.jdbc.thinLogonCapability=o3



Thanks to the customer for confirming OID authenication on SQLDeveloper (thin)

I do not know why OCI/thick did not work - for another day...

forum thread:
Thread: ORA-01017 (invalid username/password) using globally authenticated account

Turloch O'Tierney said...

Wallet works against code in development (with no code changes - so may well work with released code) - I used OCI 'thick' drier and did need to click on OS_AUTHENTICATION so it would not ask for username/password. The setup was using server software on the client - I have not tried with instant client (cannot confirm if it includes wallet software). Server was (loop back)(windows xp) and also (a remote server) (winodows xp).