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 位用户发现它有用