MySQL client fails to connect, seems to force SSL

[Nicolay Giraldo]

Description: Ubuntu Focal Fossa (development branch)
Release: 20.04

mysql Ver 8.0.19-0ubuntu4 for Linux on x86_64 ((Ubuntu))

I get this error from command line when trying to connect to a remote server:

# ERR: SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol (MySQL error code: 2026, SQLState: HY000 )

I get the same error from C++ scripts using MySQL Connector++.

Only way mysql command line works is to add --ssl-mode=DISABLED

Server version: 5.6.10-log MySQL Community Server (GPL)

The connection goes through OpenVPN.

Alternative solution: Using instead mariadb-client works without problems.

Older versions of Ubuntu don't present this issue. I tested 16.04, 18.04, 19.10, and I can connect using mysql in all of them.

Fails in 20.04 in a virtual machine too.

ProblemType: Bug
DistroRelease: Ubuntu 20.04
Package: mysql-client 8.0.19-0ubuntu4
ProcVersionSignature: Ubuntu 5.4.0-21.25-generic 5.4.27
Uname: Linux 5.4.0-21-generic x86_64
ApportVersion: 2.20.11-0ubuntu26
Architecture: amd64
CasperMD5CheckResult: skip
CurrentDesktop: ubuntu:GNOME
Date: Mon Apr 13 13:42:25 2020
InstallationDate: Installed on 2020-04-12 (0 days ago)
InstallationMedia: Ubuntu 20.04 LTS "Focal Fossa" - Beta amd64 (20200409)
PackageArchitecture: all
SourcePackage: mysql-8.0
UpgradeStatus: No upgrade log present (probably fresh install)

[Rafael David Tinoco (rafaeldtinoco)]

Hello Nicolay,

Thank you for taking the time to file a bug report.

Could you please describe your problem with more detailed information ? mysql-server-8.0 default installation in 20.04 does NOT enable SSL by default, in the server, nor in the client:

"""
mysql> status;
--------------
mysql Ver 8.0.19-0ubuntu5 for Linux on x86_64 ((Ubuntu))

Connection id: 9
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: /home/rafaeldtinoco/.vim/plugged/vimpager/vimpager
Using outfile: ''
Using delimiter: ;
Server version: 8.0.19-0ubuntu5 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 1 min 54 sec
"""

The fact that your mysql client is trying to reach the server using SSL by default gives the impression that my.cnf (or any other included configuration file) is configuring that behavior. Could you please provide, together with your report, all your mysql configuration files ?

Since there is not enough information in your report to begin triage or to
differentiate between a local configuration problem and a bug in Ubuntu, I
am marking this bug as "Incomplete". We would be grateful if you would:
provide a more complete description of the problem, explain why you
believe this is a bug in Ubuntu rather than a problem specific to your
system, and then change the bug status back to "New".

For local configuration issues, you can find assistance here:
http://www.ubuntu.com/support/community

[Nicolay Giraldo (nicolay-g)]

I am not connecting to Ubuntu 20.04 mysql-server locally, in fact, I don't have mysql-server installed in the machine.

I am connecting to a remote server that reports this version:

Server version: 5.6.10-log MySQL Community Server (GPL)

It is a server running Aurora AWS. I can't update that server to latest MySQL version, that is something only AWS can do.

The server has some test usernames that require SSL, but my username doesn't require it.

My mysql-client configuration files are all default.

No previous Ubuntu version has this issue, I tested several versions running on virtual machines.

If I install mariadb-client it also can connect without issues.

[Christian Ehrhardt  (paelzer)]

@Lars/Robie - is there some config that one can apply so the client to avoid SSL on servers that won't provide it?

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Christian Ehrhardt  (paelzer)]

I found another such bug on triage, seems to be a more common issue maybe?
I'll bump priority but leave the SME to Larss/Robie.

[Lars Tangvald (lars-tangvald)]

I'll look into this and get back to you!

[Lars Tangvald (lars-tangvald)]

I can't reproduce this with a regular (upstream) 5.6 server; Can run a 5.6 server on a debian 9 host and connect to it fine from a 20.04 running 8.0.

What is the auth type of the user in the mysql.user table?
Does the same thing happen if you create a new user with just a normal password auth?

I don't really know what the state of the AWS server would be (upstream is on 5.6.47, but the versions aren't directly comparable I think).

[Robie Basak (racb)]

