Hey Conner,

I have been looking around and asked on the oracle community forum but no luck on connecting to an instance with sqlcl wallet/mcs connections.

I can connect with sqlplus, and sql developer but cant find the setting in the sql developer/sqlcll dirs.

my sql developer is old, requests have been submitted for updates :)

sql /@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=super_cool_server)(Port=7856))(CONNECT_DATA=(service_name=super_fun)))"


SQLcl: Release 21.4 Production on Tue Sep 03 16:19:13 2024

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

USER =
URL = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=super_cool_server)(Port=7856))(CONNECT_DATA=(service_name=super_fun)))
Error Message = IO Error: IO Error sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target, connect lapse 99 ms., Authentication lapse 0 ms.
Username? (RETRYING) ('/@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=super_cool_server)(Port=7856))(CONNECT_DATA=(service_name=super_fun)))'?)

Also for sql developer you no longer have to use the Custom JDBC connection type.

Thanks
Dave

thanks for the fast response.
I did get a response from my question on the Oracle Forum and I didnt love the response for using MCS store, but one thing I like is a challenge.
I almost quit this job back in 2015 when my lead said "you ever worked on Oracle?"
Me: No
him: Well make Oracle and PKI work together.
I cried myself to sleep every night lol but with the help of bug fixes we figured it out.

Back to mission at hand.
Yes I did read that article and attempted some tweaks. None worked.

Ill provide some details when I organize them.

I had an admin give me all the privs on the sql developer dir so I can tweak more.
Let me ask this questions before I am messing with something that doesnt have all the dependent objects.
Does Sql Developer come bundled with everything required to use soft cert/wallet connections? I assume yes but want to confirm.

echo %java_home%
C:\Program Files\SQLDeveloper_21.4.2\jdk\jre

java -version
java version "1.8.0_401"
Java(TM) SE Runtime Environment (build 1.8.0_401-b32)
Java HotSpot(TM) 64-Bit Server VM (build 25.401-b32, mixed mode)

C:\sql
SQLcl: Release 21.4 Production on Thu Sep 05 15:51:34 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.

discovered the issue as I was writing this out. I will continue just incase others have the issue because its a change in how oracle handled the syntax.

this works in 21x sql developer the " " are the issue
set JAVA_TOOL_OPTIONS=-Doracle.net.wallet_location="(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\test\Wallet_stuff\sqlcl_tfs_wall)))" -Doracle.net.authentication_services="(TCPS)" -Doracle.net.ssl_server_dn_match=false

if you set above and attempt to connect it attempts to connect (I assume) there is a pause and then just returns the command line

C:\Users\t1dsoldier>set JAVA_TOOL_OPTIONS=-Doracle.net.wallet_location="(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\test\Wallet_stuff\sqlcl_tfs_wall)))" -Doracle.net.authentication_services="(TCPS)" -Doracle.net.ssl_server_dn_match=false

C:\Users\t1dsoldier>sql -verbose /@test_dev_wall_port

C:\Users\t1dsoldier>

I started fresh with sql developer 23. installed that with the java version "22.0.2" 2024-07-16
java version "22.0.2" 2024-07-16
Java(TM) SE Runtime Environment (build 22.0.2+9-70)
Java HotSpot(TM) 64-Bit Server VM (build 22.0.2+9-70, mixed mode, sharing)

--sql developer no configuration at all beside system env's
C:\INST\sqldeveloper_23\sqldeveloper\bin>echo %ORACLE_HOME%
%ORACLE_HOME%

C:\INST\sqldeveloper_23\sqldeveloper\bin>echo %TNS_ADMIN%
C:\Oracle\tns_admin

C:\INST\sqldeveloper_23\sqldeveloper\bin>echo %JAVA_HOME%
C:\Program Files\java\jdk-22\

--expected to fail, doesnt know where the wallet it
C:\INST\sqldeveloper_23\sqldeveloper\bin>sql.exe -L -verbose /@test_dev_wall_port
USER =
URL = jdbc:oracle:thin:@test_dev_wall_port
Error Message = IO Error: IO Error PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target, connect lapse 268 ms., Authentication lapse 0 ms.

