Support for Azure Database for MySQL (New PaaS in Azure)

[cohowap]

Hello, I am trying to migrate an application that uses a self hosted MySQL 5.6 to a hosted MySQL on Azure (not ClearDB). I am getting a Handshake error when I connect with and without SSL. (NOTE: This is most likely an issue with MySQL lib, but I figured I would post here while I dig into it).

Reference Documents:
https://docs.microsoft.com/en-us/azure/mysql/quickstart-create-mysql-server-database-using-azure-portal

https://docs.microsoft.com/en-us/azure/mysql/howto-configure-ssl

SSL CA Cert:
https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt

NOTE: I converted to a .cer and pem, trid all 3 neither worked.

var sequelize = new Sequelize(config.database, config.username, config.password, {
    host: "testdb.mysql.database.azure.com",
    port: 3306,
    dialect: "mysql",
    dialectOptions: {
        encrypt: true,
        ssl: {
            ca: fs.readFileSync('C:/Development/mysql-ca.pem')
        }
    }
});

What do you expect to happen?

I would expect everything to be working as normal.

What is actually happening?

Unhandled rejection SequelizeBaseError: 101057795:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:openssl\ssl\s23_clnt.c:794:
    at Handshake._callback (c:\Development\node_modules\sequelize\lib\dialects\mysql\connection-manager.js:95:20)
    at Handshake.Sequence.end (c:\Development\node_modules\mysql\lib\protocol\sequences\Sequence.js:86:24)
    at c:\Development\node_modules\mysql\lib\protocol\Protocol.js:175:20
    at TLSSocket.<anonymous> (c:\Development\node_modules\mysql\lib\Connection.js:315:9)
    at emitOne (events.js:96:13)
    at TLSSocket.emit (events.js:188:7)
    at TLSSocket._tlsError (_tls_wrap.js:566:8)
    at emitOne (events.js:96:13)
    at TLSSocket.emit (events.js:188:7)
    at emitErrorNT (net.js:1281:8)
    at _combinedTickCallback (internal/process/next_tick.js:74:11)
    at process._tickCallback (internal/process/next_tick.js:98:9)

Dialect: mysql
Database version: 5.6
Sequelize version: 3.30.4

[bgrainger]

I filed mysqljs/mysql#1729 for a handshaking bug with Azure Database for MySQL when SSL is off. I suspect there is a second issue (the one reported above) with SSL is on.

[cohowap]

Update: Microsoft seems to have acknowledged the issue and is addressing the SSL problem from the server side. See: https://social.msdn.microsoft.com/Forums/en-US/c8fedbcc-909d-41ce-8c72-0374f76fdf82/cannot-connect-from-nodejs?forum=AzureDatabaseforMySQL

There is yet, another problem, when not using SSL, the MySQL lib has an issue properly switching modes and assumes an unsupported protocol. Thanks to Brad, there is a PR to fix this, but it seems to be in waiting state. @bgrainger Thanks for your efforts putting that together.

[bgrainger]

According to this Stack Overflow answer, Microsoft has fixed the SSL connection error.

The handshaking bug mentioned still exists though and will still cause problems once the SSL connection is established.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[cohowap]

Still an issue, more progress is being made. Once mysql lib is updated, sequelize will have to also be updated with latest mysql.

mysqljs/mysql#1776

[sidorares]

@cohowap currently default driver is mysql2 and there is already support for auth plugins there

[cohowap]

@sidorares you are correct sir. Thank you for pointing that out. I will run my tests and see where it gets me.

sequelize/lib/dialects/mysql/connection-manager.js

Line 33 in 2fc43c4

this.lib = require('mysql2');

[kahwooi]

Download the cert from :

https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem

Then connect to the database with the following options :

 dialectOptions: {
     encrypt: true,
     ssl : {
        ca: fs.readFileSync('BaltimoreCyberTrustRoot.crt.pem')
     }
  }

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