Ssl connection error protocol version mismatch

Setting up MySQL SSL and Secure Connections

There are different articles on how to setup MySQL with SSL but it’s sometimes difficult to end up with a good simple one. Usually, setting up MySQL SSL is not really a smooth process due to such factors like “it’s not your day”, something is broken apparently or the documentation lies 🙂 I am going to provide the brief instructions on how to setup MySQL with SSL, SSL replication and how to establish secure connections from the console and scripts showing the working examples.

Quick links:

Setup SSL on MySQL

1. Generate SSL certificates according to the example 1. Use the different Common Name for server and client certificates.

2. For the reference, I store the generated files under /etc/mysql-ssl/

3. Add the following lines to /etc/my.cnf under [mysqld] section:

# SSL
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem

4. Restart MySQL.

5. Create an user to permit only SSL-encrypted connection:

GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY ‘pass’ REQUIRE SSL;

Establish secure connection from console

1. If the client is on a different node, copy /etc/mysql-ssl/ from the server to that node.

2. Add the following lines to /etc/my.cnf under [client]:

# SSL
ssl-cert=/etc/mysql-ssl/client-cert.pem
ssl-key=/etc/mysql-ssl/client-key.pem

3. Test a secure connection:

]# mysql -u ssluser -p -sss -e ‘s’ | grep SSL
SSL: Cipher in use is DHE-RSA-AES256-SHA

Setup SSL replication

1. Establish a secure connection from the console on slave like described above, to make sure SSL works fine.

2. On Master add “REQUIRE SSL” to the replication user:

GRANT REPLICATION SLAVE ON *.* to ‘repl’@’%’ REQUIRE SSL;

3. Change master options and restart slave:

STOP SLAVE;
CHANGE MASTER MASTER_SSL=1,
MASTER_SSL_CA=’/etc/mysql-ssl/ca-cert.pem’,
MASTER_SSL_CERT=’/etc/mysql-ssl/client-cert.pem’,
MASTER_SSL_KEY=’/etc/mysql-ssl/client-key.pem’;

SHOW SLAVE STATUSG
START SLAVE;
SHOW SLAVE STATUSG

Establish secure connection from PHP

1. Install php and php-mysql packages. I use the version >=5.3.3, otherwise, it may not work.

2. Create the script:

]# cat mysqli-ssl.php
Ssl_cipher
[1] => DHE-RSA-AES256-SHA
)

Establish secure connection from Python

1. Install MySQL-python package.

2. Create the script:

]# cat mysql-ssl.py
#!/usr/bin/env python
import MySQLdb
ssl = <‘cert’: ‘/etc/mysql-ssl/client-cert.pem’, ‘key’: ‘/etc/mysql-ssl/client-key.pem’>
conn = MySQLdb.connect(host=’127.0.0.1′, user=’ssluser’, passwd=’pass’, ssl=ssl)
cursor = conn.cursor()
cursor.execute(‘SHOW STATUS like “Ssl_cipher”‘)
print cursor.fetchone()

]# python mysql-ssl.py
(‘Ssl_cipher’, ‘DHE-RSA-AES256-SHA’)

Notes

Alternative local SSL connection setup
If you connect locally to the server enabled for SSL you can also establish a secure connection this way:
1. Create ca.pem:

cd /etc/mysql-ssl/
cat server-cert.pem client-cert.pem > ca.pem

2. Have only the following ssl- lines in /etc/my.cnf under [client]:

# SSL
ssl-ca=/etc/mysql-ssl/ca.pem

Error with “ssl-ca” on local connections
If you left the line “ssl-ca=/etc/mysql-ssl/ca-cert.pem” under [client] section in /etc/my.cnf on the server enabled for SSL and try to establish local SSL connection, you will get “ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)”.

Discrepancy in documentation
http://dev.mysql.com/doc/refman/5.5/en/using-ssl-connections.html says “A client can connect securely like this: shell> mysql –ssl-ca=ca-cert.pem” which does not work with “REQUIRE SSL”. You still have to supply the client cert and key for any or a combined client+server cert for a local secure connection.

Lead Platform Engineer at Percona. Developing monitoring tools, automated scripts and leading Percona Monitoring and Management project.

Источник

Enabling SSL in MySQL

I’m running Ubuntu Server 12.04, and I want to enable SSL connections to MySQL.

I’ve generated the following keys/certs files with OpenSSL:

I stored these at /etc/mysql , then added added the following lines to /etc/mysql/my.cnf :

Next, I restarted the server with sudo service restart mysql .

However, this doesn’t seem to enable SSL. Within a mysql session:

Any ideas what I’m missing? Thanks

7 Answers 7

Ubuntu 12.04 comes with a OpenSSL 1.0.1, which has somewhat different defaults than the older OpenSSL 0.9.8 version.

Among other things, if you’re using openssl req -newkey rsa:2048 to generate an RSA key, you’ll end up with a key in a format called PKCS #8. Represented in the PEM format, these keys have the more generic ——BEGIN PRIVATE KEY—— header, which doesn’t tell you what kind (RSA, DSA, EC) key it is.

Previously, with OpenSSL 0.9.8, keys were always in a format called PKCS #1, which represented as PEM, had the header ——BEGIN RSA PRIVATE KEY—— .

Because of this you cannot simply change the header and footer from:

It’s not the same thing and it won’t work. Instead you need to convert the key to the old format using openssl rsa . Like this:

MySQL (v5.5.35) on Ubuntu 12.04 is using an SSL implementation called yaSSL (v2.2.2). It expect keys to be in the PKCS #1 format and doesn’t support the PKCS #8 format used by OpenSSL 1.0 and newer. If you simply change the header and footer, as suggested by other posts in this thread, MySQL/yaSSL won’t complain, but you’ll be unable to connect and instead end up with an error like this:

Ubuntu 14.04 comes with OpenSSL 1.0.1f and new settings. Among other things, it will generate certificates with SHA256 digests instead of SHA1, which was used in earlier versions. Incidentially, the yaSSL version bundled with MySQL doesn’t support this either.

If you’re generating certificates for use with MySQL, remember to make sure the RSA keys are converted to the traditional PKCS #1 PEM format and that certificates are using SHA1 digests.

Here’s an example of how to generate your own CA, a server certificate and a client certificate.

Источник

How can I resolve an ERROR 2026 SSL connection error when connecting to an Amazon RDS for MySQL or Aurora DB instance?

Last updated: 2021-02-10

I’m trying to connect to my Amazon Relational Database Service (Amazon RDS) DB instance or cluster using Secure Sockets Layer (SSL). I received the following error:

«ERROR 2026 (HY000): SSL connection error»

How can I resolve ERROR 2026 for Amazon RDS for MySQL, Amazon Aurora for MySQL, or Amazon Aurora Serverless?

Short description

There are three different types of error messages for ERROR 2026:

• ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure
• ERROR 2026 (HY000): SSL connection error: Server doesn’t support SSL
• ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation

See the following troubleshooting steps for each error message.

Resolution

ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure

To troubleshoot this error, first validate whether you’re using the cluster endpoint or the DB instance endpoint. To learn how Amazon RDS supports SSL, see Using SSL with a MySQL DB instance or Using SSL with Aurora MySQL DB clusters.

If you use a client that supports Subject Alternative Names (SAN), then you can use only the cluster endpoint. If your client doesn’t support SAN, you must use the endpoint of the primary DB instance.

Note: The default MySQL command line client doesn’t support SAN.

If you receive this error when trying to connect to the cluster endpoint, try connecting to the endpoint of the primary DB instance in the connection string. For example, you can connect to the cluster endpoint. In the following example, the cluster endpoint is abcdefg-clust.cluster-xxxx.us-east-1.rds.amazonaws.com. The DB instance endpoint is abcdefg-inst.xxxx.us-east-1.rds.amazonaws.com.

Connect using the cluster endpoint

Connect using the DB instance endpoint

ERROR 2026 (HY000): SSL connection error: Server doesn’t support SSL

You can receive this error if the server or engine version that you use doesn’t support SSL. To resolve this error, migrate to an engine that supports SSL connections.

ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed or ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation

