Add support for sha256_password

[yukozh]

Since Azure will enforce sha256 soon, could you please add support for sha256_password? Or @elemount will help you to implement this. He is working on support sha256_password for each mysql clients.

[bgrainger]

From the documentation:

To connect to the server using an account that authenticates with the sha256_password plugin, you must use either an SSL connection or an unencrypted connection that encrypts the password using RSA, as described later in this section.

I'll initially plan to support just SSL connections. That page doesn't explain how the client side plugin works, other than:

If an SSL connection is used, the password is sent as cleartext but cannot be snooped because the connection is encrypted using SSL.

Perhaps this means it just works the same as mysql_clear_password (in the protocol) and sha256_password is just a signal to the server on how to store the password?

[yukozh]

FYI
https://web.archive.org/web/20170404163410/https://dev.mysql.com/doc/internals/en/sha256.html

[yukozh]

This is a blocking issue, we have a plan to use MySqlConnector instead of the oracle one in Azure database for MySQL service. But we have enforced sha256, without sha256 supporting, we cannot get our service work with MySqlConnector.

[yukozh]

BTW, Could you please make the test not only the performance but also the stability. The oracle one lost connection sometimes and has a memory leak bug. That's the one of reasons to switch to yours.

[elemount]

Hi @bgrainger , sha256_password plugin have two scenario, 1. SSL enabled, it just works like clear password, this scenario only need little effort. 2. SSL does not enabled, and it will use RSA mechanism. Describe in the link https://dev.mysql.com/doc/refman/5.7/en/sha256-pluggable-authentication.html .

[caleblloyd]

Here is a good Stack Overflow Post on RSA in .NET

In the unencrypted case, the client can optionally provide the server RSA public key locally. This would need a new connection string option such as SHA256 Password Public Key File

[bgrainger]

If the consumer doesn't provide the server RSA public key, the connector library can request it by sending the single byte 01 during authentication. The server will then reply with the public key file. This SO answer gives an example of loading it (I haven't tested it).

[bgrainger]

I've opened a PR that adds sha256_password support for SSL connections only (same as Connector/NET). @kagamine is this sufficient for your needs?

Supporting non-SSL connections requires RSA public-key encryption. I have some WIP code on my sha256-rsa branch but I wasn't able to get it working. EDIT: I found the bug and will just need to clean up the code before I can merge it.

[bgrainger]

if there's a bug in the XOR logic or something else

@elemount has opened PyMySQL/PyMySQL#583 which demonstrates how to write this code in Python.

[yukozh]

We need both scenario, if you need, @elemount will help you implement that.

[bgrainger]

@kagamine I just found the bug (and edited my previous comment); both scenarios (SSL and non-SSL) will be supported in MySqlConnector.

[bgrainger]

Just updated PR #284 with sha256_password support for SSL connections and RSA public key encryption for non-SSL connections (requires MySQL Server with OpenSSL).

[bgrainger]

Shipped in 0.22.0.