MySQL Bugs: #70023: Connection using old (pre-4.1.1) authentication protocol refused (secure

Bug #70023	Connection using old (pre-4.1.1) authentication protocol refused (secure_auth en
Submitted:	14 Aug 2013 4:19	Modified:	27 Sep 2013 2:28
Reporter:	Andrew Reis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S4 (Feature request)
Version:	6.0.6-11184	OS:	Windows (8 Professional x64)
Assigned to:		CPU Architecture:	Any

Description:
I'm receiving the following error:
"Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)"

When connecting to a Hostgator shared web server MySQL instance. I can connect with our "cPanel" credentials (overall account) just fine, but when I try and connect using a dedicated user created and added to a database within cPanel, I receive the error. Hostgator says it's not their problem and there must be a place to turn the secure_auth option on and off, though I haven't been able to find it.

How to repeat:
Try to create connection in MySQL workbench to mysql 5.5.30 hostgator/cpanel MySQL instance

Suggested fix:
Add preference option to enable and disable pre-4.1.1 secure_auth client option

Also, PHP on the same shared web server can connect to MySQL using mysqli_connect with the dedicated account just fine. There must be a difference in the way the mysqli php module and workbench 6.0.6 hash/authenticate against mysqld

In the connection settings, go to the Advanced tab and add useLegacyAuth=1

Hi Andrew,

It sounds as though the account causing problems is configured with an older password hash, and the server is configured to refuse such connections.  I don't know the context in which the account was created, or whether you can issue SHOW GRANTS FOR user@host, or SELECT user, host, password, plugin FROM mysql.user - but those would show you the password hash for the given account.  If it's 16 characters long, it's the old password hash, and it makes sense that a server configured with --secure-auth would reject such connections.  Note that this is the default behavior starting with MySQL 5.6, but in MySQL 5.5, it --secure-auth is not enabled by default:

http://mysqlblog.fivefarmers.com/2012/05/30/why-your-pre-4-1-client-wont-like-mysql-5-6/

If this is the case, there's nothing that can be done at the client side to make this account work - the conflict is between the way the account is provisioned (old password hash) and the configuration of the server to refuse to use old password hashing.  Again assuming this is the case, the account cannot be in use today (at least for connections, it may still be the definer or invoker for stored programs/events/etc.) and you may want to simply create a new account.

According to the hosting provider (HostGator) the problem is not on their end. The "client" in this case if MySQL Workbench 6.0.6-11184. How would that "client" still be sending the old hash? Wouldn't that be something that the MySQL community would have to work on?

I just tried Alfredo Kengi Kojima's fix and that worked. Can this be worked into the preferences or under the advanced properties of the connection as a check box?

I missed that you were able to connect via PHP using the same account you have problems with in Workbench when commenting above; that rules out server configuration.  Note that this does mean that your account is configured with the insecure, pre-4.1 password hashing and authentication.  In addition to the security concerns which should encourage you to create a more secure account, the 5.6 behavior change will likely apply should your database be upgraded (and --skip-secure-auth isn't explicitly added by your hosting provider), and that account will be "locked out".

We'll add a specific option for this, thanks for filing this.

Adding useLagacyAuth=1 works for the editor connection but export data (mysqldump) continues to fail with the same error.  The db server is mysql 5.0.77 and has not changed.  The only change was upgrading Workbench from 5.2.47 to 6.0.6.

I've the same problem.
The mysql connection is ok after use useLegacyAuth=1, but export data not work yet.
BTW,If userlegacyAuth not set, mysql workbench will crash when open connections every time.
my OS: Mac OS X 10.8, mysql server: 5.0.95, workbench 6.0.6

Hi,

I have the some error when i try to connect remote database, but my colleague use the same user & pwd and it's work for him !

So i'm not sure if it's due to the server side, i mean the configuration for hash method of passwords

Any idea what it could be the reason ?

i'm using Mysql workbench 6.0.6-win32

Thanks

I used Alfredo Kojima's fix in the Other Options box and it worked.

I have found the fix and it worked for me
It is a bug of settings where variable old_passwords is set to 1;
I have fixed it using :

Set session old_passwords=0; set password for 'user'@'%' = password('mypassword');

Reference
http://stackoverflow.com/questions/18017354/mysql-odbc-link-fails-due-to-authentication-pr...

Fixed as of MySQL Workbench 6.0.7, and here's the changelog entry:

A new option was added in the connection settings "Advanced" tab for
toggling the secure_auth (useLegacyAuth) option, to optionally connect to
legacy systems.

Thank you for the bug report.

As far as I can see the problem still persists during the Bulk Data Transfer. I get these errors.

15:12:04 [ERR][      copytable]: Failed opening connection to MySQL: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)
15:12:04 [ERR][      copytable]: Exception: mysql_real_connect: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

