ReportServer Forum

sothea

Member

Registered: 2016-07-26

Create Datasource using ssl

Hi,

I want to make connection to MySQL that stay on another server with ssl info.

I try to make the connection but there is no place to add the certification such as ca-cert.pem, client-cert.pem, etc.

I really appreciate for help.

Best regards,
Sothea

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Create Datasource using ssl

Hallo Sothea,

welcome to the ReportServer forum.

Configuring MySQL with SSL is indeed a bit cumbersome. I'll try to guide you through the steps.

First, there are two basic setups that we can look at:
1) Only the server sends a certificate. The client authenticates via username and password. This is usually sufficient to properly protect connections and somewhat simpler to set up.
2) Mutual authentication via certificates. This is required if you set up the user in MySQL with REQUIRES X509 .

As for the first case. The necessary JDBC URL Properties that we need to set are

useSSL=true
requireSSL=true (optionally)
trustCertificateKeyStoreUrl=file:/PATH/TO/TRUSTSTORE
trustCertificateKeyStorePassword=TRUSTSTORE_PASSWORD

The first property tells the driver to use SSL if possible. The second optional property tells the driver to fail if the server cannot open an SSL connection. Finally, the truststore tells the driver which certificates it should accept, that is, the truststore will contain the certificate of the MySQL server (or a certificate higher up in the trust chain).

Thus, what we need to do is to create a truststore to hold the ca-cert.pem. On a unix shell we can create the truststore and import the certificate via

keytool -import -alias mysqlServerCACert -file ca-cert.pem -keystore truststore

This will prompt you for a password and whether or not to trust the certificate (the answer is yes). It will then generate a file called truststore.

This is all we need in order to properly set up SSL with MySQL where only the server's certificate is used. The full jdbc URL could then be

jdbc:mysql://localhost:3306/DATABASE?useSSL=true&trustCertificateKeyStoreUrl=file:/PATH/TO/truststore&trustCertificateKeyStorePassword=PASSWORD

Now for the second case with additional client side authentication via a client certificate (client-cert.pem). For this we need a second store to hold the client certificate together with the corresponding private key. This store is usually called a keystore. The first step is to combine client certificate with its key. Given that you have the certificate (client-cert.pem) and the corresponding key (client-key.pem) you can generate this bundled (PKCS12) file via

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -out client.p12 -name clientalias

It will ask for a password that we will need in a moment in order to create the keystore and import the generated client.p12 file. For this we can use the command

keytool -importkeystore -destkeystore keystore -srckeystore client.p12 -srcstoretype PKCS12 -alias clientalias

This will generate the keystore (as a file named keystore) and import the previously generated client.p12 file. It will ask for the password set on the client.p12 file and also ask for a password to be used with the keystore. This second password is the one that we need to pass on to the jdbc driver, that is, there are now two new properties to configure

clientCertificateKeyStoreUrl=file:/PATH/TO/keystore
clientCertificateKeyStorePassword=PASSWORD

Finally, the full JDBC URL with client side certificates could look as follows:

jdbc:mysql://localhost:3306/DATABASE?useSSL=true&trustCertificateKeyStoreUrl=file:/PATH/TO/truststore&trustCertificateKeyStorePassword=PASSWORD&clientCertificateKeyStoreUrl=file:/PATH/TO/keystore&clientCertificateKeyStorePassword=PASSWORD

There is one final thing to note: currently ReportServer JDBC URLs can only hold 255 characters per default. You might need to increase this value. For this you need to alter the tables RS_DATABASE_DATASOURCE and RS_DATABASE_DATASOURCE_A and increase the length of the field named url.

I hope this helps.

Best Regards,
Arno

sothea

Member

Registered: 2016-07-26

Re: Create Datasource using ssl

I try with your steps and I get stuck with the following command:

keytool -importkeystore -destkeystore keystore -srckeystore client.p12 -srcstoretype PKCS12 -alias clientalias

After run this command, it is required to enter the password. But I can't find the keystore file.

How can I find the file (keystore)?

I really appreciate for your advice.

Best Regards,
Sothea

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Create Datasource using ssl

The keystore will be created in the directory you are working in. You should be asked three passwords, once for the new keystore, a confirmation for the new keystore and once for the client.p12.

Best Regards,
Arno

sothea

Member

Registered: 2016-07-26

Re: Create Datasource using ssl

Now I can make connection to the database by using ssl.

Really appreciate for your help and support.

Thanks,
Sothea