When Oracle upgraded SQL Developer from 19.1 to 19.2 they updated some libraries. One of those updates meant that SSH keypairs that had been generated by PuttyGen didn’t work anymore and you should “Generate OpenSSH format ppk” (see Oracle Support Doc ID: 2575854.1)
However, when attempting to do so using PuttyGen it didn’t seem to work. Taking a step back, here was the situation in 19.1 or earlier:
- You had SQL Developer 19.1 running on your desktop and had used the SSH Hosts dialog to create an SSH connection pointing SQL Developer at the private key on your machine (typically, this is an id_rsa file).
- The server you are connecting to has the associated public key (typically, id_rsa.pub) in the authorized_keys file associated with the account you were connecting to on the remote server.
- Everything worked as expected and you could connect to databases on the remote database server using the SSH connection.
After an upgrade to 19.2 (during which SQL Developer will ask you to import settings from a previous release), there is a chance that the private key that you previously had pointed to will no longer work.
My ‘used to work in 19.1 and earlier’ private key file looked like this:
-----BEGIN RSA PRIVATE KEY-----
Proc-Type: 4,ENCRYPTED
DEK-Info: AES-128-CBC,abcdefghijklmnopqrstuvwxyz123456
abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz12
[SNIP]
abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz12
-----END RSA PRIVATE KEY-----
If I opened that file in Putty Key Generator on a Windows 10 box, it said that it was already an OpenSSH SSH-2 Private key.
Since Oracle Support and others said that the key needed to be in OpenSSH format, one would assume that maybe you’d use the Conversion feature of Puttygen to convert the key…
Nope. No go.
After playing around for a long time I discovered the OpenSSH commands that are now built into Windows 10. After testing a million things, this is what actually worked:
C:\sshkeys> ssh-keygen -e -f id_rsa > id_rsa_for_sql_developer_19.2 Enter passphrase:
Once pointed at the id_rsa_for_sql_developer_19.2 file, SQL Developer happily prompted me for my (very long) passphrase and established a connection to my database.
Great! Problem solved.
However, and this is the strange thing that I don’t really understand… If you look at the contents of the generated file, you’ll see something like this:
---- BEGIN SSH2 PUBLIC KEY ---- Comment: "2048-bit RSA, converted by rich@ROTOR from OpenSSH" abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz12 abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz12 abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz12 abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz12 abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz12 abcdefghijklmnopqrstuv ---- END SSH2 PUBLIC KEY ----
Yep, that is what it looks like, a public key in SSH2 format (although I obviously obfuscated the above, for what reason, I do not know)…
Why an SSH2 Public Key works when you should have a private key (if I understand SSH connections correctly, and this sort of makes me feel like I don’t), I have no idea. But work it did, so I happily moved on.
As an aside, I did have an SR open with Oracle on this which I ended up closing myself, but I did ask them to update Doc ID: 2575854.1 with the above information before I closed it.
Happy SQL Developer-ing!
Cars and Code 
March 27th, 2020 at 9:22 am
followed your note but cant seem to get sql developer to work on my mac , I keep on getting the same error more authentication methods available
not really sure what else i can try , seems to work fine with 19.1
i create the keys with ssh-keygen on the mac and then ran your command
ssh-keygen -e -f id_rsa > id_rsa_for_sql_developer_19.2
Still does not want to work 😦
Thanks michael
March 27th, 2020 at 2:53 pm
Michael,
I feel your pain. I believe that the SQL Developer team is going to support ppk keys again in SQL Developer 20.1.
Right now I’m actually creating SSH tunnels using MobaXterm (there is a free version). MobaXterm has a great UI for building the tunnels and everything ‘just works’.
I set the local port to be something like 11521, bounce through my SSH server on port 22 and then redirect to the database server on 1521. You have to add the key after you create the initial configuration for the tunnel.
Here’s a link: https://blog.mobatek.net/post/ssh-tunnels-and-port-forwarding/
Rich
March 27th, 2020 at 2:54 pm
Ugh. Just realized that MobaXterm is PC only…
March 27th, 2020 at 4:19 pm
Check this out here:
https://insum.ca/sql-connection-via-encrypted-ssh-tunnel/
April 25th, 2020 at 9:29 am
Hi,
Did you get the solution for this.
I am trying to connect to AWS hosted RDS database which is behind firewall using local port forwarding. But the pem key doesnot work giving the same error as you.
I have just unzip SQL developer 19.4 and trying to configure. Do I need Oracle instant client or something? Or is this something else I can check?
June 11th, 2020 at 8:08 am
abhishekpatel0,
Currently, my solution is to create a tunnel using a tool like MobaXterm and then just have SQL Developer use that tunnel instead of trying to use the SSH tunneling feature within SQL Developer. Some of my team members have rock solid SSH connections with SQL Developer, others, like myself, can’t keep the connections working.
SSH tunnels can be created directly on the command line. Anton wrote this up quite well here: https://insum.ca/sql-connection-via-encrypted-ssh-tunnel/
October 26th, 2021 at 4:13 am
Hi Rich,
thank you for sharing this. It’s two years later now, I’m working with sqldev 21.2.1.204 and wanted to give you and readers a short heads up:
Yes, meanwhile sqldev accepts PuTTY’s .ppk format, again. BUT:
While loading private key files from a shared (cloud) drive on Windows, I got the error message “no more authentication methods available”. This is probably due to SSH restrictions around access rights to key files. Moving the key file to a personal /local directory with stricter access limits solved the issue for me.
Which is strange, because it worked with PuTTY in the first place. Looks like sqldev enforces SSh security practices more strictly.
Cheers,
Uwe
October 26th, 2021 at 9:19 am
Awesome! Thanks for that.
January 20th, 2022 at 3:24 pm
Hi Über
The issue is: my new DOH laptop uses SQL Developer 21, it always gives me connection error for access STC DB:
“failed: org.apache.ssh.common.SshException: No more authentication methods available”
I put the key in local directory and tried .ppk, also OpenSSH id_rsa converted from PuttyGen
Any idea?
January 20th, 2022 at 4:03 pm
Lily,
Are you using puttygen? This should now be working again with modern versions of SQL Developer (a new one just came out today).
April 27th, 2022 at 5:13 pm
I finally got it to work by swapping out the .ppk to a .pem format. I used the puttygen conversion tool to do the conversion.
(Version 21.4.1.349)
April 27th, 2022 at 5:20 pm
Yeah, over the various versions the formats have changed, but it’s nice to know that the .pem format works now.