Visokio website     Downloads     Video tutorials     KnowledgeBase  
Data sources: Connecting to Amazon RDS database MySQL using ssl - Visokio Forums
Data sources: Connecting to Amazon RDS database MySQL using ssl
  •     donald July 22, 2014 8:25AM
    To connect to a MySQL database hosted on Amazon RDS using ssl.


    1. Download the public key for Amazon RDS.
    http://s3.amazonaws.com/rds-downloads/mysql-ssl-ca-cert.pem

    2. Add the key to the java keystore for use with Omniscope.

    You will need to install the latest Java SE Development Kit 7 to use the keytool. We will also be using the root certificates file provided with the java runtime.

    Java SE Development Kit 7 Downloads

    http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

    Create a directory for working with the certificates.
    Copy the Amazon RDS public key file that you downloaded into it.
    Copy the cacerts authority file from the jdk jre security library directory.

    mkdir C:\Users\USERNAME\omniscope-server\security
    cd C:\Users\USERNAME\omniscope-server\security
    copy "C:\Users\USERNAME\Downloads\mysql-ssl-ca-cert.pem” "C:\Users\USERNAME\omniscope-server\security\”
    copy "C:\Program Files\Java\jdk1.7.0_65\jre\lib\security\cacerts” "C:\Users\USERNAME\omniscope-server\security\”


    Import the Amazon RDS public key into the authority file.

    "C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe” -import -v -alias awsrdsmysql -file mysql-ssl-ca-cert.pem -keystore cacerts

    You will be prompted for the password which is "changeit”

    You will be prompted to Trust this certificate”, type yes

    Certificate was added to keystore
    [Storing cacerts]

    You can view the certificates to check using the following command
    "C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe” -list -v -keystore cacerts

    You now have a file with the default root certificates and the Amazon RDS mysql public key.

    3. Set the Java keystore file for Omniscope to this file.
    Edit the installconfig.properties file for your Omniscope installation.

    http://www.visokio.com/kb/installconfig

    Uncomment the ADDITIONAL_JVM_ARGS parameter if commented out and add the following , the value for -Djavax.net.ssl.trustStore must be the full path and filename of the keystore file.

    -Djavax.net.ssl.trustStore=”C:\Users\USERNAME\omniscope-server\security\cacerts"

    ADDITIONAL_JVM_ARGS=-Djavax.net.ssl.trustStore="
    C:\Users\USERNAME\omniscope-server\security\cacerts"


    4. Download and install the latest official JDBC driver for MySQL - MySQL Connector/J.
    http://dev.mysql.com/downloads/connector/j/

    5. Start Omniscope and connect to the database using the Database table source or Database table output.

    Connection details
    Select database: JDBC (Advanced)
    Driver jar path: path to the MySQL Connector/J JDBC driver you installed earlier
    e.g.
    C:\Program Files (x86)\MySQL\MySQL Connector J\mysql-connector-java-5.1.31-bin.jar

    Driver Class: com.mysql.jdbc.Driver
    JDBC URL: jdbc:mysql://HOSTNAME:PORT/DATABASENAME?useSSL=true

    The HOSTNAME:PORT is the authorized Endpoint listed in the RDS Dashboard for the database instance. You need to append the useSSL=true parameter to the url.
    See http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html for the JDBC URL Format.

    e.g.
    jdbc:mysql://visokiodbinstance.cnyz7m0qcuin.eu-west-1.rds.amazonaws.com:3306/visokiomysqldb?useSSL=true



    image

    image
  • 3 Comments
  • Robinp July 23, 2014 11:11AM
    Hi,

    Thanks for this it has seemed to work a treat. However it has started causing errors when trying to connect to other services (Google). I can no longer email directly out from Visokio no can I connect to a Google Spreadsheet.

    It will read:
    "Server SSL error
    The server you are connecting to does not have a valid certificate from an authorised authority"

    When I comment out the ADDITIONAL_JVM_ARGS line (added in this tutorial) from installconfig.properties the Google connection resumes and Google interacts with Visokio again.

    Any insight would be greatly appreciated.

    Robin
  •     donald July 23, 2014 12:32PM
    Can you check that the original certificate authority root keys supplied with the Java jdk have been copied to the new certificate ?

    Open a command prompt and change to the directory with the new keystore that you are using with Omniscope.

    Check the original certificates supplied with Java
    "C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe" -list -v -keystore "C:\Program Files\Java\jdk1.7.0_65\jre\lib\security\cacerts"

    When prompted to Enter keystore password use "changeit"

    You can save the text output to a file to make it easier to check.
    "C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe" -list -v -keystore "C:\Program Files\Java\jdk1.7.0_65\jre\lib\security\cacerts" > cacerts_original_jdk_caroot.txt

    Open the text file "cacerts_original_jdk_caroot.txt" in notepad and there should be at least 87 entries of "Entry type: trustedCertEntry"

    Now check the new certificate that you are using with Omniscope.
    "C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe" -list -v -keystore "cacerts"

    Save the text output to a file to make it easier to check.
    "C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe" -list -v -keystore "cacerts" > cacerts_new.txt

    Open the text file "cacerts_new.txt" in notepad and there should be one extra entry of "Entry type: trustedCertEntry" which is the one we added to the keystore.


    Alias name: awsrdsmysql
    Creation date: 22-Jul-2014
    Entry type: trustedCertEntry

    Owner: CN=aws.amazon.com/rds/, OU=RDS, O=Amazon.com, L=Seattle, ST=Washington, C=US


    If the number of entries is less than 88 then you will have to go through the commands listed above to recreate the cacerts file.

    Make sure that you copy the original cacerts file supplied with Java as the aim is to append the Amazon RDS MySQL certificate to the "root" certificates.

    Attachments
    cacerts_new.txt 120K
  • Robinp July 24, 2014 6:58AM
    This worked perfectly, all is running properly now.

    Much appreciated.

    Robin

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership