If you’re using a password technology that integrates with desktop applications, then you probably want to also secure your Oracle Database connections.
Many of these work with command line interfaces for launching said applications, then they can inject their magic and handle the user interaction when it comes to supplying the password.
So we frequently see requests like this –
We would like the ability to launch SQL Developer with command line parameters such as a userid/password and connect string similar to what can be done today with SQLcl…
Customer #12345
Well, now you can do just that in version 20.2
One of our developers in their spare time put together an open source utility that allows you submit connection requests from outside of SQL Developer.
You can read all about Brian‘s solution here.
But, I’m going to give you the boiled down, TL;DR version, right here and right now.
Step-by-Step Instructions
Configure Check for Updates
We’re going to pull down an extension that allows SQL Developer to both listen for and make connection requests.
In the main menu of SQL Developer, open, Help – Check for Updates.
You’re going to to ADD a new Update Center.
For the location, enter this URL
https://raw.githubusercontent.com/bjeffrie/sqldev-update-center/master/update-center.xml
Make sure your new entry is checked before you hit next. If you ONLY want to see our open source extensions, make sure it’s the ONLY thing checked.
Click Next and confirm the install by hitting ‘Finish
When it’s done, it’ll ask to Restart SQLDev, say ‘Yes.’
Configuring SQL Developer
When SQL Developer has come up, we’re going to go into the preferences.
You’re going to at a minimum enable the first and third option, and confirm the port you want SQL Developer to listen for connection requests on.
By default, the connections coming in will be transient…that is, if you close and re-open SQL Developer, you won’t see them there in your Connection Panel.
Enabling ‘Persist’ will make those permanent additions to your SQL Developer configuration.
After clicking ‘Ok’, if you open the View – Task Progress panel, you’ll see there’s a new task running.
For this to work for your secure password scenario, you’re going to make at least one, if not two calls.
First, SQL Developer needs to be already open, with this Connection Helper task running. You can achieve that by making a call to SQL Developer from your bash or cmd prompt or simply open it like you always would from your Applications or Start Menu/shortcut.
Once it’s running, you can make another call to create/make the connection.
Making the connection from the CLI.
I’m on Windows, but since this is Java, it’ll be the same as on your Mac or Linux desktops.
Part of what was installed when you added this extension was a Connection Helper Client, a jar file.
This file goes here –
With the jar file located, we can now make our connection.
Open a terminal/cmd, and cd to that directory.
Call it.
java -jar ConnectionHelperClient.jar -HelperClientDemo2=hr/oracle@localhost:1521/orcl or java -jar ConnectionHelperClient.jar -HelperClientDemo2=hr@localhost:1521/orcl
Here’s an animated GIF of me using the feature.
Note that I have already made one connection, that’s why you see the call already there when I bring up the CMD window and why you already see a connection folder called ‘Transient’ with a similar connection going.
Transient vs Persistent Connections
If I close and re-open SQL Developer, i’ll see my Connection Helper task, but I won’t see my previous connections made with it. That’s because we left the ‘Persist’ options disabled in the preferences.
23 Comments
How do you submit a connection and have it configure using tns. When I launch it, it configures it as a basic connection
Greatly appreciate the Connection Helper. However, I started having issues today.
I can’t establish a connection and the panel shows ConnectionHelperTask-1 (Failed).
Is there any logging anywhere?
I’ve downloaded and installed this as you described and just like in your video, the command prompt responds with ‘Request Submitted’ when I enter some working connection details (I know they are working because I can connect using SQL Developer the ‘normal’ way).
Unlike your video, nothing then happens in SQL Developer; no connection banner pops up and no SQL Worksheet appears. Any idea what could be wrong??
Further investigation prove that the problem is linked to the error here:
https://github.com/oracle/oracle-db-examples/issues/125
Although I only downloaded this last week!!
The Dev, Brian, says he published that fix to both the public and his personal repo…are you sure SQLDev is using the right bits? Maybe try deleting it and re-adding it back?
I have tried removing it from SQL Developer and re-adding it but the problem still exists. I was using the ZIP file available directly from the update site (the process you describe above) so I thought that maybe the fix has only been applied to the code on GitHub so I downloaded the source code with a view to compiling it locally.
However, I am unsure whether I should be compiling ConnectionHelper (run ant _deploy from /sqldeveloper/extensions/java/ConnectionHelper) or ConnectionHelperClient (run ant _deploy from /sqldeveloper/extensions/java/ConnectionHelperClient)
I guessed at ConnectionHelper and the compiler returned several errors along the lines of cannot find symbol; TaskException and RaptorTaskManager for example
Please open an issue on the project page. I’m not the developer and can’t provide support.
Love the extension. The only thing I miss is the possibility to use TNS names in the connect string.
Hi, I dont see these options for sql developer installed on MAC. is there are any way we can achieve this on MAC?
It’s Java, so there’s nothing OS specific about it. Should be same on Mac, Windows, Linux.
I have downloaded the connectionHelper from check updates and later after by app restart, I cant see “connection Helper” under preferences. On windows it is showing up but on Mac I dont see it under preferences. Anything to be done here?
No, it should just work. I can ask my guy tomorrow for advice. You don’t see any messages written to the log panel, do you?
Hi Jeff,
Thank you for the TL;DR version!!
It has been 6 months since you last said “Not today, but that would be ideal”, so is it tomorrow yet?
Any plans to add this feature?
Regards.
I don’t have resources available to do it, hence the ideal.
Is there any way to run ConnectionhelperClient.jar outside of SQL Developer ? Maybe just leave the listener running, and have it spin up a new instance of SQL Developer for each connection request ?
Thanks !
Not today, but that would be ideal.
Is there any chance this can be considered for development ?
Thanks !
Is there a way to do this without having the SQLDeveloper session running ?
It would be nice to have where running the connectionhelper would start a new instance of SQL Developer and connects to the database specified in the arguments ?
Thnks !
That’s the rub, it has to be already running.
Exactly Madan, this would awesome.
That was it. Found it in the App archive at: “SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/extensions/oracle.db.example.sqldeveloper.extension.connectionHelper/lib”
Thanks Jeff!
This is just what I was looking for. Do you know where ConnectionHelperClient.jar is located on Mac? I’m not able to find it.
Thanks!
You have to open up the App archive, and then it should basically be in the same place.