Preparing your Community Connector for MySQL 8 – part 2 – SHA256

In part 1 of this series we looked at implementing support for the caching_sha2_password authentication plugin using the libmyqlclient C library.  This is possible for C based connectors or connectors that can make use of an external C library.  For some, this is not possible.  For example, Java and C# both function better if they don’t have to thunk out to an external library.  For these, implementing the MySQL client/server protocol natively is required.  This part 2 is about implementing support for the caching_sha2_password plugin natively.  It is expected that you already have a working connector and understand the client/server protocol.  For reference please internal documentation here.  This post will not attempt to always give you precise byte positions or counts.  For that please see the above linked internal documentation.

This blog post will not attempt to explain every aspect of implementing the connection phase of the client server protocol.   For help in implementing it please refer to

Initial Handshake

When initially connected, the server sends an initial handshake packet with lots of information.  Part of that information is the default authentication plugin that is set for the server.  Many connectors give the user the option of specifying an authentication plugin via a configuration or connection string option.  Doing this can eliminate a round trip during authentication.  However if you don’t do this or the user has not specified an authentication method, then attempting authentication using the default method is the way to go.

So a handshake response packet is now sent.  Part of this packet is the username, the “auth response”, and the plugin name.   Username is simple and the plugin name will be the name of the authentication plugin you received in the initial handshake packet.   By default starting with 8.0.4, that will be “caching_sha2_password” (assuming we are not switching to a different method).  The “auth response” part of the packet is the SHA256 scramble of the password.  This scramble uses the format

XOR(SHA256(PASSWORD), SHA256(SHA256(SHA256(PASSWORD)), seed_bytes))

Here the seed_bytes are the bytes originally passed to the plugin.

After sending the handshake response packet to the server, the server will respond with a packet.  There are four possible responses from the server:

  • Server sends a “More data” packet (first byte == 0x01) with the second byte = 0x03.  This will be followed by a normal OK packet.  This is the case when the user’s password is already in the server cache and authentication has succeeded.  We call this the “fast” authentication.
  • Server sends back an “error” packet.  This means the user’s password is in the cache but it doesn’t match what was given.
  • Server sends back an “auth switch” packet (first byte == 0xFE).  This means that the user who is trying to authenticate is using a different authentication plugin than the one specified and an auth switch cycle needs to happen.
  • Server sends back a “More data” packet (first byte == 0x01) with the second byte = 0x04.  This means that more data is needed to complete the authentication.  In the example of using “caching_sha2_password” this means that the users password is not in the server cache and the server is asking the client to send the user’s full password.  This is called the “full” authentication.

Full Authentication

When the server cache does not contain the password hash, the server asks for the full password so the cache can be populated.  This can be done in one of two ways:

  • Passing it in plain text if the connection is already secure using SSL/TLS.
  • Encrypting it with the servers public key if the connection is not secure.

Full Authentication via SSL/TLS

If the connection is already secure then all that is necessary is to pass the users password in clear text.  This is as simple as simply sending a single packet containing only the password as a zero terminated array of bytes.  The server will then respond with either an “Ok” packet if authentication was successful or an “Error” packet if not.

Full Authentication via RSA Key Exchange

If the connection is not already secure then passing the password in clear text is obviously not going to work.  This case is handled by the client encrypting the user’s password with the server’s public key and sending that back.  Here are the steps to accomplishing that.

  • In response to receiving the 0x01 0x04 packet from the server, the client has two choices.  The client could respond with a packet containing the single byte 0x02.  This requests the server send it’s public key.  This is considered somewhat insecure.  A better option if available would be for the client to have the server public key locally.  This would be stored in some implementation defined way.
  • If the client requested the server send the public key, the server will then respond with a “More data” packet containing the servers public key
  • The client then responds with the password encrypted with the servers public key. (see below for more details)
  • The server would then respond with either the Ok or Error packet.

Password Encryption (a closer look)

The password is “obfuscated” first by employing a rotating “xor” against the seed bytes that were given to the authentication plugin upon initial handshake.   Pseudocode for this might look like this:

Buffer would then be encrypted using the RSA public key the server passed to the client.  The resulting buffer would then be passed back to the server.

RSA Padding Change

It’s important to note that a incompatible change happened in server 8.0.5.  Prior to server 8.0.5 the encryption was done using RSA_PKCS1_PADDING.  With 8.0.5 it is done with RSA_PKCS1_OAEP_PADDING.  This means that if you have implemented support for this authentication scheme for servers prior to 8.0.5 you will need to update your connector to make this change.


I hope this blog has helped you understand a bit better how to implement this new security protocol into your product.  We continually strive to find new and better ways to help protect your data.  Please let us know if you have any questions or find any errors with this post!