Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: SSH tunneling to connect PDI to Database

  1. #1
    Join Date
    Sep 2017
    Posts
    18

    Default SSH tunneling to connect PDI to Database

    Hello!

    I've been working with Pentaho CE for a few months now. Usually I use table input in PDI without any trouble, however, I had to obtain data from a new DB (PostgreSQL) and apparently I can only do so using ssh port tunneling. Now, other tools incorporate this capability out of the box, but I haven't found anything but posts saying you have to do that with another tool/client.

    Could anyone help me configure putty or another tool (using windows btw) to properly connect PDI with the Database? I've tried to do it myself but I fail to connect either way.

    If required I can post some screenshots of the process I followed.

    Thanks

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Trust me, you don't want an ETL tool (like Kettle) to do network management.

    What exactly is your problem - creating the ssh tunnel or using it from Kettle?
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Sep 2017
    Posts
    18

    Default

    I have created the tunnel with Putty, following this tutorial:

    http://realprogrammers.com/how_to/se...ith_putty.html

    So I have my comunter's port 5433 redirected to the port 5432 from the server with the database.

    After that I just try to add a connection in PDI with the settings:
    (Using Native -JDBC- access)

    Name:  gpd.jpg
Views: 1195
Size:  18.4 KB

    so I've tried both, using 127.0.0.1 and localhost as the host name. I've also tried Database name with and without '/' at the beginning... I just don't know what is wrong.

    The error message is the following one:

    Error connecting to database [Database Connection Name]: org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Error connecting to database: (using class org.postgresql.Driver)
    El intento de conexión falló.


    org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database
    .
    .
    .
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    Error connecting to database: (using class org.postgresql.Driver)
    El intento de conexión falló.

    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:585)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:456)
    ... 57 more
    Caused by: org.postgresql.util.PSQLException: El intento de conexión falló.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:259)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
    at org.postgresql.Driver.makeConnection(Driver.java:452)
    at org.postgresql.Driver.connect(Driver.java:254)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:567)
    ... 58 more
    Caused by: java.net.SocketException: Connection reset
    at java.net.SocketInputStream.read(SocketInputStream.java:210)
    at java.net.SocketInputStream.read(SocketInputStream.java:141)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
    at org.postgresql.core.PGStream.receiveChar(PGStream.java:293)
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:455)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:207)
    ... 65 more

    Hostname :127.0.0.1
    Port :5433
    Database name: DatabaseName
    Last edited by qwerty123; 02-13-2018 at 04:44 AM.

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Did you make sure the tunnel is working without Kettle, i.e. the firewall settings are ok?

  5. #5
    Join Date
    Sep 2017
    Posts
    18

    Default

    I do not have much control of firewall and other network settings in my company environment so I did nothing.

    Do you think it should work after the steps I followed if firewall,... was properly set? I mean, is this the correct way of doing it?

  6. #6
    Join Date
    Sep 2017
    Posts
    18

    Default

    So I downloaded RazorSQL to test the connection using the built-in ssh feature and it worked. Now, as a test I decided to try to connect to the database with PDI and it worked! So my problem is to make the connection properly with putty, not with firewall or because I have not configured well the Pentaho tool.

    I'll look into tunneling and post back my results. Thanks for your help marabu!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.