You can receive this error if the certificate identifier (certificate file name) isn’t correct. You can also receive this error if the certificate identifier isn’t supported by the MySQL client, for example with Aurora Serverless. If you use Aurora Serverless clusters and you use the MySQL client to connect to Aurora Serverless, then you must use the MySQL 8.0-compatible MySQL commands.

Be sure to use the correct certificate identifier name and the correct path to the certificate to connect successfully. Before connecting, confirm that you have downloaded the correct certificate. For more information, see Using SSL to encrypt a connection to a DB instance.

The root certificate file is in the Downloads directory in an Amazon Elastic Compute Cloud (Amazon EC2) instance. In the following example, you enter the incorrect path, which results in ERROR 2026:

Note: This example uses the connection string in the home directory, but the root certificate is in the Downloads directory.

In the following example, you use the path to the root certificate to connect successfully:

You can also receive this error if you don’t have permissions to the directory that the certificate is stored in. Be sure that the certificate is in a directory that you have permissions to access. See the following examples to connect with and without permissions:

Источник

SSL connection error: protocol version mismatch

Bug Description

Percona Server (MySQL) version Ver 5.5.23-55-log for Linux on x86_64 (Percona Server (GPL), Release rel25.3, Revision 240)

This is added to my.cnf
[mysqld]
ssl-ca = /root/newcerts/ ca-cert. pem
ssl-cipher = DHE-RSA- AES256- SHA:AES128- SHA

MySQL show SSL is working enabled.
mysql> show global variables like ‘%Ssl%’;
+—— ——- —+—- ——- ——- ——- ——+
| Variable_name | Value |
+—— ——- —+—- ——- ——- ——- ——+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /root/newcerts/ ca-cert. pem |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | DHE-RSA- AES256- SHA:AES128- SHA |
| ssl_key | |
+—— ——- —+—- ——- ——- ——- ——+

No mater how I connect (-h) or who I connect as (-u) I get the same message when trying to use sll.

mysql —ssl-ca= /root/newcerts/ ca-cert. pem
ERROR 2026 (HY000): SSL connection error: protocol version mismatch

To make this really complete. Here is the cert.

——BEGIN CERTIFICATE——
MIIDyzCCArOgAwI BAgIJAJuwzOnesb ogMA0GCSqGSIb3D QEBBQUAMHwxCzAJ BgNV
BAYTAlVTMREwDwY DVQQIDAhPa2xhaG 9tYTEWMBQGA1UEB wwNT2tsYWhvbWEg Q2l0
eTEUMBIGA1UECgw LZ3Jlbm5hbi5jb2 0xCzAJBgNVBAMMA mRiMR8wHQYJKoZI hvcN
AQkBFhBtYXJrQGd yZW5uYW4uY29tMB 4XDTEyMDUzMTE1N Tk0OVoXDTE1MDIy NTE1
NTk0OVowfDELMAk GA1UEBhMCVVMxET APBgNVBAgMCE9rb GFob21hMRYwFAYD VQQH
DA1Pa2xhaG9tYSB DaXR5MRQwEgYDVQ QKDAtncmVubmFuL mNvbTELMAkGA1UE AwwC
ZGIxHzAdBgkqhki G9w0BCQEWEG1hcm tAZ3Jlbm5hbi5jb 20wggEiMA0GCSqG SIb3
DQEBAQUAA4IBDwA wggEKAoIBAQDJ0Q yn5JuoBJt5b6+ I1BNDBKdb4rMolJ B5x0Pv
ZmOC0B5Q4dluYz4 1KHhgQf/ +zkudvnH1P8zFMP S908RZAC09/ uZpGHbfGq/ ZYBd3
qcnkFVePAPoyChG Qc9OW8vv5jvNVCp sV/6gfL27+ RkuT6axFR5PNzpP AuGGJ5QyY
2O9k0/zYXbiAj0a QXaLe3UhUI0JGC3 4Tc8kjCw3Ml1Vqq 8jmeSgT+ mlw1T5c1hfD
1+mD8E/ ErPIbbqU5yuFevF +ENxZste2m2Xa92 V3Oe3A5gM53+ xsJ/171uNiS6Fuy
Labbdgp4y1asun8 Zl64KmBFDw/ doGf8pggwagRO7R EdMn8YPAgMBAAGj UDBOMB0G
A1UdDgQWBBRHYMk qlq9Qp02WU8oi6m CMu/Ut8zAfBgNVH SMEGDAWgBRHYMkq lq9Q
p02WU8oi6mCMu/ Ut8zAMBgNVHRMEB TADAQH/ MA0GCSqGSIb3DQE BBQUAA4IBAQB7
MZivAO6l2XhPvz6 hE6XZgmZg9LVQU0 gn7AG6QZWaPFRuM 9uRqY2E2ymWBKn+ n0vy
m0cY03MewFTbDBN OdlfRdUJ7oiKZux L9a/eEcipME7CBI NKZzcYS3JCCqqjr E04v
FUZhc3jHBhN0y0l MT9whC+ sByDmmtO2c6E99B murgp4KHSvPRUSc mV/S/tEoHxuO
tAeyZ4n4431Rt03 TTtTOzF3tPj1VjT IBgENmLMbgjevcD AQ6kPeHTMkL9u2m Kpxd
7o3HOus5/ ArbiF+Q7RSFK6sR eTqG4TDyuEGK3Mf D8Ugqfpcb2I9Iwf 5pf0M0BSnY
F4K47ha0tZkWX1e GoI78
——END CERTIFICATE——