Note that "Use the old authentication protocol" has been ticked on in both (target and source) connections.

confirmed.

It seems that a similar error still exists in my MySQL Workbench V6.0.7.11215.

When I set the option Advanced/disable_secure_auth, a connection to the MySQL server can be establied, but a Data Export fails with the error message

14:47:29 Dumping XYZ (all tables)

Running: mysqldump.exe --defaults-extra-file="c:\users\AAA\appdata\local\temp\tmpji2srr.cnf"  --set-gtid-purged=OFF --user=BBB --max_allowed_packet=1G --host=CCC --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "DDD"

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

Operation failed with exitcode 2

Greetings

Andre

I am seeing the same problem. It appears that wbcopytables.exe is not reading the setting of the "Use the old authentication protocol" option on the advanced tab. because the connection verifies and obviously works all the way up to the actual copy and the error messages all reference wbcopytables.exe.

I can confirm this is still an issue for data export in workbench 6.0.8.11354 build 833 community edition. 

The initial connection now works with the additional option added but when using export it does not cary over that connection parameter.

Further information..

Running: mysqldump.exe --defaults-extra-file="appdata\local\temp\3\tmpxdzape.cnf"  --set-gtid-purged=OFF --max_allowed_packet=1G --host=localhost --protocol=tcp --user=admin --port=50605 --default-character-set=utf8 "dbname_VNLC1lGFCsGP"

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

Operation failed with exitcode 2

09:22:00 Export of dbname.sql has finished with 1 errors

This EXACTLY my problem.  To get the connection to work I did:

