Skip to Main Content
Forums

SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sqlcl mkstore and tns question

Robert LockardSep 29 2016 — edited Sep 30 2016

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>

Comments

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

Post Details

Added on Sep 29 2016
1 comment
1,204 views