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.

This will let you pull any/all of our open source extension examples.

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.

You just want the first one, Connection Helper

Click Next and confirm the install by hitting ‘Finish

I cheated & put the restart confirmation on top of this Wizard, you’ll see it after you click ‘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.

There’s a new item called ‘Connection Helper’ – go there.

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.

So to open SQL Developer with a connection from the CLI, SQL Developer needs to already be open.

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 –

You’ll probably want to find that, and copy it to your SQL Developer BIN folder, or to wherever you want to ultimately call your connection requests.

With the jar file located, we can now make our connection.

Open a terminal/cmd, and cd to that directory.

Call it.

I pass the name of my connection, and the connection properties.
java -jar ConnectionHelperClient.jar -HelperClientDemo2=hr/[email protected]:1521/orcl
or
java -jar ConnectionHelperClient.jar [email protected]: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.

Presto! The dialog you see behind the CMD prompt is my Oracle connection banner message.

Transient vs Persistent Connections

I’ll only see this folder and any connections made with the Helper during this instance of SQLDev.

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.

thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

21 Comments

  1. Avatar
    Dan Griffiths Reply

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

    • thatjeffsmith

      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?

    • Avatar
      Dan Griffiths

      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

    • thatjeffsmith

      Please open an issue on the project page. I’m not the developer and can’t provide support.

  2. Avatar
    Ulf Carlsson Reply

    Love the extension. The only thing I miss is the possibility to use TNS names in the connect string.

  3. Avatar
    Nitesh Devineni Reply

    Hi, I dont see these options for sql developer installed on MAC. is there are any way we can achieve this on MAC?

    • thatjeffsmith

      It’s Java, so there’s nothing OS specific about it. Should be same on Mac, Windows, Linux.

    • Avatar
      Nitesh Devineni

      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?

    • thatjeffsmith

      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?

  4. Avatar
    Mohamad Rahim Reply

    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.

  5. Avatar
    Madan Sudhindra Reply

    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 !

  6. Avatar
    Madan Sudhindra Reply

    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 !

  7. Avatar
    David Hollenberger Reply

    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!

  8. Avatar
    David Hollenberger Reply

    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!

    • thatjeffsmith

      You have to open up the App archive, and then it should basically be in the same place.

Reply To Ulf Carlsson Cancel Reply

RSS
Follow by Email
LinkedIn
Share