Bug #85025 SSL based Connection failing when sha256_password plugin is set to default
Submitted: 16 Feb 2017 16:12 Modified: 11 Oct 2017 13:35
Reporter: Omer Malik Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Encryption Severity:S3 (Non-critical)
Version:5.7.16 OS:Windows (2012)
Assigned to: CPU Architecture:Any
Tags: .net, SHA256

[16 Feb 2017 16:12] Omer Malik
Description:
We have a MySQL server 5.7.16 CE where sha256_password plugin set to default. when we try to connect it using Connector/Net 6.9.9 (tested with 6.9.8) it shows following error,

Authentication to host 'xxxxxx' for user 'sha256_user' using method 'sha256_password' failed with message: Bad handshake

Note that his error is shown for all connections even if I try to use a user with native password as well, when sha256_password is set to default.
For Application we use C#.net  (.net frameowrk 4.0 and 4.5 )

Now if we made the native password plugin as default in MySQL, and then try to connect with the server, it works for the SHA-256 users as well with same SSL certificate and settings. However, when we made the sha256 plugin default again, it starts showing the same handshake error again.

I have tried also specifying the client certificate file (PFX) with the connection string but still same error. When we use the same client files (pex) with python or PHP, it works. For PHP and Python it works even if we have sha256_password set to default on MySql. 

How to repeat:

All certificates are generated using  https://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html   and https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-ssl.html
 CA is bind to random name, Server is bind to hostname.

Server settings 
....
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

plugin-load = validate_password.dll
validate-password=FORCE_PLUS_PERMANENT
default-authentication-plugin = sha256_password

Sample app Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace MySQLTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string con = "DataSource=myserver;database=database;user=sha256_user;password=password;CertificateFile=D:\\client.pfx;CertificatePassword=pass;SSL Mode=Required";
                using (MySqlConnection connection = new MySqlConnection(con))
                {
                   // connection.
                    connection.Open();
                    connection.close();

                }
            }
            catch (Exception ex)
            {
                
            }

        }
    }
}

Results
No exception  (sha256_password not set to default) 
Authentication to host 'xxxxxx' for user 'sha256_user' using method 'sha256_password' failed with message: Bad handshake     (sha256_password set to default)
[16 Mar 2017 11:25] Muhammad Naveed
Hi, I am facing the same issue. Any feedback on this?
[11 Oct 2017 13:35] Chiranjeevi Battula
Hello Omer,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.