Mysql workbench ssl connection error ssl is required

Настройка SSL-подключений в приложении для безопасного подключения к базе данных Azure для MySQL

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных Azure для MySQL — отдельный сервер

База данных Azure для MySQL — отдельный сервер находится по пути выхода на пенсию. Настоятельно рекомендуется выполнить обновление до База данных Azure для MySQL — гибкий сервер. Дополнительные сведения о миграции на База данных Azure для MySQL — гибкий сервер см. в статье «Что происходит с одним сервером База данных Azure для MySQL»?

База данных Azure для MySQL поддерживает подключение сервера базы данных Azure для MySQL к клиентским приложениям с помощью протокола SSL (Secure Sockets Layer). Применение SSL-соединений между сервером базы данных и клиентскими приложениями обеспечивает защиту от атак «злоумышленник в середине» за счет шифрования потока данных между сервером и приложением.

Шаг 1. Получение SSL-сертификата

Скачайте сертификат, который необходим для взаимодействия с сервером базы данных Azure для MySQL по протоколу SSL (https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem), и сохраните файл сертификата на локальном диске (в этом руководстве для примера мы использовали папку C:SSL). Для браузеров Microsoft Internet Explorer и Microsoft Edge: по завершении скачивания переименуйте сертификат в BaltimoreCyberTrustRoot.crt.pem.

Сертификаты для серверов в национальных облаках см. по следующим ссылкам: Azure для государственных организаций, Azure для Китая и Azure для Германии.

Шаг 2. Привязка SSL

Сведения о строках подключения для конкретных языков программирования см. в приведенном ниже образце кода.

Подключение к серверу с помощью MySQL Workbench по протоколу SSL

Настройте MySQL Workbench, чтобы безопасно подключаться по протоколу SSL.

В диалоговом окне Setup New Connection (Настройка нового подключения) откройте вкладку SSL.

В поле Use SSL (Использовать SSL) установите значение Require (Требовать).

Введите расположение файла BaltimoreCyberTrustRoot.crt.pem в поле SSL CA File: (Файл центра сертификации SSL-сертификата).

Для существующих подключений вы можете привязать SSL, щелкнув правой кнопкой мыши значок подключения и выбрав пункт изменить. Откройте вкладку SSL и привяжите файл сертификата.

Подключение к серверу с помощью интерфейса командной строки MySQL по протоколу SSL

Кроме того, можно привязать SSL-сертификат при помощи интерфейса командной строки MySQL, выполнив следующие команды.

При использовании интерфейса командной строки MySQL для Windows может появиться ошибка SSL connection error: Certificate signature check failed . В этом случае замените параметр —ssl-mode=REQUIRED —ssl-ca= на —ssl .

Шаг 3. Применение SSL-соединений в Azure

Использование портала Azure

С помощью портала Azure перейдите к серверу базы данных Azure для MySQL и щелкните Безопасность подключения. Воспользуйтесь выключателем для включения или отключения параметра Enforce SSL connection (Применять SSL-соединение), а затем щелкните Сохранить. Для повышения безопасности корпорация Майкрософт рекомендует всегда включать параметр Enforce SSL connection (Применять SSL-соединение).

Использование Azure CLI

Вы также можете включить или отключить параметр ssl-enforcement с помощью Azure CLI, используя значения Enabled и Disabled соответственно.

Шаг 4. Проверка SSL-соединения

Выполните команду mysql status, чтобы проверить наличие подключения к серверу MySQL с помощью протокола SSL.

Убедитесь, что соединение зашифровано, просмотрев выходные данные, в которых должно отображаться следующее: SSL: используемый шифр — AES256 SHA

Пример кода

Чтобы установить безопасное подключение приложения к Базе данных Azure для MySQL по протоколу SSL, изучите приведенные ниже примеры кода.

См. список совместимых драйверов, поддерживаемых службой «база данных Azure для MySQL».

Источник

SSL Connection to MySQL 8.0.16 fails

We updated our MySQL-Server to 8.0.16 and I am not able to establish an SSL connection to the server. I am getting the following error message:

SSL connection error: The message received was unexpected or badly formatted

It is noteworthy that I had the same issue with the MySQL Workbench 6.3 CE, there was also some SSL-handshake-related error when connecting but after updating to the latest version 8.0 CE I can establish the connection again.

HeidiSQL Official release 10.1.0.5464

I investigated this issue with our database administrator and the issue is the TLS version and has most likely nothing to do with the MySQL version.

Formerly we had no specifics about the TLS version. Now the server is configured with tls_version=TLSv1.2 and HeidiSQL is not able to build up a TSL 1.2 connection. After disabling this configuration I was able to connect again. But since our administrator does not want to disable this configuration in production (in fact he would force TLS 1.3 if MySQL would support it without additional software) I am in desparate need of HeidiSQL supporting TLS 1.2.

