MySQL SSL verification error after update to 23.01

[mkl262]

Hi, I have upgraded ejabberd from 22.05 to 23.01, and after the upgrade ejabberd fails to connect to MySQL DB because of an SSL error. When I change sql_ssl_verify to false, the connection is successful. The MySQL certificate is validated by the mysql-ca.crt certificate, and it is verified to work in other services connecting to the same MySQL server.

ejabberd.yml MySQL config:

sql_type: mysql
sql_server: "mysql"
sql_database: "ejabberd"
sql_username: "ejabberd"
sql_password: 'password'
sql_port: 3306
sql_pool_size: 20
sql_keepalive_interval: 1
sql_start_interval: 5
sql_ssl: true
sql_ssl_verify: true
sql_ssl_cafile: "/mnt/ssl/mysql-ca.crt"
new_sql_schema: false
default_db: sql

Error from ejabberd.log:

2023-02-22 19:45:47.705514+00:00 [notice] TLS client: In state certify at ssl_handshake.erl:2084 generated CLIENT ALERT: Fatal - Handshake Failure
 - {bad_cert,hostname_check_failed}
2023-02-22 19:45:47.701525+00:00 [notice] TLS client: In state certify at ssl_handshake.erl:2084 generated CLIENT ALERT: Fatal - Handshake Failure
 - {bad_cert,hostname_check_failed}
2023-02-22 19:45:47.705198+00:00 [notice] TLS client: In state certify at ssl_handshake.erl:2084 generated CLIENT ALERT: Fatal - Handshake Failure
 - {bad_cert,hostname_check_failed}
2023-02-22 19:45:47.701569+00:00 [notice] TLS client: In state certify at ssl_handshake.erl:2084 generated CLIENT ALERT: Fatal - Handshake Failure
 - {bad_cert,hostname_check_failed}
2023-02-22 19:45:47.707780+00:00 [error] p1_mysql_conn: ssl start failed: {tls_alert,
                                  {handshake_failure,
                                   "TLS client: In state certify at ssl_handshake.erl:2084 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}"}}

2023-02-22 19:45:47.707775+00:00 [error] p1_mysql_conn: ssl start failed: {tls_alert,
                                  {handshake_failure,
                                   "TLS client: In state certify at ssl_handshake.erl:2084 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}"}}

2023-02-22 19:45:47.701489+00:00 [notice] TLS client: In state certify at ssl_handshake.erl:2084 generated CLIENT ALERT: Fatal - Handshake Failure
 - {bad_cert,hostname_check_failed}
2023-02-22 19:45:47.707933+00:00 [error] p1_mysql_conn: ssl start failed: {tls_alert,
                                  {handshake_failure,
                                   "TLS client: In state certify at ssl_handshake.erl:2084 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}"}}

[licaon-kter]

You've read https://github.com/processone/ejabberd/issues/3981 yet?

[mkl262]

Yes, but I dont use mutual TLS. I dont understand why I have to set sql_ssl_verify to false for it to work.

[licaon-kter]

Do you run the mysql server on the same server or on a different one? If the same you can disable the SSL thing, right?

[prefiks]

sql_ssl_verify checks if cert was signed by known certificate authority, i am guessing you are using self signed cert - so it's not and so you cert don't pass that verification. You could try to create cert that is signed properly, but you will at least need to use real domain name for that.

[mkl262]

@licaon-kter No, I run the MySQL server on a different server, and I must use SSL. @prefiks Yes, the CA certificate is self signed, but thats why I added the sql_ssl_cafile option, doesnt it use this CA certificate to verify the MySQL server certificate?

[dkliss]

sql_ssl_verify

I had similar issue with my external MySQL server, which does not currently have a domain name but only a private IP address. If you have self-signed certificate for a MySQL (and the certificate is generated by MySQL itself) on a separate machine, it only worked with sql_ssl_verify: false in my case.

Not knowing, what the setup behind sql_ssl_verify, what I understood from MySQL documentation "Host name identity verification with VERIFY_IDENTITY does not work with the self-signed certificates that are created automatically by the server".

I was using MySQL (8.0) auto created TLS certificate as I did not have a domain name for the MySQL server. If you have an actual domain name for your mysql server and a certificate from lets encrypt (say), then you may be able to set sql_ssl_verify to true as you are no longer using "auto MySQL auto generated certificates". I have not tested this and hence cannot confirm But it may satisfy MySQL requirement of host name identity verification & then you may be able test with sql_ssl_verify: true and see if it works.

[badlop]

@mkl262 were you able to solve this problem, or bypass it somehow, and can this issue get closed?

[Neustradamus]

@mkl262, @dkliss: Have you looked the new ejabberd version? 23.04 has a lot of SQL improvements (some by @nosnilmot):

• https://www.process-one.net/blog/ejabberd-23-04/

[dkliss]

@mkl262, @dkliss: Have you looked the new ejabberd version? 23.04 has a lot of SQL improvements (some by @nosnilmot):

• https://www.process-one.net/blog/ejabberd-23-04/

I have only started testing 23.04. It is working for me without changing anything on my old MySQL configuration i.e. i still have sql_ssl_verify: false. Since, I am using a self-signed certificate, this issue may not apply to me.