2

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,

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Thiago MadPin
  • 104
  • 2
  • 6

4 Answers4

2

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…. :)
DPP
  • 31
  • 4
1

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.

Community
  • 1
  • 1
Brian.D.Myers
  • 2,448
  • 2
  • 20
  • 17
  • I´m not a java developer, do you have some tutorial to do it? And what is PDI? Tkx, – Thiago MadPin May 22 '13 at 20:49
  • PDI = Pentaho Data Integration ... or Kettle. Did you check the answer? You could use their launcher class for a sample. You'll have to investigate what options you need to use with your driver and adapt their code. That is unless you're using the MySQL driver. – Brian.D.Myers May 22 '13 at 20:58
  • Yes, I´m using MySql, but when I construct this class, how I will connect it to kettle? – Thiago MadPin May 23 '13 at 15:23
  • When you get your URL working, cut and paste it into the 'Custom Connection URL' text box in Kettle's connection configuration dialog. It looks like you can't do it with the standard MySQL connection; you'll have to use a Generic connection, but that's where you'll find the Custom Connection URL text box. – Brian.D.Myers May 23 '13 at 16:13
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.

user2372869
  • 21
  • 2
0

If this helps someone else, in my case after trying a lot of solutions without success, including the ODBC option. I solved the error downloading the required driver, for connecting to Amazon RDS database because I hand´t had problems connecting to other mysql servers, but I had this issue only for RDS, so download from this url : https://www.oracle.com/database/technologies/jdbc-ucp-122-downloads.html and unzip the ojdbc8.jar and put it into the lib folder of your PDI, restart your Spoon and you´re ready to get connected with the regular jdbc and mysql connection.

yunieski dieguez garcia
  • 81
  • 2