To confirm, the MySQL client in Focal *does* work if you use "--ssl-mode=DISABLED"?

If so, this seems appropriate to me since otherwise there would be a downgrade attack. I'm not sure this is a bug in MySQL or Ubuntu's MySQL packaging then - it's a step up in security which is the sort of thing users can expect from new Ubuntu releases.

It would be nice to have a configuration item for users to turn this behaviour off. If that exists it would be useful if somebody could please document that in this bug. If it doesn't exist then someone could file a bug upstream to request making it possible, but I don't think it can be considered a bug in Ubuntu except that we might be able to make the feature available if upstream implements it.

Thank you for bringing this to our attention though - I think it would be a useful thing to add to the release notes.

[Lars Tangvald (lars-tangvald)]

ssl-mode should default to a mode that tries to autodetect server support, with automatic fallback to disabled(PREFERRED, per https://dev.mysql.com/doc/refman/8.0/en/connection-options.html#option_general_ssl-mode), but I'm not sure if that works properly with the Aurora server.

Have you looked at https://aws.amazon.com/premiumsupport/knowledge-center/rds-error-2026-ssl-connection/?

The error message you get doesn't quite match what is mentioned there or what MySQL would report if the problem was that the client tried to enforce ssl, but I did find https://github.com/microsoft/msphpsql/issues/1023 which also mentions it working in 19.10 but no longer in 20.04. Maybe something related to openssl 1.1.1f?

[Martin Frost (martin-frost)]

I have the same issue, and for me it *does* work if I use `--ssl-mode=DISABLED` to connect.

This is using the mysql-client installed through apt in Ubuntu 20.04 beta (mysql-client 8.0.19-0ubuntu4), and connecting to a MySQL Server version 5.7.21 running in docker on the same machine.

I get the same problem when trying to connect using the mysqlclient-python library in a django application I'm working on.

The mariadb-client version of the `mysql` binary can connect to the same database without using `--ssl-mode=DISABLED`.

If I replace mysql-client and libmysqlclient-dev with mariadb-client and libmariadbclient-dev and then reinstall the python mysql library, that works as it is supposed to.

[Lars Tangvald (lars-tangvald)]

Mariadb client is built with gnutls, I believe.

I was able to reproduce this now. If you install an older mysql server from before 5.6 also used openssl (5.6.44 in my case), then enable ssl on the server, you get the error reported when trying to connect to it from Ubuntu 20.04 8.0 client. The user you try to connect to doesn't need to have any special settings on it.

Upgrading to 5.6.47 fixed the issue for me, so I'm guessing this is because 5.6.44 was built with yassl, which is old and unsupported (again, I don't know exactly what Amazon does, so needs verifying).
The cause might be that yassl only supports TSL 1.1, while Ubuntu with the update to OpenSSL 1.1.1f has started enforcing 1.2 as the minimum version (if I'm reading the changelog correctly).

I don't think this is a bug. Only fix would be setting ssl-mode to DISABLED in the client config or getting the server updated (which as you said is outside your control) to one that isn't built with yaSSL.

[Lars Tangvald (lars-tangvald)]

This also fits with seeing the same problem for a 5.7.21 server, which was also built with yaSSL.
Could you test updating your docker image server to the newest 5.7 and see if that fixes it?

[Nicolay Giraldo (nicolay-g)]

I found a workaround for C++ when I don't want to use certificates:

 sql::Driver *driver;
 sql::ConnectOptionsMap connection_properties;
 sql::Connection *con;

 connection_properties["hostName"] = host;
 connection_properties["userName"] = username;
 connection_properties["password"] = password;
 connection_properties["schema"] = dbname;
 connection_properties["port"] = port;
 connection_properties["OPT_RECONNECT"] = true;
 connection_properties["CLIENT_COMPRESS"] = true;
 connection_properties["OPT_SSL_MODE"] = sql::SSL_MODE_DISABLED;

 con = driver->connect(connection_properties);

[Nicolay Giraldo (nicolay-g)]

In case I do actually want to use certificates and SSL, the error when connecting to Aurora DB is a bit different:

In C++:

# ERR: SQLException in query: [] on line 0
# ERR: SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol (MySQL error code: 2026, SQLState: HY000 )

Using the command line client mysql:

mysql -h servername.rds.amazonaws.com -u username -p --ssl-ca=/home/username/sslcert/rds-combined-ca-bundle.pem --ssl-mode=VERIFY_CA
Enter password:
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

Again, all this works in previous versions of Ubuntu.

I guess if the connection works this is what Robie Basak means by a downgrade attack.

[Nicolay Giraldo (nicolay-g)]

Another workaround not to require adding the --ssl-mode parameter to MySQL command line:

Create a file ~/.my.cnf

With contents:

[client]
ssl_mode=DISABLED

[Lars Tangvald (lars-tangvald)]

The problem with using ssl is that you are using a client (openssl 1.1.1f-based) that will not speak anything older than TLS 1.2, trying to connect to a server that can only speak TLS 1.1.

From 1.1.1f-1ubuntu1 changelog entry (https://launchpad.net/ubuntu/+source/openssl/+changelog):
    - Set OPENSSL_TLS_SECURITY_LEVEL=2 as compiled-in minimum security
      level. Change meaning of SECURITY_LEVEL=2 to prohibit TLS versions
      below 1.2 and update documentation. Previous default of 1, can be set
      by calling SSL_CTX_set_security_level(), SSL_set_security_level() or
      using ':@SECLEVEL=1' CipherString value in openssl.cfg.

[Gordon Lack (gordon-lack)]

So this is a real problem for anyone trying to talk to a server over which they have no control.

I get this error message when running fetchmail to (try to, but fail to) download mail from an IMAP server. In 19.10 it worked.

Making 1.2 the minimum default is OK, but there has to be some simple way that users can tell pre-built *client* packages to use 1.1 if they need to. There is no way i can get fetchmail to call SSL_CTX_set_security_level, and there is no openssl.cfg config file.

[Marcel Lange (sns-mlange)]

I have the same issue with mysqldump when using port forwarding over ssh.

mysqldump: Got error: 2026: SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol when trying to connect

Unfortunately I couldn't figure out a workaround hence `--ssl-mode=DISABLED` is not available for mysqldump.

[Marcel Lange (sns-mlange)]

Sorry `--ssl-mode=DISABLED` works for mysqldump as well.

[Paride Legovini (paride)]

Hi, according to [1]:

- Aurora MySQL 5.6 supports Transport Layer Security (TLS) version 1.0.
- Aurora MySQL 5.7 supports TLS version 1.0, 1.1, and 1.2.

and an upgrade path is documented [2]. I think you can downgrade the minimum OpenSSL requirements for TLS connections as outlined in [3], however I discourage this for the reasons you can easily imagine.

I don't think this qualifies as an MySQL bug: the issue is with OpenSSL, but it isn't to be reported there as the change has been already widely discussed in both Debian and Ubuntu. However I won't set the status of this bug to Invalid before hearing back from you, so Incomplete it is for the moment.

[1] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Security.html
[2] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.html
[3] https://askubuntu.com/questions/1233186/ubuntu-20-04-how-to-set-lower-ssl-security-levelhttps://askubuntu.com/questions/1233186/ubuntu-20-04-how-to-set-lower-ssl-security-level

[Nicolay Giraldo (nicolay-g)]

Even with a compatible MySQL version, the client should not try to force an SSL connection when it is not requested.

For now, --ssl-mode=DISABLED is required, so this is IMO, a confirmed bug.

[mohit (mohitpawar10)]

I am also facing the same issue when exporting data from AWS MySQL instance using the workbench. Data export works for one version of MySQL (5.7.22) but not for the other(5.7.19) version. I am able to view data and export individual table data but not the whole database at once. MySQL workbench version is 8.0 and I am connecting AWS RDS instance using SSL tunneling.

[mohit (mohitpawar10)]

Sorry, I am connecting over SSH not SSL.

[Paride Legovini (paride)]

Hi,

The default is --ssl-mode=PREFERRED, so the SSL connection is not forced, but if the server offers it then the client will use it [1]:

PREFERRED: Establish an encrypted connection if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established.

IIUC things do not work nicely as the client/server SSL versions are not compatible. In this case it is not possible to simply make the client fallback to a non-encrypted connection if SSL is available but the connection fails, as this mechanism would allow for an easy downgrade attack. There has to be some level of enforcement.

I understand the situation is not optimal, but having new security standards requires deprecating the old ones at some point, and when servers and clients are too much out of sync problems arise. I don't see a way out here which does not compromise on security.

[1] https://dev.mysql.com/doc/refman/5.7/en/connection-options.html#option_general_ssl-mode

[Carlos Quiros (qlands)]

Other problems with --ssl-mode=PREFERRE by default happens with third-party libraries like Qt. For example, the current version of QMYSQL does not allow to change the SSL mode because it assumes --ssl-mode=DISABLED by default or to indicate the SSL parameters for an SSL connection. Therefore, Qt application compiled in Ubuntu 20.04 that connects to a 5.7.X server may fail.

[Rafael David Tinoco (rafaeldtinoco)]

@qlands, for those type of cases, can't you do something like:

OPENSSL_CONF=<file> binary

to those binaries linked to libssl 1.1.1f-XXXX ?

Example:

$ openssl ciphers -tls1 -s

<shows nothing>

but...

$ OPENSSL_CONF=~/.openssl.cnf openssl ciphers -tls1 -s

ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:DHE-RSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES128-SHA:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA

if I have:

$ cat ~/.openssl.cnf

openssl_conf = default_conf

[ default_conf ]

ssl_conf = ssl_sect

[ssl_sect]

system_default = ssl_default_sect

[ssl_default_sect]
MinProtocol = TLSv1
MaxProtocol = None
CipherString = DEFAULT:@SECLEVEL=1

[Carlos Quiros (qlands)]

@rafaeldtinoco, I tried that but nothing changed:

Now If I do:

OPENSSL_CONF=~/.openssl.cnf openssl ciphers -tls1 -s

I get:

ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:DHE-RSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES128-SHA:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA

However when I try to connect with MySQL I get:

ERROR 2026 (HY000): SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol

An on the server-side (5.7.27) I get:
"Bad handshake"

Qt applications cannot connect. Same "Bad handshake"

[Rafael David Tinoco (rafaeldtinoco)]

just checking, you're trying to:

OPENSSL_CONF=~/.openssl.cnf <qt application binary>

and getting the error ?

[notromda (mortonda)]

I'm seeing this using a new Ubuntu 20.04 server trying to connect to AWS RDS instance where I have never needed or used SSL before. Using ssl-mode=DISABLED works to get the command line mysql to work, but ansible mysql_db using PyMySQL seems to not honor that.

[Paride Legovini (paride)]

Hi,

Rafael's suggestion (comment 27) may help in your case too, however according to [1] Amazon RDS supports TLS 1.2, so I'd check if anything has to be enabled or upgraded on the server side to make it work.

[1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/infrastructure-security.html

[notromda (mortonda)]

Turns out I was NOT using PyMysql ... Ansible fell back to a different database library that was not honoring the lack of ssl. Using PyMysql, `ssl_mode=DISABLED` works. Also, rails database config needs to add `ssl_mode: disabled` to connect without SSL.

[Marc KIefer (tt-administrator)]

I can confirm this behavior with 20.04 (mysql-client 8.0.20-0ubuntu0.20.04.1) connecting to MySQL-Server (5.7.22-1.el7) on CentOS Linux release 7.5.1804 (Core). The MySQL connection worked before the dist-upgrade from 18.04 to 20.04, the CentOS MySQL-Server was not changed at all.

[Bob Florian (bobflorian)]

I was able to work around this by reinstalling the latest OpenSSL libs from source on Ubuntu 20.04 (which was not ideal - but it worked).

[tom (tvalenti)]

This is still an issue. I use AWS. I upgraded from 18.04 to 20.04 and got not supported errors for SSL using MySQL workbench. I did fix my issue by upgrading my DB from 5.6.21 to 5.6.46. TLS 1.0 is supported on all versions. TLS 1.1 and 1.2 is ONLY supported on 5.6.41 and up. Same applies to the 5.6 Aurora MySQL 1.22.1 and up. MySQL 5.7.21 supports everything and 1.2 is ONLY available on this version. This was a seamless upgrade and highly recommended and to not disable SSL.

[Christian Gutierrez (chesstrian)]

I found this answer very useful: https://askubuntu.com/a/1250807

[Paride Legovini (paride)]

Hi,

As explained by Robie, Lars and me in the comments above this is not a bug, but an intentional bump in the OpenSSL minimal protocol requirements for secure connections. For this reason I marked the mysql-8.0 task as Invalid.

We understand there are cases where a downgrade may be necessary, for those hopefully rare cases the askubuntu answer linked by Christian Gutierrez or the very similar workaround proposed by Rafael in comment #27 are valid, but the real path forward is upgrade the outdated servers.

I'm leaving the Release Notes task open, as this should definitely be better documented.