How connect Pentaho Data Integration with Amazon R

Question

I´m having difficult with create a new connection on Pentaho Data Integration (Kettle) with Amazon RDS, Amazon needs a CA Cert, and I dont know how to input it to connection.

Someone can help me?

Tkx,

Answer 1

Finally got it to work. Here's how. I did not need my cert to do this

1. set up an ODBC DSN using the mySQL driver (you may have to download it from Oracle) test it to make sure it works (remembering that you connect with the endpoint in RDS)

2. in Kettle Get a generic database connector (this did NOT work with the mySQL connector with ODBC or JDBC) Enter your newly creadted DSN Enter the use name and password

Thats it. it worked!

I was able to access a query with the database table input step using my new connector.

Answer 2

I've never used Amazon RDS, but PDI connects to most everything with JDBC. I assume you've downloaded the cert file. Then the key is getting a connect URL that will successfully use the cert from Java. I'd suggest writing a short Java program that does nothing but connect to your DB. This question may be of some help:

MySQL JDBC over SSL problem

Once you get a URL that uses your cert, paste it into your PDI connection definition in the Custom Connection URL text box. Note, to get the custom connection URL field, you'll have to use the Generic connection type, not the MySQL connection type.

Answer 3

Establish Secure Connection (SSL) To AWS (RDS) Aurora / MySQL from Pentaho (PDI Kettle)

1. You need to create a new user id and Grant SSL rights to it. So this user id can connect to Aurora / MySQL only using Secured connection.
GRANT USAGE ON *.* TO 'admin'@'%' REQUIRE SSL
2. Download public RDS key (.pem fie) from AWS  (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html#Aurora.Overview.Security.SSL)
3. Downloaded file contains certificates / keys for each region. 
4. Split certificates from .PEM file into different .PEM files 
5. Use JDK keytool command utility to import all these PEM files into a single truststore (xyz.jks) file
    a. keytool -import -alias xyz.jks -file abc1.pem -keystore truststore
6. Configure JNDI entry for your Aurora / MySQL instance in Pentaho Properties File "data-integration\simple-jndi\jdbc.properties"
    a. Sample JNDI configuration 
    -------------------------------------------------------------------------
    RDSSecured/type=javax.sql.DataSource
    RDSSecured/driver=com.mysql.jdbc.Driver
    RDSSecured/user=admin
    RDSSecured/password=password
    RDSSecured/url=jdbc:mysql://REPLACE_WITH_RDS_ENDPOINT_HERE:3306/DATABASE_NAME?verifyServerCertificate=true&useSSL=true&requireSSL=true
    -------------------------------------------------------------------------
7. Make sure you copied MySQL connector jar in "lib" directory of your pentaho installation. Use connector version 5.1.21 or higher.
8. 
9. Create a copy of Spoon.bat / Spoon.sh based on your operating system E.g. Spoon_With_Secured_SSL_TO_RDS.bat or Spoon_With_Secured_SSL_TO_RDS.sh
10. Now we need to pass the truststore details to Pentaho at startup, so edit the copied script and append below mentioned arguments to OPT variable 
    a. -Djavax.net.ssl.trustStore="FULL_PATH\xyz.jks"
    b. -Djavax.net.ssl.trustStorePassword="YOUR_TRUSTSTORE_PASSWORD"
11. Use new script to start Spoon here after to establish the secure connection
12. Open/create your Job / Transformation
13. Go To View Tab - Database Connections and create new connection
    a. Connection Type: MySQL
    b. Access: JNDI
    c. JNDI Name: RDSSecured 
        i. Same as name used in JDBC.properties file
14. Test Connection and you are ready…. :)