Add a subject alternative name to a MySQL server SSL certificate

If a MySQL server uses SSL and the trustServerCertificate property is set to false, attempts to connect to the server can result in the following error if the certificate uses only the common name (CN) field:

tls: failed to verify certificate: x509: certificate relies on legacy Common Name field, use SANs instead

To resolve this issue, see the following sections:

What does this message mean?

This error message indicates that the SSL certificate being used for the MySQL server does not include the required subject alternative name (SAN) field. It uses only the legacy common name (CN) field.

In the past, SSL certificates relied solely on the certificate's CN field to specify the hostname for which the certificate is valid. However, modern SSL/TLS standards recommend using the SAN extension to specify the valid host names. The CN field is deprecated for this purpose.

The SAN extension allows a single SSL certificate to specify multiple host names (such as domain names or IP addresses) that are valid for secure communication. SANs provide greater flexibility and compatibility with various use cases, including scenarios where multiple host names need to be secured under a single certificate.

When a client (such as Platform Connect's onboarding process) attempts to establish an SSL/TLS connection to a MySQL server, it verifies the server's SSL certificate. If the certificate lacks the SAN field or contains an incomplete list of valid host names, the client might fail to validate the certificate, resulting in the error message shown above.

Update the certificate to include the SAN

To address this issue, you must regenerate or update the SSL certificate for the MySQL server to include the SAN extension with the appropriate host names. The certificate should list all the host names (or IP addresses) that clients will use to connect to the MySQL server.

Task 1: Create a self-signed certificate

Create a CA key and certificate. This will be used to sign your server certificate:

openssl genrsa -out ca-key.pem 2048
openssl req -new -x509 -nodes -key ca-key.pem -subj "/CN=MyCA" -days 365 -out ca.pem

Generate a private key for your server and then create a certificate signing request (CSR):

openssl req -newkey rsa:2048 -keyout server-key.pem -out server-req.pem -subj "/CN=localhost"

Create a configuration file called san.cnf to specify the SAN field:

echo -e "[req]\nreq_extensions = v3_req\ndistinguished_name = req_distinguished_name\n\n[req_distinguished_name]\n\n[v3_req]\nsubjectAltName = @alt_names\n\n[alt_names]\nDNS.1 = localhost" > san.cnf

[req]
req_extensions = v3_req
distinguished_name = req_distinguished_name

[req_distinguished_name]

[v3_req]
subjectAltName = @alt_names

[alt_names]
DNS.1 = localhost

Use the CSR and the CA to generate the server certificate:

openssl x509 -req -in server-req.pem -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem -days 365 -extfile san.cnf -extensions v3_req

Be sure to remove the passphrase from the private key. You can use OpenSSL before making a connection:
```
openssl rsa -in server-key.pem -out server-key-nopass.pem
```

Task 2: Add the certificate to the trusted certificate store

To ensure that the SSL certificate is trusted, add it to the trusted certificate store.

Add the certificate on a Windows server

Convert the certificate file from PEM format to CRT format. You can use OpenSSL or other tools for this conversion. For example:
```
openssl x509 -inform PEM -in ca.pem -outform DER -out ca.crt
```
Add the certificate to the Windows trust store. You can do it like this:
1. Open the Microsoft Management Console (MMC) by searching for mmc in the Windows Start menu and running it as an administrator.
2. In the MMC, choose File > Add/Remove Snap-in.
3. Select Certificates, and click Add.
4. Select Computer account, and click Next.
5. Select Local computer, and click Finish.
6. Close the Add or Remove Snap-ins window.
7. In the MMC, expand the Certificates (Local Computer) node.
8. Right-click Trusted Root Certification Authorities, and click All Tasks > Import.
9. Follow the instructions in the wizard to import the CA certificate (ca.crt) into the trusted root store.
  
  After it is imported, Windows trusts certificates signed by the CA represented by the imported certificate.

Add the certificate on a Linux server

Copy the CA certificate file (ca.pem or ca.crt) to the directory /etc/ssl/certs on your Linux system. You might need superuser privileges to perform this operation.
Ensure that the certificate file has the appropriate permissions. It should be readable by the system but not writable by regular users. You can set the permissions using the chmod command:
```
sudo chmod 644 /etc/ssl/certs/ca.pem
```
Optionally, you can update the system's certificate store by running the update-ca-certificates command. This command scans the /etc/ssl/certs directory for certificate files and updates the certificate store accordingly. It's a good practice to run this command after adding or removing certificates:
```
sudo update-ca-certificates
```

Adding the CA certificate to the /etc/ssl/certs directory and updating the certificate store ensures that the certificate is trusted by the system, allowing for secure connections to servers using this certificate.

Task 3: Add the CN to the JDBC driver properties

When the certificates are in place, add the CN to the JDBC driver properties. To update the properties:

Open the following file in a text editor:

DPA-Install-Dir/iwc/tomcat/ignite_config/idc/repo.properties file
Edit the repo.jdbcDriverProperties setting.

Add the following properties:

CN=MyCA;serverSslCert=/path/ca.pem;trustServerCertificate=true;useSSL=true

Where:

CN=MyCA: Indicates the Common Name (CN) attribute of the SSL certificate. It usually refers to the name or identifier associated with the certificate.

Optionally, you can enter:

-subj "/CN=MyCA"

The -subj option can be use with used to specify the subject (distinguished name) for the generated X.509 certificate. The subject represents the entity to which the certificate belongs, typically identified by various attributes such as Common Name (CN), Organization (O), Organizational Unit (OU), or Country (C).
serverSslCert=/path/ca.pem: Specifies the path to the CA certificate (ca.pem) file on your local system. This certificate is used to verify the server's SSL certificate during the SSL handshake.
trustServerCertificate=false: Instructs the client to not automatically trust the server's SSL certificate without verification against the CA certificate provided. By setting it to false, you enforce strict certificate validation.
useSSL=true: Indicates that SSL encryption should be used for the JDBC connection. This ensures that data exchanged between the client and the MySQL server is encrypted.

Example:

CN=MyCA;serverSslCert=/Users/zeeshan.imdad/Downloads/mysql-95-cert/ca.pem;trustServerCertificate=true;useSSL=true

Search SolarWinds Support