MYSQL Workbench:  in order for the connection to work, in the "Connections" tab, "Advanced" tab add the value pair: "useLegacyAuth=1" (without quotes) to the "Others" text field.  (Source:  http://bugs.mysql.com/bug.php?id=70023)

But now I can't back-up the database because I get the (pre-4.1.1) error.  The "System Variable" "secure_auth" has a value of "OFF".

The last post is from January 22 2014 any resolution since then?  I am running MySQL Workbench 6.1 Community, Version 6.1.4.11773 Build 1454 (wish that version text was selectable).

Thanks,
Jeff

Hi Jeff,

I never did find a solution to this. If you have the same problem on the latest version it must still exist when connecting to old auth servers.

I resorted to using mysqldump from the command line and then upgraded the server to a newer version later when convenient.

Chris

What about using an older version of MySQL Workbench would that work?

I installed MySQL Administrator 1.2.15 and was able to generate a back-up file.  Issue resolved with a work-around.

I just downloaded the newest version of MySQL Workbench (6.1 CE). There is a checkbox under Connection | Advanced (tab) called "Use the old authentication protocol". I turned this on and my connection worked.

Alfredo Kengi Kojima sugesstion to fix the bug worked..

Despite this bug is marked as CLOSED, is's not solved actually! Yes, there's a new option on Advanced tab, from Manage Server Connections window, called "Use the old authentication protocol.", which solves only the initial connection problem. As reported previously, further actions such as import and export are NOT using this setting, and the action fails with an ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

Useless resolution...

using v6.1.6 CE

Just installed on a server in order to export all the different database's so that we can migrate off of this old server, and still run into the problem of the "Use Old Authentication" not translating over to the import / export section which really, is the only reason we installed workbench for. Is this getting fixed anytime soon as this is a serious setback to using workbench.

+1 the problem with exporting data using the migration wizard.  Schema creation works fine, but the connection appears to be reauthenticated without respect for the "use old authentication..." setting for the export operations.

Yes, I can confirm the same as above - still not able to do the backup but can connect. Setting does not carry over to backup screen.

Using the latest MySQL Workbench, If I try to do a data export from within MySQL Workbench interface while connected to an old MySQL Server then I get "mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect". Looks like the issue is fixed in only one place and not carried over

I am running into the same error while trying to export data from an old MySQL Server 5.1 system (for which I only have read-only access over SSH to) which the fix put in to close this bug did not fix as reported above.  Therefore I re-opened this Data Export compatibility issue as bug #73331.

Since installing 6.1.7 yesterday I too can only connect to database if I click 'Use the old authentication protocol' and am still unable to export database files and I get this message: 'Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect'.

I am using Mac OS X 10.6.8.

I use MySQL Workbench to back up my databases so this is a serious problem for me.

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

Yo he tenido el mismo problema con el mySql Workbeanch versión 6.2. conectandome a un host arrendado que no permitia hacer cambios en las opciones de la cuenta del usuario. Para solucionarlo, seleccioné la pestaña "Advanced" y marqué la opción "Use the old authentication protocol". De esa manera pude conectarme sin problemas.

I had the same problem with Workbeanch mySql version 6.2. connecting me to a host leased No you can make changes to the settings of the user account. To fix this, I selected the "Advanced" tab and checked the "Use the old authentication protocol" option. That way I could connect without problems.

I'm using Workbench 6.2.4.12437 Build 2426 (64 bit) and I can only connect to a remote DB by checking "Use the old authentication protocol."  

The Data Migration wizard gets all the way to the Data Migration > Bulk Data Transfer step (which includes successfully testing the connection) before failing:

Starting...
Prepare information for data copy...
Prepare information for data copy done
Create shell script for data copy...
Table copy script written to C:\Users\mbmas_000\Desktop\copy_migrated_tables.cmd
Create shell script for data copy done
Determine number of rows to copy....
Counting number of rows in tables...
wbcopytables.exe --count-only --passwords-from-stdin --mysql-source="XXXX REDACTED XXXX" --source-use-cleartext --table-file=c:\users\mbmas_~1\appdata\local\temp\tmpzfnzr4
18:25:47 [INF][      copytable]: --table `XXXX REDACTED XXXX`	`invXXXXX`
18:25:47 [INF][      copytable]: --table `XXXX REDACTED XXXX`	`proXXXXX`
18:25:47 [INF][      copytable]: --table `XXXX REDACTED XXXX`	`domXXXXX`
18:25:47 [INF][      copytable]: --table `XXXX REDACTED XXXX`	`proXXXXX`
18:25:47 [INF][      copytable]: --table `XXXX REDACTED XXXX`	`domXXXXX`
18:25:47 [INF][      copytable]: --table `XXXX REDACTED XXXX`	`invXXXXX`
18:25:47 [INF][      copytable]: --table `XXXX REDACTED XXXX`	`docXXXXX`
18:25:47 [INF][      copytable]: --table `XXXX REDACTED XXXX`	`perXXXXX`
18:25:47 [INF][      copytable]: Connecting to MySQL server at XXXX REDACTED XXXX:3306 with user XXXX REDACTED XXXX
18:25:47 [ERR][      copytable]: Failed opening connection to MySQL: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)
18:25:47 [ERR][      copytable]: Exception: mysql_real_connect: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

Loading table information from file c:\users\mbmas_~1\appdata\local\temp\tmpzfnzr4

ERROR: Determine number of rows to copy: Error getting row count from source tables, wbcopytables exited with code 1
Failed

As others have hinted, this issue is still not fixed. I am trying to do a Data Export operation on a MySQL 5.1.45 database table. Here is the output:

12:21:51 PM Dumping dbname (table)
Running: /usr/libexec/mysql-workbench/mysqldump --defaults-file="/tmp/tmpVYrS_v/extraparams.cnf"  --set-gtid-purged=OFF --user=user --host=host --protocol=tcp --port=3306 --default-character-set=utf8 --no-data --skip-triggers "dbname" "table"
mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect