Connecting to Azure Database for MySQL fails

[bgrainger]

Create an Azure Database for MySQL 5.7 instance and attempt to connect to it. The following exception is thrown:

Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.
    at Handshake.UseOldPasswordPacket (F:\mysql\lib\protocol\sequences\Handshake.js:85:15)
    at Protocol._parsePacket (F:\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (F:\mysql\lib\protocol\Parser.js:76:12)
    at Protocol.write (F:\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (F:\mysql\lib\Connection.js:103:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    at TCP.onread (net.js:548:20)
    --------------------
    at Protocol._enqueue (F:\mysql\lib\protocol\Protocol.js:141:48)
    at Protocol.handshake (F:\mysql\lib\protocol\Protocol.js:52:41)
    at Connection.connect (F:\mysql\lib\Connection.js:130:18)
    at Object.<anonymous> (F:\Code\Projects\azure-mysql\index.js:9:12)
    at Module._compile (module.js:570:32)
    at Object.Module._extensions..js (module.js:579:10)
    at Module.load (module.js:487:32)
    at tryModuleLoad (module.js:446:12)
    at Function.Module._load (module.js:438:3)
    at Module.runMain (module.js:604:10)

I believe this is the same problem as mysql-net/MySqlConnector#259 with a spurious error message due to Handshake.js assuming all packets starting with 0xFE are requesting old auth.

[dougwilson]

What should it do instead? Is there any documentation you can point to showing the wire protocol that Azure is using? You can also put together a PR if there is nothing you can point me to.

[bgrainger]

Oracle literally just deleted all the protocol documentation (https://dev.mysql.com/doc/internals/en/client-server-protocol.html) so we have to use archived docs.

As discussed in Authentication Method Mismatch, an Authentication Method Switch Request packet can be sent by the server during initial handshaking.

mysqljs assumes that this always means dropping down to mysql_old_password, but that isn't the case here: Azure sends a request to re-auth with `mysql_native_password. As per my commit for MySqlConnector, we have to detect this and send an authentication response.

I'm trying to put together a PR now; I'm familiar with the MySQL protocol but don't know the mysqljs library that well.

I've also posted on the MSDN forums requesting that Azure MySQL support fast path authentication (which would prevent this from the server side).

[dougwilson]

Oh, gotcha, this is an auth plugin switch request. Yes, this library does not support that part of the protocol currently. I believe that makes this issue a duplicate of #1396

[dbalaouras]

I'm having exactly the same issue while setting up Ghost with Azure's Mysql Database. Is there a known workaround yet?

[bgrainger]

@dbalaouras I authored a workaround in e3e123e (but it hasn't been merged in yet). I suppose you might be able to cherry-pick that fix to a local copy of the code (and/or use npm link)?

The only other workaround I can think of would be a custom MySQL proxy that sits between this code and Azure Database for MySQL, and handles the authentication correctly; that would be a rather complex project though. This wouldn't work unless it knew your original password.

Other than that, based on what I know about Azure MySQL and mysqljs, I can't think of an easy workaround.

[dbalaouras]

@bgrainger many thanks for the followup. I need to use the original version of Ghost, hence I cannot modify it's codebase. I guess I have to wait for your patch to get pulled, or Azure to support fast path authentication..in the meanwhile I'm dropping Azure Mysql.