Configuring SSL Encrypted Connections Between the SQL Gateway and the MySQL Database

Procedure

In order to encrypt the connection between the SQL Gateway and the MySQL database by SSL, perform the following steps:

Step

Action

1

Create self-signed certificates for both, the client and the server.

2

Configure the MySQL server.

3

Configure the MySQL database for verifying client certificates (if required).

4

Configure the SQL Gateway.

Creating Self-Signed Certificates for MySQL Connections

The installation of the SQL Gateway contains a batch file that creates self-signed certificates for the MySQL server.

As a prerequisite for executing the batch file, OpenSSL must be installed on the PC. To obtain OpenSSL, visit http://www.openssl.org.

Proceed as follows:

Step

Action

Details

1

Open the batch file.

In the Settings > Database Certificates tab, select Open folder > MySQL.

2

Adapt the batch file template.

  1. Replace the placeholder "MYSQL_SERVER_TEST" by the name that will be given to your root certificate.

  2. Replace the placeholder "Computer_Name" by the full computer name or the host name of the server PC if name verification is required. It is used to identify the server certificate.

  3. Replace the placeholder "MYSQL_SERVER_TEST_CLIENT" by the name that will be given to your client certificate.

3

Execute the batch file.

Three certificates are created:

  • Root certificate (ca.pem)

  • Server certificate (server-cert.pem and server-key.pem)

  • Client certificate (client-cert.pfx)

4

Copy the root certificate and the server certificate to the data directory of the MySQL server.

Copy the client certificate to the PC running the SQL Gateway.

–

5

Adapt the my.ini file of the MySQL server as described in the Configuring the MySQL Server section.

–

6

Restart the MySQL server.

–

7

Import the client certificate to the certificate store as described in the Managing Certificates in the Certificate Store section of this document.

Result: The client certificate and the root certificate are imported.

Configuring the MySQL Server

Adapt the my.ini file of the MySQL server.

To activate SSL and to use the server certificate for SSL encrypted connections, set the options ssl, ssl-ca, ssl-cert, ssl-key as indicated in the following example using the default MySQL data folder.

Insert the following lines at the end of the my.ini file. 

ssl
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

Configuring the MySQL Database for Verifying Client Certificates (Optional)

Configure this optional step if the client certificates should be verified by the MySQL database before the connection is established.

To achieve this, set up the SSL options of the Database User Accounts by executing the command ALTER USER:

Command

Description

REQUIRE X509

Requests a certificate from the client.

REQUIRE ISSUER 'issuer'

Allows access to clients providing a certificate that complies to the defined issuer.

REQUIRE SUBJECT 'subject'

Allows access to clients providing a certificate that complies to the defined subject.

Configuring the SQL Gateway

In the Configuration tab of the SQL Gateway, configure the SSL parameters:

Step

Action

1

Select the suitable entry from the list of Database Servers.

2

On the right-hand side, set the parameter SSL Encryption to ON.

3

Select the suitable option for validating the server certificate with the Server Validation parameter.

4

Select the suitable option for validating the client certificate with the Client Certificate parameter.

Validation of Server Certificates

For MySQL connections, the Configuration tab of the SQL Gateway allows you to configure how to evaluate server certificates.

If the parameter SSL Encryption is set to ON, the parameter Server Validation provides the following options:

Server Validation option

Description

Validate Certificate

The SQL Gateway computer validates the server certificate.

Validate Certificate + Verify Name

The SQL Gateway computer validates the server certificate and verifies the name.

NOTE: If this option is used, the parameter Server Address must match the subject name in the server certificate.

No Validation

The server certificate is not verified by the SQL Gateway computer.

 WARNING
UNAUTHENTICATED ACCESS
  • Use the No Validation setting only for testing purposes.
  • Do not use the No Validation setting during operation.
Failure to follow these instructions can result in death, serious injury, or equipment damage.

Client Certificates

For MySQL connections, the Configuration tab of the SQL Gateway allows you to select a client certificate to be used for the SSL connection to the MySQL server.

If the parameter SSL Encryption is set to ON, the parameter Client Certificate provides the following options:

Client Certificate option

Description

None

Select the option None if no client certificate is provided.

From File

Select the option From File if a client certificate is provided as a .pfx file. The following parameters are additionally displayed:

  • Certificate File allows you to browse for the .pfx file or to enter the path to the .pfx file.

  • Certificate Password allows you to enter the password for the .pfx file.

From Personal Store

Select the option From Personal Store if the client certificate is installed in a certificate store. The following parameters are additionally displayed:

  • Certificate Store that provides the options Current User and Local Computer.

    NOTE: If the option Current User is selected, the SQL Gateway service must run under this user account. In this case, use the option Local Computer.

  • In the Certificate Thumbprint, you can enter the thumbprint of the client certificate.

EIO0000002417.07

© 2022

Schneider Electric.

All rights reserved.