Источник

How To Configure SSL/TLS for MySQL on Ubuntu 16.04

Not using Ubuntu 16.04 ? Choose a different version or distribution.

Introduction

MySQL is the most popular open-source relational database management system in the world. Modern package managers have reduced some of the friction to getting MySQL up and running, but there is still some configuration that should be done after installation. One of the most important areas to spend some extra time on is security.

By default, MySQL is configured to only accept local connections. If you need to allow remote connections, it is important to do so securely. In this guide, we will demonstrate how to configure MySQL on Ubuntu 16.04 to accept remote connections with SSL/TLS encryption.

Prerequisites

To follow along with this guide, you will need two Ubuntu 16.04 servers. We will use one as the MySQL server and the other as the client. Create a non-root user with sudo privileges on each of these servers. Follow our Ubuntu 16.04 initial server setup guide to get your server into the appropriate initial state.

On the first machine, you should have the MySQL server installed and configured. Follow our MySQL installation guide for Ubuntu 16.04 to install and configure the software.

On the second machine, install the MySQL client package. You can update the apt package index and install the necessary software by typing:

When your server and client are ready, continue below.

Check the Current SSL/TLS Status

Before we begin, we can check the current status of SSL/TLS on our MySQL server instance.

Log into a MySQL session using the root MySQL user. We’ll use -h to specify the IPv4 local loopback interface in order to force the client to connect with TCP instead of using the local socket file. This will allow us to check the SSL status for TCP connections:

You will be prompted for the MySQL root password that you selected during the installation process. Afterward, you will be dropped into an interactive MySQL session.

Show the state of the SSL/TLS variables by typing:

The have_openssl and have_ssl variables are both marked as DISABLED . This means that SSL functionality has been compiled into the server, but that it is not yet enabled.

Check the status of our current connection to confirm:

As the above output indicates, SSL is not currently in use for our connection, even though we are connected over TCP.

Close the current MySQL session when you are finished:

Now we can start configuring MySQL for SSL to secure our connections.

Generate SSL/TLS Certificates and Keys

To enable SSL connections to MySQL, we first need to generate the appropriate certificate and key files. A utility called mysql_ssl_rsa_setup is provided with MySQL 5.7 and above to simplify this process. Ubuntu 16.04 has a compatible version of MySQL, so we can use this command to generate the necessary files.

The files will be created in MySQL’s data directory, located at /var/lib/mysql . We need the MySQL process to be able to read the generated files, so we will pass mysql as the user that should own the generated files:

The generation will produce output that looks something like this:

Check the generated files by typing:

