Connecting via jump server using sshtunnel/Paramiko in Python, authentication fails with "Password is required for key", but the key is not encrypted

I'm trying to access a remote server to and pull down data using PyMySQL cursor object. This remote server is on a vendor's AWS account, and is reachable via a Transit Gateway connection to one of my company's public subnets. I have the credentials for the SQL server instance, I have an EC2 that has been whitelisted and the remote network has an access rule that allows connection. Using this EC2 I can successfully execute the an ELT python code and bring over the data that I need.

What I'm trying to do now is use that EC2 as a jump server to connect to the remote sql server from my local machine, but am receiving an error. After feedback in the comments to my original post from @Martin Prikryl, I was directed to Connect to database through double SSH tunnel using Python Paramiko. This code is failing when I try to implement. The first part is to initialize a bastion host via:

from sshtunnel import SSHTunnelForwarder

private_key_path = 'c:\\Users\\*****\\Documents\\****.pem'
private_key = paramiko.RSAKey(filename=private_key_path)

print("Before SSHTunnelForwarder")

with SSHTunnelForwarder(
        'bastion_host',
        ssh_username="ec2-user", ssh_pkey=private_key,
        remote_bind_address=('**.***.***.***', 22),
        ssh_private_key_password="") as bastion:
    print("Inside SSHTunnelForwarder")
    bastion.start()

With the following printed to the terminal:

2023-12-20 12:00:39,390| ERROR   | Password is required for key C:\Users\****/.ssh\id_ed25519
Before SSHTunnelForwarder

2023-12-20 12:00:41,698| ERROR   | Could not resolve IP address for bastion_host, aborting!

And the following exception raised:

---------------------------------------------------------------------------
BaseSSHTunnelForwarderError               Traceback (most recent call last)
Cell In[9], line 9
      5 private_key = paramiko.RSAKey(filename=private_key_path)
      7 print("Before SSHTunnelForwarder")
----> 9 with SSHTunnelForwarder(
     10         'bastion_host',
     11         ssh_username="ec2-user", ssh_pkey=private_key,
     12         remote_bind_address=('**.***.***.***', 22),
     13         ssh_private_key_password="") as bastion:
     14     print("Inside SSHTunnelForwarder")
     15     bastion.start()

File c:\Users\USERNAME\pyenvironments\DataEng\Lib\site-packages\sshtunnel.py:1608, in SSHTunnelForwarder.__enter__(self)
   1606 def __enter__(self):
   1607     try:
-> 1608         self.start()
   1609         return self
   1610     except KeyboardInterrupt:

File c:\Users\USERNAME\pyenvironments\DataEng\Lib\site-packages\sshtunnel.py:1331, in SSHTunnelForwarder.start(self)
   1329 self._create_tunnels()
   1330 if not self.is_active:
-> 1331     self._raise(BaseSSHTunnelForwarderError,
   1332                 reason='Could not establish session to SSH gateway')
   1333 for _srv in self._server_list:
   1334     thread = threading.Thread(
   1335         target=self._serve_forever_wrapper,
   1336         args=(_srv, ),
   1337         name='Srv-{0}'.format(address_to_str(_srv.local_port))
   1338     )

File c:\Users\USERNAME\pyenvironments\DataEng\Lib\site-packages\sshtunnel.py:1174, in SSHTunnelForwarder._raise(self, exception, reason)
   1172 def _raise(self, exception=BaseSSHTunnelForwarderError, reason=None):
   1173     if self._raise_fwd_exc:
-> 1174         raise exception(reason)
   1175     else:
   1176         self.logger.error(repr(exception(reason)))

BaseSSHTunnelForwarderError: Could not establish session to SSH gateway

Note:

1. There is no password on the key
2. I've tried specifying an empty password
3. The Security Group that the jump box is on, is allowing SSH traffic from my IP address.
4. I can successfully SSH to the jump server via: ssh -i "***.pem" ec2-user@ec2-**-***-***-***.compute-1.amazonaws.com

The sshtunnel discovered another key in the ~/.ssh folder and it tried to use. As the key is encrypted, and you didn't specify any passphrase, the connection fails. Imo that's wrong behaviour of the library.

To workaround it, set host_pkey_directories to empty list, to avoid the sshtunnel automatically trying the keys in the ~/.ssh folder.

with SSHTunnelForwarder(
        'bastion_host',
        ssh_username="ec2-user", ssh_pkey=private_key,
        remote_bind_address=('**.***.***.***', 22),
        ssh_private_key_password="",
        host_pkey_directories=[]) as bastion:

From the other error message, it looks like the bastion_host is not a valid/routable hostname.