sqlcl mkstore and tns question

Robert Lockard

Edited to add another use case:

Simple question, I can connect with sqlcl using a keystore using ezconnect. I can not connect with sqlcl using the keystore and tns, I can connect with sqlplus using the keystore. Any ideas why I get an ORA-01017 when I attempt to connect with my credentials from my keystore using TNS?

I can connect with credentials from my keystore when I use an ezconnect connect string.

[[email protected]]$ sql /@hanirpora1:1521/DIRPHN.TEST

SQLcl: Release 4.2.0.16.153.2014 RC on Thu Sep 29 15:27:05 2016

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

Last Successful login time: Thu Sep 29 2016 15:27:09 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[email protected]:1521/DIRPHN.TEST>

I get an ORA-01017 username/password error when I attempt to connect with credentials from my keystore using tns.

[[email protected]]$ sql /@dirphn

SQLcl: Release 4.2.0.16.153.2014 RC on Thu Sep 29 15:03:32 2016

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

  USER          =
  URL           = jdbc:oracle:oci8:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP )(HOST = hanirpora1)(PORT = 1521))    (CONNECT_DATA = (SERVER = DEDICATED)      (SERVICE_NAME = DIRPHN.TEST)    )  )
  Error Message = ORA-01017: invalid username/password; logon denied
  USER          =
  URL           = jdbc:oracle:thin:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP )(HOST = hanirpora1)(PORT = 1521))    (CONNECT_DATA =  (SERVER = DEDICATED)      (SERVICE_NAME = DIRPHN.TEST)    )  )
  Error Message = ORA-01017: invalid username/password; logon denied
Username? (RETRYING) ('/@dirphn'?)
Warning: You are no longer connected to ORACLE.

I can connect to sqlcl when I use my credentials and tns

[[email protected] ~]$ sql [email protected]

SQLcl: Release 4.2.0.16.153.2014 RC on Thu Sep 29 15:29:07 2016

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

Password? (**********?) ***********
Last Successful login time: Thu Sep 29 2016 15:29:14 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[email protected]>

I can connect to sqlplus using my keystore and tns

[[email protected] ~]$ sqlplus /@dirphn

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 29 15:53:36 2016

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

Last Successful login time: Thu Sep 29 2016 15:29:14 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[email protected]>

Gaz in Oz

Hi Robert,

I was expecting to be able to use the "-oci" option with sqlcl to make it work, but alas, not.

How ever, a custom "url" of /@jdbc:oracle:oci8:@tns_alias does:

sql /@jdbc:oracle/oci8:mydbSep 30, 2016 2:25:04 PM java.util.prefs.WindowsPreferences <init>SQLcl: Release 4.2.0 Production on Fri Sep 30 14:25:04 2016Copyright (c) 1982, 2016, Oracle.  All rights reserved.        Dont forget to attend Oracle OpenWorld September 18 to 22, 2016Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL>

A bit of a cludge, it does allow for the use of tnsnames.ora though.

...and to answer your question:

Any ideas why I get an ORA-01017 when I attempt to connect with my credentials from my keystore using TNS?

I believe the substitution of the tns alias happens before the wallet is read, and there is no entry for the connect string that sqlcl puts together. It may even stop the lookup. sqlnet trace may shed more light on what exactly is or is not going on here.

By using the custom URL syntax, no substitution of the tns alias happens and so the wallet entry is found and login is succesful.

With -verbose, the connections are as follows:

sql -verbose /@mydb...INFO: Attempting to connect using URL= "jdbc:oracle:oci8:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.4)(PORT = 1522))(CONNECT_DATA =SERVER = DEDICATED) (SERVICE_NAME = mydb))  )"

ORA-01017

sql -verbose /@jdbc:oracle:oci8:@mydb...INFO: Attempting to connect using URL= "jdbc:oracle:oci8:@imydb"Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL>

Success!

Cheers,

Gaz.