set java_tool_options
set JAVA_TOOL_OPTIONS=-Djavax.net.ssl.trustStore=NONE -Djavax.net.ssl.trustStoreType=Windows-ROOT -Djavax.net.ssl.keyStore=NONE -Djavax.net.ssl.keyStoreType=Windows-MY -Doracle.net.authentication_services=(TCPS) -Doracle.net.ssl_server_dn_match=false


C:\INST\sqldeveloper_23\sqldeveloper\bin>sql.exe -L -verbose /@test_dev_wall_port
Picked up JAVA_TOOL_OPTIONS: -Djavax.net.ssl.trustStore=NONE -Djavax.net.ssl.trustStoreType=Windows-ROOT -Djavax.net.ssl.keyStore=NONE -Djavax.net.ssl.keyStoreType=Windows-MY -Doracle.net.authentication_services=(TCPS) -Doracle.net.ssl_server_dn_match=false

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

2024-10-01 13:58:52.902 INFO oracle.dbtools.raptor.scriptrunner.commands.NLSLANGListener runOnConnect NLS_LANG is not set
SQL> show connection
COMMAND_PROPERTIES:
type: ORACLE
user:
url: test_dev_wall_port
CONNECTION:
t1dsoldier@jdbc:oracle:thin:@test_dev_wall_port
CONNECTION_IDENTIFIER:
test_dev_wall_port
CONNECTION_DB_VERSION:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
NOLOG:
false
PRELIMAUTH:
false
SQL> show java
Java Detail
-----------
java.home= C:\INST\sqldeveloper_23\jdk\jre
java.vendor= Oracle Corporation
java.vendor.url= https://openjdk.java.net/
java.version= 11.0.21.0.2
--------------------------------------------------------------------------------
os.arch= amd64
os.name= Windows 11
os.version= 10.0
path.separator= ;
file.separator= \
line.separator=

user.dir= C:\INST\sqldeveloper_23\sqldeveloper\bin
user.home= C:\Users\t1dsoldier
user.name= t1dsoldier
user.language= en
user.region= null
file.encoding= Cp1252
Used memory: 49.6MB
Max available memory: 2,048.0MB
--------------------------------------------------------------------------------
SQL_HOME=null
Classpath

--------------------------------------------------------------------------------
null
SQL> show jdbc
-- Database Info --
Database Product Name: Oracle
Database Product Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
Database Major Version: 19
Database Minor Version: 0
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 21.8.0.0.0
Driver Major Version: 21
Driver Minor Version: 8
Driver URL: jdbc:oracle:thin:@test_dev_wall_port
Driver Location:
resource: oracle/jdbc/OracleDriver.class
jar: C:/INST/sqldeveloper_23/jdbc/lib/ojdbc11.jar
JarSize: 5181682
JarDate: Mon Dec 11 20:21:32 EST 2023
resourceSize: 3060
resourceDate: Mon Sep 12 13:10:48 EDT 2022


===========================
sqlcl 24.x has the same behavior, if double quotes are used nothing returns
sqlcl 24.2.0.180.1721

C:\Users\>sql -verbose /@test_dev_wall_port
Picked up JAVA_TOOL_OPTIONS: -Doracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\test\Wallet_stuff\sqlcl_tfs_wall))) -Doracle.net.authentication_services=(TCPS) -Doracle.net.ssl_server_dn_match=false

SQL> show connection
COMMAND_PROPERTIES:
type: ORACLE
url: test_dev_wall_port
CONNECTION:
WALL_TEST@jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = test_server)(PORT = 444)) ) (CONNECT_DATA = (SERVICE_NAME =testl) ) )
CONNECTION_IDENTIFIER:
test_dev_wall_port
CONNECTION_DB_VERSION:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
NOLOG:
false
PRELIMAUTH:
false

Thanks for talking through this with me. I had just opened an SR because I was stumped.