The last column shows the generated filenames. The central columns that show “mysql” indicate that the generated files have the correct user and group ownership.

These files are the key and certificate pairs for the certificate authority (starting with “ca”), the MySQL server process (starting with “server”), and for MySQL clients (starting with “client”). Additionally, the private_key.pem and public_key.pem files are used by MySQL to securely transfer password when not using SSL.

Enable SSL Connections on the MySQL Server

Modern MySQL versions will look for the appropriate certificate files within the MySQL data directory when the server starts. Because of this, we don’t actually need to modify the MySQL configuration to enable SSL.

We can just restart the MySQL service instead:

After restarting, open up a new MySQL session using the same command as before. The MySQL client will automatically attempt to connect using SSL if it is supported by the server:

Let’s take a look at the same information we requested last time. Check the values of the SSL related variables:

The have_openssl and have_ssl variables read “YES” instead of “DISABLED” this time. Furthermore, the ssl_ca , ssl_cert , and ssl_key variables have been populated with the names of the relevant certificates that we generated.

Next, check the connection details again:

This time, the specific SSL cipher is displayed, indicating that SSL is being used to secure our connection.

Exit back out to the shell:

Our server is now capable of using encryption, but some additional configuration is required to allow remote access and mandate use of secure connections.

Configuring Secure Connections for Remote Clients

Now that we have SSL available on the server, we can begin configuring secure remote access. To do this, we need to:

• Require SSL for remote connections
• Bind to a public interface
• Create MySQL user for remote connections
• Adjust our firewall rules to allow external connections

Configure Remote Access with Mandatory SSL

Currently, the MySQL server is configured to accept SSL connections from clients. However, it will still allow unencrypted connections if requested by the client.

We can fix this by turning on the require_secure_transport option. This requires all connections to be made either with SSL or with a local Unix socket. Since Unix sockets are only accessible from within the server itself, the only connection option open to remote users will be with SSL.

To enable this setting, open the /etc/mysql/my.cnf file in your text editor:

Inside, there will be two !includedir directives used to source additional configuration files. We will need to put our own configuration beneath these lines so that they override any conflicting settings.

Start by creating a [mysqld] section to target the MySQL server process. Under that section header, set require_secure_transport to ON :

That line is the only setting required to enforce secure connections.

By default MySQL is configured to only listen for connections originating on the local computer. To configure it to listen for remote connections, we can set the bind-address to a different interface.

To allow MySQL to accept connections on any of its interfaces, we can set bind-address to “0.0.0.0”:

Save and close the file when you are finished.

Next, restart MySQL to apply the new settings:

Verify that MySQL is listening on “0.0.0.0” instead of “127.0.0.1” by typing:

The “0.0.0.0” in the above output indicates that MySQL is listening for connections on all available interfaces.

Next, we need to allow MySQL connections through our firewall. Create an exception by typing:

Remote connection attempts should now be able to reach our MySQL server.

Configure a Remote MySQL User

The MySQL server is now listening for remote connections, but we currently don’t have any users configured that can connect from an outside computer.

Log into MySQL as the root user to get started:

Inside, you can create a new remote user using the CREATE USER command. We will use our client machine’s IP address in the host portion of the user specification to restrict connections to that machine.

For some redundancy in case the require_secure_transport option is turned off in the future, we’ll also specify during account creation that this user requires SSL by including the REQUIRE SSL clause:

Next, grant the new user permissions on the databases or tables they should have access to. To demonstrate, we’ll create an example database and give our new user ownership:

Next, flush the privileges to apply those settings immediately:

Exit back out to the shell when you are done:

Our server is set up to allow connections to our remote user.

Testing Remote Connections

On the MySQL client machine, test to make sure you can connect to the server successfully. Use the -u option to specify the remote user and the -h option to specify the MySQL server’s IP address:

After specifying the password, you will be logged in to the remote server.

Check to make sure that your connection is secure:

Exit back out to the shell:

Next, attempt to connect insecurely:

After being prompted for your password, your connection should be refused:

This is what we were working towards. It shows that SSL connections are permitted, while unencrypted connections are refused.

At this point, our MySQL server has been configured to accept remote connections securely. You can stop here if this satisfies your security requirements, but there are some additional pieces that we can put into place to increase our security and trust further.

Configuring Validation for MySQL Connections (Optional)

Currently, our MySQL server is configured with an SSL certificate signed by a locally generated certificate authority (CA). The server’s certificate and key pair are enough to provide encryption for incoming connections.

However, we aren’t currently leveraging the trust relationship that a certificate authority can provide. By distributing the CA certificate to clients, as well as the client certificate and key, both parties can provide proof that their certificates were signed by a mutually trusted certificate authority. This can help prevent spoofed connections to malicious servers.

In order to implement this extra, optional safeguard, we will need to:

• Transfer the appropriate SSL files to the client machine
• Create a client configuration file
• Alter our remote user to require a trusted certificate

Transfer the Client Certificates to the Client Machine

To start, we need to grab the MySQL CA and client certificate files from the MySQL server and place them on the MySQL client.

Begin by making a directory on the MySQL client in the home directory of the user you will use to connect. Call this client-ssl :

Since the certificate key is sensitive, we should lock down access to this directory so that only the current user can access it:

Now, we can copy the certificate information to the new directory.

On the MySQL server machine, display the contents of the CA certificate by typing:

Copy the entire output, including the BEGIN CERTIFICATE and END CERTIFICATE lines to your clipboard.

On the MySQL client, create a file with the same name inside the new directory:

Inside, paste the copied certificate contents from your clipboard. Save and close the file when you are finished.

Next, display the client certificate on the MySQL server:

Again, copy the contents to your clipboard. Remember to include the first and last line.

Open a file with the same name on the MySQL client within the client-ssl directory:

Paste the contents from your clipboard. Save and close the file.

Finally, display the contents of the client key file on the MySQL server:

Copy the displayed contents, including the first and last line, to your clipboard.

On the MySQL client, open a file with the same name in the client-ssl directory:

Paste the contents from your clipboard. Save and close the file.

The client machine should now have all of the credentials required to access the MySQL server. Next, we need to alter our remote user.

Require a Certificate from a Trusted CA for the Remote User

Currently, the MySQL client has the files available to present its certificate to the server when connecting. However, the server is still not set up to require the client certificate from a trusted CA.

To change this, log into the MySQL root account again on the MySQL server:

Next, we need to change the requirements for our remote user. Instead of the REQUIRE SSL clause, we need to apply the REQUIRE X509 clause. This implies all of the security provided by the previous requirement, but additionally requires the connecting client to present a certificate signed by a certificate authority that the MySQL server trusts.

To adjust the user requirements, use the ALTER USER command:

Flush the changes to ensure that they are applied immediately:

Exit back out to the shell when you are finished:

Next, we can test to make sure we can still connect.

Test Certificate Validation When Connecting

Now is a good time to check whether we can validate both parties when we connect.

On the MySQL client, first try to connect without providing the client certificates:

Without providing the client certificate, the server rejects the connection.

Now, connect while using the —ssl-ca , —ssl-cert , and —ssl-key options to point to the relevant files within the

You should be logged in successfully. Log back out to regain access to your shell session:

Now that we’ve confirmed access to the server, we can implement a small usability improvement.

Create a MySQL Client Configuration File

To avoid having to specify the certificate files each time you connect, we can create a simple MySQL client configuration file.

Inside your home directory on the MySQL client machine, create a hidden file called

At the top of the file, create a section called [client] . Underneath, we can set the ssl-ca , ssl-cert , and ssl-key options to point to the files we copied over from the server. It should look like this:

The ssl-ca option tells the client to verify that the certificate presented by the MySQL server is signed by the certificate authority we pointed to. This allows the client to trust that it is connecting to a trusted MySQL server.

The ssl-cert and ssl-key options point to the files required to prove to the MySQL server that it too has a certificate that has been signed by the same certificate authority. We need this if we want the MySQL server to verify that the client was trusted by the CA as well.

Save and close the file when you are finished.

