13

I am trying to remotely connect Google Data Studio with our MySQL Database, which is hosted on an AWS instance. To allow for a secure connection, we added SSL access to the AWS's MySQL database user as recommended in the documentation:

GRANT USAGE ON *.* TO 'encrypted_user'@'%' REQUIRE SSL;

The problem here is that AWS, unlike GOOGLE CloudSQL, only generates a Server certificate, and not a Client certificate, nor a Client private key (as far as I can tell). Both the latter is needed to enable SSL for Google Data Studio & MySQL connection.

enter image description here

Just to add a side-note, we also white-listed Google's recommended IPs as listed here. There are a lot of users in this thread complaining that white-listing specific IPs does not work, they had to add wildcard on the subnets. So we have also added addresses of the /16 subnets for each IP: 

64.18.%.%
64.233.%.%
66.102.%.%
66.249.%.%
72.14.%.%
74.125.%.%
108.177.%.%
173.194.%.%
207.126.%.%
209.85.%.%
216.58.%.%
216.239.%.%

Finally, one does not need to restart the AWS firewall after white-listing new IPs, it is immediately in-effect.

My Questions:

  • Is there absolutely no way to create a client certificate and a client private key on MySQL hosted on AWS ?

  • I would really want to use SSL between Google Data Studio (GDS) and our MySQL-DB, but the GDS-UI does not allow us to connect without filling in the client certificate and client private key. Is there any work around at the moment for me to allow this secure connection ?

Thanks in advance!

d_-
  • 1,391
  • 2
  • 19
  • 37
  • 2
    I am interested into this question too. Something that came to my mind is that could we terminate SSL connection at for example at a EC2 instance, with a running stunnel or something. I will share my experiences as soon as I've experimented this us too. – Ville Mattila Apr 27 '18 at 08:41
  • Unfortunately I think that this is not possible. The reason is that it looks like Google don't allow to use self-signed client certificate. The only way to create a client certificate signed by the same CA (Amazon), is to have the private key and it obviously impossible. – Juan Lago Aug 22 '18 at 08:32

1 Answers1

4

I was able to establish SSL connection between Google Data Studio and Amazon RDS PostgreSQL using Amazon server certificate and self-signed client cert + key created with OpenSSL:

openssl req -newkey rsa:2048 -nodes -keyout client.key -x509 -days 365 -out client.crt

Taken from https://stackoverflow.com/a/48994943/2789084.

George
  • 116
  • 8
  • 7
    I get a nice generic "Can't connect to the database. Please double check your connection parameters." when I use Amazon's certificate and my own generated client key and cert. Nothing really useful to try and debug this issue... so if anyone else knows more, please post info :) – Charlie Schliesser Jul 19 '18 at 15:34
  • Ideally, the above answer shouldn't be accepted as it talks about the connection between Amazon RDS and PostgreSQL, while the original issue is establishing a connection between Amazon RDS and MySQL, and the above-accepted solution doesn't work for that. – Zubair Jun 10 '21 at 06:22
  • Using this worked for me. Maybe you needed to make inbound rules in AWS for the Google IP addresses? @Zubair this helped me toward the right solution, but since I'm using Postgres, I made a different Q/A here https://stackoverflow.com/questions/70625623/how-can-i-connect-google-data-studio-to-an-aws-rds-postgres-instance – claptimes Jan 07 '22 at 18:28