Category Archives: SSH

Solved (?) SQL Developer 19.2: No more authentication methods available when using SSH Connections

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:

  1. 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).
  2. 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.
  3. 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.Puttygen Notice

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…

Puttygen Conversion

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!


X11 Forwarding with MIT Magic Cookies to Oracle Cloud as multiple users

Every time I do this, I always forget the steps, especially for allowing connections after you su to another user… So, here we go:

I’m going to connect two machines: rotor (it’s a palindrome), my local Windows machine, and cloudbox, my Oracle Cloud server.

The first thing I’m going to do is connect into cloudbox as the opc user, become root and then update the sshd_config file with an X11UseLocalhost no entry. I also commented the previous value (which was already commented out) and added a comment about who changed what and when. Then I’ll restart the ssh daemon.

[opc@cloudbox ~]$ sudo -s
[root@cloudbox opc]# vim /etc/ssh/sshd_config

# 2019-08-12 Rich Soule changed below to allow remote X11 Connections
#X11UseLocalhost yes ## Original value commented out as in this line
X11UseLocalhost no

[root@cloudbox ~]# systemctl restart sshd

Next, we’ll make sure that enough of the X11 tools are on the server.

[root@cloudbox ~]# yum install xclock -y

I’m going to use MobaXterm as my client on my Windows box and open up a local terminal on rotor. MobaXterm automatically gives me an X11 Server so I don’t have to use something like Xming to give me a local X11 Server.

When I created cloudbox, my Oracle Cloud server, a private key file was created. This private key file was saved to rotor, my Windows box, in a folder on my local computer, so I’m going to cd to that folder and start an ssh connection:

[Rich.Rotor] ➤ ssh -X -i id_rsa opc@cloudbox

At this point, I have to provide the passphrase for my private key file that is in this directory. After supplying the passphrase, I’ll be connected to cloudbox.

Last login: Mon Aug 12 22:23:10 2019 from somewhere on the internet
/usr/bin/xauth:  file /home/opc/.Xauthority does not exist
[opc@cloudbox ~]$

The message above should really be something like “.Xauthority does not exist, so I’m creating it.” because that is what just happened. The DISPLAY environment variable was set to the IP address of the cloud server with a :10.0 appended to the end, and we can see what ended up in .Xauthority file by using the xauth list command:

[opc@cloudbox ~]$ echo $DISPLAY
10.10.0.2:10.0
[opc@cloudbox ~]$ xauth list
cloudbox.myreg.myvcn.oraclevcn.com:10  MIT-MAGIC-COOKIE-1  6ab3d32cf1c543ecaf83c79297ee3fbc

At this point, X11 based commands will now work, but only for the opc user.

[opc@cloudbox ~]$ xeyes&
[1] 13177

xeyes

If I become another user, then X11 commands won’t work.

[opc@cloudbox ~]$ sudo su - oracle
Last login: Mon Aug 12 22:35:15 GMT 2019 on pts/0
[oracle@cloudbox ~]$ xeyes&
[1] 13595
[oracle@cloudbox ~]$ Error: Can't open display:

[1]+ Exit 1 xeyes
[oracle@cloudbox ~]$

Looking above, it appears that the DISPLAY environment variable for the oracle user has yet to be set. The blank line after the message essentially tells us it has a NULL value. However, even if we set it, it still doesn’t work yet.

[oracle@cloudbox ~]$ export DISPLAY=10.10.0.2:10.0
[oracle@cloudbox ~]$ xeyes&
[1] 14213
[oracle@cloudbox ~]$ X11 connection rejected because of wrong authentication
Error: Can't open display: 10.10.0.2:10.0

[1]+  Exit 1                  xeyes
[oracle@cloudbox ~]$

The trick at this point is to pass along the MIT Magic Cookie that got generated for the opc user to the oracle user. The easiest way to do this is to just copy and paste the full output from the xauth list command as the opc user into an xauth add command as the oracle user:

[oracle@cloudbox ~]$ xauth add cloudbox.myreg.myvcn.oraclevcn.com:10 MIT-MAGIC-COOKIE-1 6ab3d32cf1c543ecaf83c79297ee3fbc
xauth: file /home/oracle/.Xauthority does not exist
[oracle@cloudbox ~]$ xeyes&
[1] 14512
[oracle@cloudbox ~]$

At this point, everything works and X11 commands will now display on my local Windows box from Oracle Cloud as the oracle user.

Happy Linuxing!

 


SQL Developer SSH Connections In Depth

Capture

Available now: A 29-page white paper on SQL Developer SSH Connections that explains in detail how SSH connections should be configured between your desktop/laptop and your organization’s database!

The vast majority of explanations out on the internet of how to configure SSH connections between Oracle SQL Developer and an Oracle Database use incredibly simplistic explanations, often using the oracle os account as the ssh user (absolutely a worst practice!), and not including important things like how firewalls will impact connections, how your ssh server should be configured, etc.

I wrote a 29-page white paper which has been published on the Insum.ca website. It covers the above and more in exacting detail. If you click the link above you’ll be taken to a description of the white paper and then to a page where you’ll be able to download it after supplying a name and email (a fair trade for the amount of time and effort I put into the white paper…).

Note: The image above is from the white paper and is used for explanation purposes only… None of those things actually exist except for the WordPress external firewall that protects just this blog, or, actually, all the blogs at WordPress.com.