Now, you can connect to the MySQL server without adding the —ssl-ca , —ssl-cert , and —ssl-key options on the command line:

Your client and server should now each be presenting certificates when negotiating the connection. Each party is configured to verify the remote certificate against the CA certificate it has locally.

Conclusion

Your MySQL server should now be configured to require secured connections for remote clients. Additionally, if you followed the steps to validate connections using the certificate authority, some level of trust is established by both sides that the remote party is legitimate.

If you’ve enjoyed this tutorial and our broader community, consider checking out our DigitalOcean products which can also help you achieve your development goals.

Источник

For my own sanity, I’m writing down the steps I took to get SSL working between 2 servers today. I ran into some frustration following the step-by-step instructions on both mysql.com as well as on 1 or two other websites on the web. To add to the fun, it seems sometimes I would get different, no descriptive, errors such as:

• ERROR 2026 (HY000): SSL connection error (no additional details)
• ERROR 2026 (HY000): SSL connection error: Unable to get certificate
• ERROR 2026 (HY000): SSL connection error: protocol version mismatch

So here is what I did, step by step, with multiple test points…

On The MYSQL SERVER  that I wanted to connect to, I logged in and created a self signing Certificate Authority (CA) and then used that to sign a key for my mysql server. The commands looked like this:

openssl genrsa 1024 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

openssl req -newkey rsa:1024 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

You’ll note that I used 1024 for my key instead of 2048. While 2048 would be more secure, and is what is provided in the example here, it causes my version of OpenSSL to generate in a slightly weird format that the mysql client doesn’t seem to like in some cases. A number of example online said there was a simple work around involving adding “RSA” to the header and footer of the keys, but no matter what I seemed to do, this never worked for me… so I just lowered the key strength to 1024 to get it to generate it the old way. There was also a suggestion to merge the server and client certs into one “CA”… but I didn’t like that either… in fact… I didn’t want a Client cert if if I could avoid it… as I wasn’t authenticating against it… I just wanted the SSL tunnel.

Once I had my certs created, I added these 3 lines to my.cnf. In theory I could have put them in the startup command for the server, but this was the better long term solution:

ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Note: when I created all my certs above, I was in the folder /etc/mysql/ (in case you didn’t guess that already).

At this point, I restarted the mysql server and ran the following query on it:

show variables like “%ssl%”;

The server came back and would say that it “have_ssl” and “have_openssl” along with the paths to the certs I just loaded in. This was a good sign.

I think modified one of my users to only allow connections using SSL:

grant usage on *.* to example_user require ssl;

I than began testing from client devices. The first stop was the mysql workbench for windows. I loaded it up and tried to log in, and on the first try it failed (this I took to be a good sign, as I hadn’t told it anything about the SSL). I then grabbed the ca-cert.pem file I created on my server, and brought it over to my client box. I then told mysql workbench to use this file for the CA. You’ll note I DID NOT specify a client key or certificate for this. I then tried to connect, and wha-la, it let me in.

To confirm I was in fact transmitting over SSL at this point, I ran this query:

show status like ‘Ssl_cipher’;

and then insured it came back with a value (if it’s blank, your NOT connected over SSL)

I then moved on to my second client, which a LAMP setup. I tried connecting using phpmyadmin, and of course, it failed again as I had told it nothing about the certificate authority. So again, I copied the file over, this time adding the following to my my.cnf:

[client]
ssl-ca=/path/to/ca-cert.pem

I first tested with my mysql client on the machine and it worked great. I did run into a little trouble with phpmyadmin, but I found the connect lines in my config file and basically added this one line before the connection was made, but after the init:

mysqli_ssl_set ($link , NULL , NULL , “/path/to/ca-cert.pem” ,NULL , NULL );

I think this may not have been needed if my version of phpmyadmin had been newer… but not sure. (I was in 3.4.5 at the time, the documentation I was reading would have been for 4.2.7).

Regardless, when I signed off for the night, all my mysql connections that I cared about between my older “clients” and my new mysql server were running over SSL. Tomorrow, I hope to setup some replication between servers over SSL. This may require that I create a client certificate, but until then, I’m happy to not have needed one.