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:Microsoft Windows (XP)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: ODBC Connector, secure_auth

[26 Dec 2013 21:33] Jacek Kucharski
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.
[30 Dec 2013 3:43] Bogdan Degtyariov
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.
[31 Jan 2014 1:00] Bugs System
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".
[21 Feb 2014 15:55] Michael Wasson
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.
[24 Feb 2014 4:13] Bogdan Degtyariov
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.
[24 Feb 2014 6:51] Michael Wasson
Then why is the option available in MySQL Workbench?  Shouldn't Workbench and ODBC be consistent?
[24 Feb 2014 7:31] Michael Wasson
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.
[24 Feb 2014 7:38] Bogdan Degtyariov
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.
[24 Feb 2014 7:41] Bogdan Degtyariov
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.
[26 Feb 2014 19:31] Jacob Hershey
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
[27 Feb 2014 2:11] Bogdan Degtyariov
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.
[12 Apr 2014 6:24] Jack Lamport-Stokes
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?
[13 Sep 2014 8:41] Nikos Fatsis
At least is there available client side workaround while using version 5.3 of the Connector ?
[6 Jan 2015 15:53] Nic Sandfield
See this report on a bug in the C API: http://bugs.mysql.com/bug.php?id=75425
[17 Apr 2015 7:36] Martins Brivnieks
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)
[22 Jul 2015 11:31] Ingolf Tippner
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.
[24 Feb 2016 16:30] Brian Henning
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.
[24 Feb 2016 16:38] Brian Henning
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.
[13 Feb 2017 6:43] Bogdan Degtyariov
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.