mysql: TLS without certificates (no client-cert, no server-cert-verify) is not possible

[keiki85]

What happened:
Since the database was switchted to TLS only mode "--require_secure_transport=ON" I can't connect to it anymore with Grafana.
MySQL clients like Workbench or IntelliJ are automatically using TLS and connecting without any issue.
Error message:
“logger=tsdb.mysql t=2023-01-16T10:32:40.287979533+01:00 level=error msg=“Query error” error=“Error 3159: Connections using insecure transport are prohibited while --require_secure_transport=ON.””

What you expected to happen:
I can connect to MySQL.

How to reproduce it (as minimally and precisely as possible):
I'm not sure yet. I guess you need to configure a MySQL instance with said flag.

Anything else we need to know?:
I tried to get an answer in the forum but it didn't help https://community.grafana.com/t/cant-connect-to-mysql-datasource-since-switch-to-require-secure-transport-on-mode/79748

Environment:

• Grafana version: 9.3.6 (taken from arch AUR)
• Data source type & version: dunno where to get this information
• MySQL DBMS: Report from IntelliJ connection:

MySQL (ver. 8.0.28-19.1)
Case sensitivity: plain=exact, delimited=exact
Driver: MySQL Connector/J (ver. mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0), JDBC4.2)

• OS Grafana is installed on: Arch Linux
• User OS & Browser: Endeavor OS which is based on arch
• Grafana plugins: No extra

Thank you very much.

[tonypowa]

👋 Thanks for creating this issue, @keiki85

would it make any difference to try a different method? what if you provisioned mysql data source (with TLS) in a yaml file?

[keiki85]

@tonypowa Thanks for the reply and sorry for my late answer.

I get the same issue with provisioning. With my understanding Grafana is doing the same.

Do you have more options with provisioning which I didn't see? On provisioning I didn't find more options regarding TLS.

In my MySQL clients I use (MySQL workbench and IntelliJ using JDBC underneath) I don't need to specify the use of TLS as it automatically is used.

[keiki85]

If I understand the grafana code correct than you specify the "tls" parameter for your MySQL library is only set when certificates are provided. In my case I don't need to provide them. That's why tls is not set and kept at default "false". See https://github.com/go-sql-driver/mysql#usage in the chapter tls.
Code at: pkg/tsdb/mysql/mysql.go:108

The "normal" MySQL client has "preferred" as default configuration. So it will automatically use TLS if available. https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html

For Mysql JDBC library it's the same behavior since 8.0.13 according to https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html

[tonypowa]

hi @keiki85

thanks for the additional notes

however, I am unable the replicate the same environment from my side, so i will seek help from the BI squad, that supports questions related to mysql as a datasource.

they will get in touch with you

[keiki85]

Thank you for your reply.

I got grafana working locally by using following code change. Basically setting tls to preferred when the specific TLS config is not applicable.

Index: pkg/tsdb/mysql/mysql.go
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/pkg/tsdb/mysql/mysql.go b/pkg/tsdb/mysql/mysql.go
--- a/pkg/tsdb/mysql/mysql.go	(revision 211a999a2a6724185acb650f7fd7ec487d460208)
+++ b/pkg/tsdb/mysql/mysql.go	(date 1678092717941)
@@ -106,6 +106,8 @@
 				return nil, err
 			}
 			cnnstr += "&tls=" + tlsConfigString
+		} else {
+			cnnstr += "&tls=preferred"
 		}
 
 		if dsInfo.JsonData.Timezone != "" {

[rmacian]

Does this will be assigned ? I've ran onto the same problem when trying to configure a mysql datasource when secure tranport is enabled using 9.4.17. I've tested so far 9.5 and 10.2.1 with same result
[xorm] [info] 2024/02/26 13:29:32.122745 PING DATABASE mysql logger=tsdb.mysql t=2024-02-26T13:29:32.152392869Z level=error msg="Query error" error="Error 3159: Connections using insecure transport are prohibited while --require_secure_transport=ON." logger=context userId=1 orgId=1 uname=admin t=2024-02-26T13:29:32.15256