MySQL(Google Cloud SQL): User Access denied only from Java

Question

I've set up Google Cloud SQL (MySQL 5.7) with SSL-Certificates.

Now when i try to connect with HeidiSQL it works with the following settings:

• Client Certificate
• Server Certificate
• Client key
• Url: localhost
• Port: 23306
• User
• Password
• Database

I forwareded the port from my production server to my local machine on port 23306. (The DB-Access is restricted to the IPs of the production-server)

Now i try to run my spring boot application with the same settings. But i always get this error:

java.sql.SQLException: Access denied for user 'mysqluser'@'x.x.x.x' (using password: YES)

I created a keystore and truststore and configured JVM to use them with the following parameters:

-Djavax.net.ssl.keyStore=files/keystore-dev
-Djavax.net.ssl.keyStorePassword=test
-Djavax.net.ssl.trustStore=files/truststore-dev
-Djavax.net.ssl.trustStorePassword=test

I assume that this works, because otherwise i would get and SSL-Exception before getting the AccessDenied Exception.

I used the following settings in application.properties of my Spring Boot Application:

spring.datasource.url=jdbc:mysql://localhost:23306/test_db?autoReconnect=true&useSSL=true&requireSSL=true
spring.datasource.username=mysqluser
spring.datasource.password=test
spring.datasource.driverClassName=com.mysql.jdbc.Driver

I copied these settings to HeidiSQL and it works there... I don't know what i should try to fix this. Any ideas?

Thank you.

Edit 1:

No i turned on SSL debugging and can see the following warning:

Warning: no suitable certificate found - continuing without client authentication

I seems that the connection falls back to non-ssl connection and i have disabled non-SSL connections to mysql-server.

But do you have any idea why this is not working?

I used the following description to generate the keystore and truststore from certificates and the key from google cloud sql: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html

Edit2 Here is some output from SSL Debug. Could it be that no key is found in keystore?

keyStore is : files/keystore
keyStore type is : jks
keyStore provider is : 
init keystore
init keymanager of type SunX509
trustStore is: files\truststore
trustStore type is : jks
trustStore provider is : 
init truststore
adding as trusted cert:
  Subject: C=US, O="Google, Inc", CN=Google Cloud SQL Server CA
  Issuer:  C=US, O="Google, Inc", CN=Google Cloud SQL Server CA
  Algorithm: RSA; Serial number: 0x0
  Valid from Mon Jan 16 22:54:25 CET 2017 until Wed Jan 16 22:55:25 CET 2019

trigger seeding of SecureRandom
done seeding SecureRandom

Answer 1

The problem was that the generated keystore was not correct.

Correct way: First i put the Google Cloud SQL Certificates and my local cacerts file in a folder.

Then i used the following script to generate trust- and keystore:

rm generated/*

cp cacerts generated/truststore

# Add Server certificate to truststore:
keytool -import -file server-ca.pem -alias mysqlServerCACert -keystore generated/truststore
# Change password:
keytool -storepasswd -keystore generated/truststore

# Convert key
winpty openssl pkcs12 -export -inkey client-key.pem -in client-cert.pem -name my-key -out generated/client.p12
# Import converted client certificate:
keytool -importkeystore -srckeystore generated/client.p12 -srcstoretype pkcs12 -destkeystore generated/keystore

The truststore and keystore will be generated in the "generated"-folder.