Connecting to Vertica DB via SSH Tunnel with PEM file using Java automation

Question

I'm trying to connect to a remote Vertica DB using Java and connecting to it involves an SSH tunnel with the addition of a PEM file as a Public Key.

However, after creating a port forwarding session using JSch AND THEN creating a connection to the DB using JDBC, I am faced with the following error:

java.sql.SQLNonTransientConnectionException: [Vertica]VJDBC Failed to connect to host 10.13.3.213 on port 5433. Reason: Failed to establish a connection to the primary server or any backup address due to network error

The code were I am trying to connect is bellow and after that is the PortForwarder.openConnection method:

int localBindingPort = PortForwarder.openConnection();
        JDBCurl = String.format(JDBCClass + "%s:%s/%s", Host, localBindingPort, Schema);
        return DriverManager.getConnection(JDBCurl,
                username,password);

JDBCurl value ends up as "jdbc:vertica://10.13.3.213:5433/DATA_SERVICES"

public class PortForwarder extends DBConfig {
static String portsString = Port + ":" + Host + ":" + SSHPort; // port:host:hostport

static Session session = null;

public static int openConnection() {

    int assigned_port = -1;

    JSch jsch = new JSch();

    try {


        int lport;
        String rhost;
        int rport;

        String[] portParams;


        portParams = portsString.split(":");

        lport = Integer.parseInt(portParams[0]);
        rhost = portParams[1];
        rport = Integer.parseInt(portParams[2]);

        jsch.addIdentity(pemFile);

        session = jsch.getSession(SSHUsername, SSHHost, 22);

        session.setConfig("StrictHostKeyChecking", "no");
        session.setPassword(password);

        session.connect();

        assigned_port = session.setPortForwardingL(lport, rhost, rport);

    } catch (Exception e) {
        System.err.println(e);
        System.exit(1);
    }

    return assigned_port;
}

public static void closeConnection() {
    if (session != null) {
        session.disconnect();
    }
}

Have you tried connecting to that host via a known-working client? — Federico klez Culloca
– Federico klez Culloca, Commented Jul 2 at 10:10
It might be a good idea to post the value of JDBCurl (which I read as being something to do with Curl). Variable names ought to begin lower case — g00se
– g00se, Commented Jul 2 at 11:31
Have updated the post to include the final JDBCurl value as ""jdbc:vertica://10.13.3.213:5433/DATA_SERVICES". It's not Curl, it's supposed to be JDBC URL, but can see how it looks that way. — Will Preston
– Will Preston, Commented Jul 2 at 12:23
I suggest jdbcUrl in preference. afaik, SSH tunnelling normally operates on the basis that one connects to the localhost using the forwarding port. At least that's the way I do it. So by that reckoning, that url could be jdbc:vertica://localhost:5433/DATA_SERVICES but I'm not sure what JSch expects. — g00se
– g00se, Commented Jul 2 at 12:35

Will Preston · Accepted Answer · 2025-09-10 13:32:57Z

Managed to find a solution with the following methods:

private final SSHPEMTunnel sshTunnel;
private Connection dbConnection;

/**
 * Creates a new Vertica database connection through an SSH tunnel.
 *
 * @param sshHost SSH server hostname
 * @param sshPort SSH server port
 * @param sshUser SSH username
 * @param pemFilePath Path to the PEM private key file
 * @param dbHost Vertica database hostname
 * @param dbPort Vertica database port
 * @param dbName Database name
 * @param dbUser Database username
 * @param dbPassword Database password
 */
public VerticaSSHConnection(String sshHost, int sshPort, String sshUser, String pemFilePath,
                           String dbHost, int dbPort, String dbName, String dbUser, String dbPassword) {
    // Create SSH tunnel with local port forwarding
    this.sshTunnel = new SSHPEMTunnel(
        sshHost,
        sshPort,
        sshUser,
        pemFilePath,
        findFreePort(),  // Automatically find a free local port
        dbHost,
        dbPort
    );
}






    
    /**
     * Finds a free port on the local machine.
     *
     * @return a free port number
     */
    private int findFreePort() {
        try (java.net.ServerSocket socket = new java.net.ServerSocket(0)) {
            return socket.getLocalPort();
        } catch (IOException e) {
            LoggingTools.printLogLineWarn(DBToolsLogValues.FAILED_TO_FIND_FREE_PORT_DEFAULT);
            LoggingTools.printLogLineWarn(DBToolsLogValues.ERROR_PREFIX +
                    e);
            return 5433; // Default Vertica port as fallback
        }
    }

Collectives™ on Stack Overflow

Connecting to Vertica DB via SSH Tunnel with PEM file using Java automation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related