Unable to SSH tunnel to known-reachable RDS DB via Amazon Linux 2023 EC2 Instance

[modernjess]

Describe the bug I seem to be unable to connect to an RDS database (MariaDB, if it matters) via SSH tunneling through an Amazon Linux 2023 EC2 Instance. Interestingly, I can connect to the same database via an older Amazon Linux 2 EC2 instance in the same (public) VPC, security group, and subnet within AWS.

I can successfully connect to either EC2 instance via SSH (in Terminal) without issue.

I can also connect to the database with SQLPro via the manual SSH tunnel method: ssh -i .ssh/key.pem -L 3307:blah.blah.us-west-1.rds.amazonaws.com:3306 ec2-user@12.34.56.78 -N (And then opening a database connection to localhost:3307)

Also, both EC2 instances are running a PHP application that connects to the database in question, and both function normally in that regard. So I am fairly certain that the database is reachable via both EC2 instances, and that this isn't an issue with security groups, VPCs, etc.

To Reproduce Steps to reproduce the behavior:

1. Go to 'Connect…'

2. Click on 'New -> MySQL'

3. Configure database host (blah.blah.us-west-1.rds.amazonaws.com), login and password for the database

4. Click Advanced -> SSH Tunneling

5. Configure EC2 instance as Server host (in my case, I'm using the bare EC2 public IP address)

6. Configure login (ec2-user)

7. Configure key file (key.pem)

8. Uncheck "Store in iCloud"

9. Click Accept

10. Hit Save

11. Attempt to connect to the database by clicking on the new connection

12. Observed error message: Failed to authenticate via private key. Authentication failed or partial success. (4)

Expected behavior A successfully opened connection to the database.

Screenshots

Environment details (please complete the following information):

• Device: MacBook Pro (Apple M1 Max, 2021)
• OS: Sonoma 14.3.1 (23D60)
• SQLPro app Version: SQLPro for MySQL Version 2024.15 (Build 105088.5)
  • Installation source: App store
  • Target database server: MariaDB 10.6 running in Amazon RDS

Additional context This seems to be the relevant part of the log, but let me know if you want more. I'm leery of posting the whole thing because I'm using production keys at the moment.

        Sent public-key request.
        AuthList: publickey,gssapi-keyex,gssapi-with-mic
        Proceeding with publickey authentication...
        padAndSignHash:
            keyType: Private
            hashInSize: 20
            padding: PKCS v1.5
            HashOid: 1.3.14.3.2.26
            (leaveContext 3ms)
        rsaSigLen: 256
        Sent public-key request with signature.
        hostKeyAlg: 3
        Authentication failed or partial success. (4)
        PartialSuccess4: 0
        When partial success is 0, it means you are not using the correct private key that corresponds to the public key installed in the SSH user account.
        Also, to force Chilkat to continue with password authentication even if the public-key authentication fails, set UncommonOptions = "PubkeyOrPassword".
        AuthList: publickey,gssapi-keyex,gssapi-with-mic
        Publickey authentication failed..

I'm quite willing to believe that I've neglected some critical detail, but for the life of me I can't figure out why the older AL2 EC2 instance works and the newer AL2023 EC2 instance does not. Also, in trying to find a possible solution, I've done some digging through the other issues here in this project, and found this one as a fairly interesting parallel. Unfortunately, it looks like it was closed without a solution.

That said, I'm moderately experienced with linux, ssh, and AWS infrastructure, and I am quite willing to be a useful guinea pig to help figure out where the issue lies. I suspect some subtle configuration difference with the SSH host is the likely candidate, but I don't know.

I'm also definitely not demanding an answer here, or a refund. SQLPro is an indispensable part of my workflow and this is (probably) not a dealbreaker issue for me. But having this feature work (as it has been, quite reliably, on previous EC2 instances) would certainly make my life easier.

And finally, it would be very helpful to know where SQLPro stores the equivalent of known_hosts, as I'd like to reset it so I can try a fresh connection. I've hunted around and come up empty -- it's definitely not storing it in the traditional ~/.ssh/ folder on my Mac.

[modernjess]

Never one to leave well enough alone, I continued digging for answers. I might have found one here:

https://forum.scootersoftware.com/forum/beyond-compare-4-discussion/general/91191-issues-with-ssh-connection-to-amazon-linux-2023

This suggests that there is a problem using SHA1 with RSA keys and Amazon Linux 2023, as SHA1 is disabled by default.

Sure enough, I generated a new ED25519 key, put the public portion into a failing AL2023 EC2 instance, and then tried a new SQLPro connection using the ED25519 key. Sure enough, it tunnels to the DB successfully.

So I think I can say with some confidence that SSH tunneling to an AL2023 instance with SQLPro will not work when using RSA keys. Further, SQLPro SSH connections differ in this regard from plain Terminal SSH connections, for reasons that I'll leave you to investigate further.

Fortunately, there's an easy workaround: Use ED25519 key pairs instead of RSA key pairs.

I hope someone who has been banging their head against their keyboard (as I have been all day) will find this someday.

[hankinsoft]

Hi, First sorry for the issue!

And finally, it would be very helpful to know where SQLPro stores the equivalent of known_hosts, as I'd like to reset it so I can try a fresh connection. I've hunted around and come up empty -- it's definitely not storing it in the traditional ~/.ssh/ folder on my Mac.

These are stored in the keychain along side of the connections. If you open on the keychain app and search for 'RecentConnections' you will find a bunch of entries related to SQLPro (generally one per connection type, mysql, mssql, etc). Each entry contains a json array of the connections for that type including ssh details.

I had two questions as well if possible:

1. So you are all set and running now?
2. Would it be possible to reproduce this setup and provide me credentials/keys via email? That way I can test the configuration myself and see if I can come up with any sort of solution?

[modernjess]

I have an easy workaround, so no urgency from me.

I'd be happy to set up an isolated environment to test on. Should have it up within an hour.

[hankinsoft]

Oh awesome! I can be reached via kyle @ hankinsoft.com. Thanks a bunch!

[modernjess]

I've put together a complete test setup and sent you the details via email. Good luck!

[hankinsoft]

Closing as we have sorted this via email. The latest build is also fixed on the App Store, but please feel free to reopen if there are any issues.

Please consider leaving the app a rating or review if you get the chance. As an independent developer, they help me out a ton. There is even a quick shortcut to do so by running the query 'rate;'.

[modernjess]

Thanks much!