Please look up in your HeidiSQL application directory, e.g. c:Program FilesHeidiSQL — whether you have libmariadb.dll and libmysql.dll in there, and via rightclick on them, then click on «Details», find the version of both. Please post these version numbers here, so I can verify they were updated by your installation. libmariadb.dll should have v3.0.8, while libmysql.dll should have v5.6.6.0 .

Normally, HeidiSQL uses libmariadb.dll, even when connecting to MySQL. libmysql.dll is just left for backwards compatibility.

In the meantime there is libmariadb.dll v3.0.10, but the changelogs don’t say a word about TLS 1.2 support, and I can’t believe it’s the library which is outdated. There must be some setting, maybe one which I can set via mysql_options()

I found they seem to work on TLS 1.2 support in MariaDB’s connector: https://github.com/MariaDB/mariadb-connector-c

So you could give it a try and delete libmariadb.dll or rename it before you start heidisql.exe. Then HeidiSQL should fall back to use libmysql.dll and you will see if there is support for TLS1.2.

But I’m unsure whether there is a todo for me in HeidiSQL. There is the MARIADB_OPT_TLS_VERSION setting, which I probably have to set when connecting.

Set MARIADB_OPT_TLS_VERSION to define which TLS protocol versions are allowed. See https://www.heidisql.com/forum.php?t=27158

I just pushed 51da7c8e which probably support TLS up to v1.3. Please update to the next build and report back if that works for you.

Thanks for your quick help.

Now I can connect with TLS 1.2 but connecting to the «old» server without the configuration is not possible anymore. It given the error message

SSL connection error: The message received was unexpected or badly formatted

Your above tip with using the fallback libmysql.dll did not work for the Official release version. I still was not able to connect with TLS 1.2.

Ok, then what TLS version is your old server running? Heidi now allows «TLSv1.0,TLSv1.1,TLSv1.2,TLSv1.3» — while I read of another writing for «TLSv1», which is probably the cause.

SHOW SESSION STATUS LIKE ‘ Ssl_version ‘ yields TLSv1.1

I have same problem, but MySQL has not updated (5.7.26), only HeidiSQL has updated about 3 days ago.

Test with MariaDB 10.3.15 is perfect.

SHOW SESSION STATUS LIKE ‘Ssl_version’ TLSv1

Ok, so lascjr has «TLSv1» running, while HeidiSQL tries to support «TLSv1.0» — I should maybe remove that trailing «.0» or add «TLSv1» to the comma separated list.

Hi ansgar, problem still with revision 5579

I just fixed the wrong writing to «TLSv1». Please update to the next nightly build (5580) and report back if that works now.

In revision 5580, the error message has changed, now is: Unknown SSL error (0x80090308)

Hm shit. So to summarize things: you formerly said

Test with MariaDB 10.3.15 is perfect

So what exactly does not work yet? Is it probably the same as mf posted:

Now I can connect with TLS 1.2 but connecting to the «old» server without the configuration is not possible anymore.

And if so, what server version is it exactly? I cannot reproduce that here, on various servers with no tls_version configuration. So we need to be most exact here, with as many details as you have.

I tested the following HeidiSQL versions (all 64 Bit):

• 10.1.0.5464 — latest official release
• 10.1.0.5572 — 1 revision before MARIADB_OPT_TLS_VERSION
• 10.1.0.5573 — MARIADB_OPT_TLS_VERSION fix
• 10.1.0.5579 — 1 revision before TLSv1
• 10.1.0.5580 — TLSv1 fix

MySQL server 5.7.22-log (variable tls_version : TLSv1,TLSv1.1 , status Ssl_version : TLSv1.1 )

• 5464 connectable
• 5572 connectable
• 5573 SSL connection error: The message received was unexpected or badly formatted
• 5579 SSL connection error: The message received was unexpected or badly formatted
• 5580 Unkonown SSL error (0x80090308)

MySQL server 8.0.16 (variable tls_version : TLSv1.2 , status Ssl_version : TLSv1.2 )

• 5464 SSL connection error: The message received was unexpected or badly formatted
• 5572 SSL connection error: The message received was unexpected or badly formatted
• 5573 connectable
• 5579 connectable
• 5580 connectable

With libmysql.dll instead of libmariadb.dll I get on all Heidi versions:

• MySQL 5.7.22-log connectable
• MySQL 8.0.16 SSL connection error: unknown error number

We created you a user on our test databases. I will send you the credentials via e-mail.

MariaDB 10.3.15 — is perfect work MySQL 5.7.26 — Unknown SSL error (0x80090308)

@mf — thanks a lot for consistent testing results! I’m sure this helps. Also thanks for the credentials you sent me.

Use full path when loading libmariadb.dll or libmysql.dll, so we don’t accidentally prefer a libmariadb.dll somewhere on the users harddisk over the local libmysql.dll. Only if that also fails, fall back to load it from somewhere else. This hopefully helps for debugging SSL connection problems. See https://www.heidisql.com/forum.php?t=27158#p27219

Ok, found out a few things:

