How to setup SSL on MariaDB hosted on AWS RDS with Embarcadero FireDAC

I'am trying to connect to a MariaDB instance hosted on AWS RDS over SSL

Looking to the docs: http://docwiki.embarcadero.com/RADStudio/Rio/en/Connect_to_MySQL_Server_(FireDAC)

Seems I need to set those 4 params:

UseSSL=True
SSL_ca=ca-cert.pem
SSL_cert=client-cert.pem
SSL_key=client-key.pem

but Amazon only gives me a rds-combined-ca-bundle.pem

I don't know much about TLS/SSL. I need to generate those .pem files? If yes, how?

I also tried to do like so:

            db_params->Add("DriverID=MySQL");
            db_params->Add("Server=my.rds.url.com");
            db_params->Add("Port=9999");
            db_params->Add("Database=my_database");
            db_params->Add("User_Name=my_user");
            db_params->Add("Password=my_password");
            db_params->Add("UseSSL=True");
            db_params->Add("SSL_ca=absolute\\path\\to\\rds-combined-ca-bundle.pem");

But it raises the exception

[FireDAC][Phys][MySQL] SSL connection error

Without user and password same thing...

Thanks in advance!

Solution

Late at the party, but somebody else may need this.

1. The server

First, you'll have to make sure your user uses SSL / TLS encryption on authentication. In a MySQL console, run the following SQL statement:

SHOW GRANTS FOR 'my_user';
+---------------------------------------------------------------------------------------------+
| Grants for my_user@%                                                                        |
+---------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, ... ON *.* TO `my_user`@`%` IDENTIFIED BY PASSWORD '*...' REQUIRE SSL |
+---------------------------------------------------------------------------------------------+

If the result does not contain REQUIRE SSL, you need to enable SSL / TLS on your user:

ALTER USER 'my_user' REQUIRE SSL;
FLUSH PRIVILEGES;

Run the SHOW GRANTS statement again to confirm that your user is now using SSL / TLS.

Of course, you'll have to run all this under admin / root, otherwise you won't be able to grant privileges.

2. The certificate

Then, you'll have to download the proper certificate, depending on where your RDS instance is located (ex. US East (Ohio) -> us-east-2-bundle.pem). Save that .pem in your path of choice. I saved mine in a relative path to my executable (ex. './Cert').

You can download certificates from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html.

3. The code

You're mostly correct, but for the sake of consistency, I'll setup everything on the connection from scratch. This is Pascal code, but you'll get the point.

DBConnection.LoginPrompt := False;
DBConnection.ConnectedStoredUsage := [auRunTime];
DBConnection.ConnectionName := 'my_db_connection';
DBConnection.ResourceOptions.AutoConnect := False;
DBConnection.Params.DriverID := 'MySQL';
DBConnection.Params.Database := 'my_database';
DBConnection.Params.UserName := 'my_user';
DBConnection.Params.Password := 'my_password';
DBConnection.Params.Add('Server=my.rds.url.com');
DBConnection.Params.Add('Port=9999');
DBConnection.Params.Add('LoginTimeout=15');
DBConnection.Params.Add('UseSSL=True');
DBConnection.Params.Add('SSL_capath=./Cert');
DBConnection.Params.Add('SSL_ca=./Cert/us-east-2-bundle.pem');
DBConnection.Open;