Google cloud platform GCP -  mysql using Boomi Database connector - Access Denied

Document created by aarthi_sridhar Employee on Mar 23, 2018Last modified by sheng_liao462475 on Mar 23, 2018
Version 2Show Document
  • View in full screen mode

MySQL DB instance hosted at Google Cloud usually connects securely in Boomi process. Cloud SQL uses a self-signed server certificate and a certificate (public/private key pair) on the client. Boomi Database connector does connect to cloud based DB instance with SSL certificate. 


The GCP console allows to generate a public server certificate and create a private client certificate. If you are connecting securely then "Allow secure connection" has to be enabled in GCP console like in the picture. Gather these certificates. 

  1. Server certificate (server-ca.pem)
  2. Client public key certificate (client-cert.pem)
  3. Client private key (client-key.pem)

In Atom's Java_Home keystore:

Here is how to added certs to atom's keystore:

 

1. Convert server certificate pem to cer and import to keystore
keytool -importcert -alias GCPserver222018 -file C:\Users\\server-ca.der -keystore C:\Programs\Java\lib\security/cacerts -storepass changeit

 

2. Use openssl library to convert the client certificate to pkcs
openssl pkcs12 -export -in "client-cert.pem" -inkey "client-key.pem" -CAfile C:\Programs\Java\lib\security\cacerts -out newbundle.pkcs12
And now add this to java keystore
keytool -importkeystore -deststorepass changeit -destkeystore C:\Programs\Java\lib\security/cacerts -srckeystore C:\Programs\Java\lib\security/cacerts/bundleout.pkcs12 -srcstoretype PKCS12

 

As mysql is looking for a server certificates to be added to truststore where public certs are available and a keystore where private key certificates are available, Boomi atom has only one which is keystore that represents keystore and truststore.
We have to add -Djavax.net.ssl.keyStore=C:\Programs\Java\lib\security\cacerts  (and) -Djavax.net.ssl.keyStorePassword=password (password of keystore) in vmoptions file and restart the atom.

 

It is very very important that when you attempt to perform openssl step it will prompt for password and the password should be same as keystore password. The default keystore password is changeit.

 

Please check discussion here as to understand why it is required to set password the same (point6) : https://stackoverflow.com/questions/9761575/java-nosuchalgorithmexception-sunjsse-sun-security-ssl-sslcontextimpldefault
Mysql : https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html

 

One Easy way to confirm if this is the right combination of client server Certificate you received from GCP admin team is try connecting to mysql using MySql Workbench with SSL. 

 

 

 

In Boomi Database connector : 

In DB connector specify the DB properties along with useSSL=true.

 

 

Error and how to resolve : 

 

Unable to connect to database; Caused by: Unable to open database connection. Access denied for user 'root'@'69.174.34.83' (using password: YES)
If you receive this error, please check you use the right client server certificate, client certificate was bundled with same password as like keystore has and vm.options added with the lines suggested above. If GCP admin console has secured connection enabled then certificates are expected in Atom Java_Home keystore to establish secure connection.

 

 

Useful Links: 
Database connector : Database connection  ,  com.boomi.execution.ExecutionUtil  
Community links: How to Add Certificate to the Java Keystore , exception - Java NoSuchAlgorithmException - SunJSSE, sun.security.ssl.SSLContextImpl$DefaultSSLContext - Stack Overflow  (Point 6) 

MySql library to be added to userlib/database: Download mysql-connector-java-5.1.12.jar : mysql « m « Jar File Download  

GCP support documentation : Connection Options for External Applications  |  Cloud SQL for MySQL  |  Google Cloud 

Attachments

    Outcomes