MySQL Bugs: #71234: Impossible to disable 'secure_auth' option in ODBC connectors ver 5.x

Bug #71234	Impossible to disable 'secure_auth' option in ODBC connectors ver 5.x
Submitted:	26 Dec 2013 21:33	Modified:	13 Feb 2017 6:43
Reporter:	Jacek Kucharski	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S4 (Feature request)
Version:	5.x	OS:	Windows (XP)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ODBC Connector, secure_auth

Description:
ODBC connectors 5.x seem to ignore 'secure_auth=off' statement in my.cnf file, so it seems practically impossible to configure ODBC connector to make connections with secure authentication disabled in order to be able to use existing older passwords.
Tried in various variants: 'off' and '0' switch; 'secure-auth' and 'secure_auth' spelling; [client] and [mysqld] sections; different locations of 'my' file; 'cnf' and 'ini' file extensions. Always the message "Connection using old (pre 4.1.1) protocol refused (client option 'secure_auth' enabled)".  
Tried on 5.1.13, 5.2.6 and 5.3.1 beta ODBC connectors on Win XP Prof. The 'Read options from my.cnf' in the ODBC configuration tab is checked on. The ODBC connector 3.51 works OK.

How to repeat:
In win XP, enter 'secure_auth=off' in my.cfg file for ODBC connector and  try to connect.

Suggested fix:
A USEFUL IMPROVEMENT: In the 'Details' tab of the ODBC connector's configuration form, to add a check-field control to make possible to quickly set the 'secure_auth' option on/off. This shall be saved as a setting of the given ODBC connection.

Hi Jacek,

Thanks for your interest in MySQL software. I do not think that MySQL ODBC driver is the right place to manage the secure_auth variable.

Please check the online documentation:
http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_secure-auth

The variable scope is GLOBAL, so it cannot be set individually for each session as you wanted.
With MySQL Server 5.6.5 and newer the secure authentication option is enabled by default.
If you want old (pre 4.1) clients to be able to connect to the server you need to add secure-auth=off into the [mysqld] section of the MySQL server configuration file. This option will not be picked up from the config file unless MySQL Server is restarted. However, it is possible to set the variable dynamically without restarting the server. Run the following commands in mysql command line client:

set global secure_auth=off;

Check the result using

show variables like "secure_auth";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| secure_auth   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Please let me know if it helped to resolve the problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I agree this feature should be added:  A USEFUL IMPROVEMENT: In the 'Details' tab of the ODBC connector's configuration form, to add a check-field control to make possible to quickly set the 'secure_auth' option on/off. This shall be saved as a setting of the given ODBC connection.

Michael,

Please read my message from 30 Dec 2013 why adding this option on the ODBC level does not make any sense and why it should be done in the server configuraton.

It is not supported on a per-connection basis. secure_auth is a global server option. Turning it on/off is going to affect ALL connections for all clients established after the change is made.

Also, it does not look very secure if the client side makes decisions about the server security. The server should set the level of security for the clients, not vice versa.

Then why is the option available in MySQL Workbench?  Shouldn't Workbench and ODBC be consistent?

The option in MySQL Workbench is on the "Setup New Connection" dialog, Advanced tab.  "Use the old authentication protocol."  checkbox.  "This option disables Connector/C++'s secure_auth option."

This would imply that the change only occurs in the CLIENT not the SERVER.

Thanks for clarification and sorry for misunderstanding, secure_auth is a bit ambiguous.

There is a secure_auth server option, which can be set through my.cnf/my.ini config. As I said, there is not much sense in setting it from the client.

Your reference to MySQL Workbench helped me to figure out that you want MYSQL_SECURE_AUTH option to be used. Indeed, even the newest version of Connector/ODBC Driver does not have any means to set it. This bug report is qualified as a feature request.

The connection dialog in the Workbench is not entirely correct: it should have mentioned MYSQL_SECURE_AUTH instead of secure_auth. In this way it looks less confusing and clearly states what exactly the option does.

HI!
I tried creating a my.cnf and put it at c:\
My file had:
[client]
mysql_secure_auth = off

And, I still got the "Connection using old (pre 4.1.1) protocol refused (client option 'secure_auth' enabled)"

So I tried it without [client] (ie, just one line:
mysql_secure_auth = off

and it crashed my odbc setup window.
I'm new to setting up mysql odbc, so I don't know what to put in the my.cnf file

Jacob,

Sorry to disappoint you, but there is no way you can change mysql_secure_auth flag for Connector/ODBC through my.cnf or connection string options.

Right now it can only be done globally on the server.

I am new to this so I may be asking a dumb question.  I also can connect to an existing database with Workbench using useLegacyAuth=1 but not through the ODBC Connector.  How can I set mysql_secure_auth flag globally on the server?  I have seen somewhere that a connection string works if an older driver installed.  Should I do this?

At least is there available client side workaround while using version 5.3 of the Connector ?

See this report on a bug in the C API: http://bugs.mysql.com/bug.php?id=75425

Please fix it some day!
last working MyODBC connector is 5.02.04 (a/w)
all next give
!!! Err Nummber:-2147467259
[MySQL][ODBC 5.2(w) Driver]Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

Client: MsAcces97|VB6 on W8|W7|XP
Server: 5.0.67 (secure_auth = OFF)

Mysql ODBC Connector 5.3

Server Settings:
old-password
secure-auth = false

It seems, that the client always tries to connect with secure-auth.
But the server refused, because don´t support secure-auth.

How can I setup the client to use old connection type without secure-auth?
The client must use the old password hashs.

Affects all OSes.  My platform is Linux (Debian Jessie).  This is CRITICAL to allow newer ODBC applications to make DSN-less connections to older "secure_auth=OFF" servers.

Please, please consider escalating this.

Didn't mention:
Server version: 5.0.95
secure_auth = OFF
Users table doesn't have a `plugin` column

I was able to work around by setting a new secure password on the user, but that doesn't reduce the severity of this issue in the general case.

Let me amend my previous statement to say it's critical for users of older databases who still have old passwords.  It's not always possible for servers to be updated if there are legacy clients still using them.

Posted by developer:
 
Unfortunately, the Connector/ODBC 5.3 will not support disabling SECURE_AUTH option because it is deprecated in libmysqlclient.
If the client code needs connecting to older MySQL server with pre-4.1 authentication it needs to use MySQL Connector/ODBC v3.51.