1 Reply Latest reply on Sep 30, 2016 6:00 AM by Gaz in Oz

    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.

       

       

      [oracle@vbgeneric]$ 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

      RLOCKARD@hanirpora1:1521/DIRPHN.TEST>

       

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

       

      [oracle@vbgeneric]$ 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

       

      [oracle@vbgeneric ~]$ sql rlockard@dirphn

      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

      RLOCKARD@dirphn>

       

      I can connect to sqlplus using my keystore and tns

      [oracle@vbgeneric ~]$ 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

      RLOCKARD@dirphn>

        • 1. Re: sqlcl mkstore and tns question
          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:mydb

          Sep 30, 2016 2:25:04 PM java.util.prefs.WindowsPreferences <init>

          SQLcl: Release 4.2.0 Production on Fri Sep 30 14:25:04 2016

           

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

           

                  Dont forget to attend Oracle OpenWorld September 18 to 22, 2016

           

          Connected to:

          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

          With the Partitioning, OLAP and Data Mining options

           

           

          SQL>

          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 - Production

          With the Partitioning, OLAP and Data Mining options

           

          SQL>

          Success!

           

          Cheers,

           

          Gaz.

          1 person found this helpful