• removing the MARIADB_OPT_TLS_VERSION option call fixes connecting to 5.7 with SSL, but fails for 8.0 (this is what you already found out with your tests, mf)
• it makes no difference to additionally set MYSQL_OPT_TLS_VERSION , with any library.
• removing libmariadb.dll did not necessarily cause HeidiSQL to load libmysql.dll from its directory. Instead — if you have another libmariadb.dll on your harddisk and in your path (like me), that one was preferred. This was an issue I just fixed for the next build: the local libmysql.dll now has precedence over another libmariadb.dll or libmysql.dll on your system.
• you did not encounter that issue, so you likely do not have a MariaDB server installed locally
• finally my testing results are consistent with yours — and we still have a problem with a default installation connecting with SSL on 5.7
• I had the idea to offer a new advanced setting for the session, where the user can select the library to use. But that would be awful, as most users won’t know what to select here.

• I had the idea to offer a new advanced setting for the session, where the user can select the library to use. But that would be awful, as most users won’t know what to select here.

It’s not such a bad idea. In summary, there are only two libraries.

I could leave it with a checkbox automatically, and the option, distilling the checkbox in manual, to choose the library.

I found there is a parallel discussion in issue #519, so let’s please proceed there with comments. The reporter closed the issue, but that was probably by mistake.

Also, there is a ticket on https://jira.mariadb.org/browse/MDEV-13492 about that «Unknown SSL error». Probably that will also be helpful to solve this here.

How to resolve hp error code 0xc0000185? if you have any idea then, please share.

Please login to leave a reply, or register at first.

Источник

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.

Источник

MySQL Workbench allows connecting to remote server databases from your personal computer.

However, MySQL workbench failed to connect errors happen due to MySQL version compatibility or set up problems.

One such error is,

Failed to Connect to MySQL at 7129bXXX43dc.XXXX.com:3306 through SSH tunnel at wheelxx@xxx.x.xxx.xx:2414 with user username

At Bobcares, we often get requests from our customers to set up MySQL workbench as part of our Server Management Services.

Today, we’ll see the top reasons that result in MySQL failed connection and how our Support Engineers fix them.

How to Connect to a Cloud Database via MySQL Workbench

MySQL workbench is s free, visual tool for database architects, developers, and DBAs. Also, it provides a platform for database management like development, administration, database design, creation, etc. The maintenance of a database becomes easy with this unique tool.

Well, MySQL Workbench is available on Windows, Linux and Mac OS X. And, that’s yet another reason for its popularity.

Here, let’s see how our Support Engineers connect to Cloud Databases via MySQL Workbench.

1. Initially, we go to http://www.mysql.com/products/workbench/ and install MySQL WorkBench.

2. Then, we launch MySQL Workbench, select Server Administration >> New Server Instance option.

3. We select the Remote Host option and enter the URL you received when you created the database instance. Again, we click Continue.

4. Next, we go to Connection Method menu >> select Standard TCP/IP over SSH.

5. Also, we change the value for SSH hostname to name or IP address of a Cloud Server that we have created.

6. Then, we enter the following value as

1. SSH Username:- cloud server user name
2. MySQL Hostname:-  hostname received while creating the database instance via the API.
3. Username:- enter the username received when you created the database instance.

7. After that, we click Continue.

8. Finally, we enter the database instance password.

That’s it, we’ll get the Server Administration screen.

Common problems after setting up MySQL Workbench

From our experience in managing servers, we’ve seen customers facing different kinds of problems after installing MySQL Workbench.

Let’s see how our Support Team solved these common errors.

Missing SSL certificate

Recently, one of our customers contacted us with a problem related to MySQL Workbench. Also, both the SSH connection to the server and from the server to the cloud database worked. But when tried connecting from MySQL Workbench, it didn’t work. He received an error from Workbench. The error said,

Failed to Connect to MySQL at 7129b041xxxx476e8d37651071b12a6681b732.rackspaceclouddb.com:3306 through SSH tunnel at xxx.x.xxx.xx:2414 with user username connection error: error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown.

On checking, our Support Engineers found that the problem was related to the SSL of the Cloud instance. In order to solve the problem, the options were to either configure MySQL workbench to connect without SSL OR configure SSL for the cloud instance.

However, configuring MySQL workbench without SSL can be a security risk. So, we set up SSL with the Cloud Database instance and solved the problem.

Incompatible MySQL version

Similarly, another customer had an error when tried to setup workbench for cloud hosting platforms like Appian. He got an error "failed to connect to MySQL at localhost 3306 with user root".

While checking, our Support Engineers found that the installed version of MySql was MySql 8.X which is not compatible with workbench. Therefore, we installed the Version 5.7.X version and it sorted out the problem.

[Never worry about MySQL workbench failed to connect errors anymore! We are here to fix it for you.]

Conclusion

In short, errors like MySQL workbench failed to connect may happen due to MySQL version compatibility or because of missing SSL certificate. Today, we saw how our Support Engineers set up MySQL workbench and fix related errors.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;