How to MySQL connect via SSL

dneise · January 29, 2018, 12:12pm

We have a MySQL server, which is soon only accepting connections from the outside world, which are SSL encrypted.

We had a test run and got this exception:

ROOT:Error in <TMySQLServer::TMySQLServer>: Code: 3159  Msg: Connections using insecure transport are prohibited while --require_secure_transport=ON.

The error message:

Connections using insecure transport are prohibited while --require_secure_transport=ON

seems to come directly from the mysql C-API as we got the same message using the mysql command line interface and also using Python when deliberately switching ssl off.

Modern mysql clients enable encryption by default with a fallback to cleartext, older clients default to cleartext with the possibility to enable encryption with a command line parameter.

Now the question is: how can additional parameters to the connect call be provided.

As an example: we had a similar problem with Python. We create the connection using a 3rd party library called sqlalchemy, the call looked like this:

connection = create_engine('mysql://{user}:{password}@{host}/{database}')

And this call lead to the exact same error message. After some research we found, it is possible to provide an additional set of arguments, which is basically just forwarded to the MYSQL-C-API. So this:

connection = create_engine(
    'mysql://{user}:{password}@{host}/{database}',
    connect_args={'ssl': {'ssl-mode': 'preferred'}}
)

solved the problem for us in case of sqlalchemy. Does a similar possibility exist for TMySQLServer?

linev · January 29, 2018, 12:26pm

Hi,

Unfortunately, TMySQLServer class in ROOT does not provide such option.
I found, that in current mysql API there is option MYSQL_OPT_SSL_MODE, which should solve your problem. Look here for more details:

https://dev.mysql.com/doc/refman/5.5/en/mysql-options.html

Can you check if it helps in your case, adding some code here:

github.com

root-project/root/blob/master/sql/mysql/src/TMySQLServer.cxx#L210


      
                   mysql_options(fMySQL, MYSQL_READ_DEFAULT_GROUP, groupname);
                   if (gDebug) Info("TMySQLServer","Read mysql options from %s group of my.cnf file", groupname);
                }
             }
             optarr->Delete();
             delete optarr;
          }
          
          Int_t port = 3306;
          if (url.GetPort()>0) port = url.GetPort();
          
          if (mysql_real_connect(fMySQL, url.GetHost(), uid, pw, dbase, port,
                                (socket.Length()>0) ? socket.Data() : 0 , client_flag)) {
             fType = "MySQL";
             fHost = url.GetHost();
             fDB   = dbase;
             fPort = port;
          } else {
             SetError(mysql_errno(fMySQL), mysql_error(fMySQL), "TMySQLServer");
             MakeZombie();
          }

If you can find solution, I can add correspondent option(s) to TMySQLServer class.

Regards,
Sergey

system · February 12, 2018, 12:26pm

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.