Authentication Exception from C# app when connecting to MySQL 8

Question

We have written a test harness as our application is failing with the following exception when connecting to a MySQL 8 database:

Unhandled Exception: System.Security.Authentication.AuthenticationException: 
    Authentication to host 'xxx.xxx.x.xx' faied.
        at MySql.Data.Common.Ssl.StartSSL(Stream& baseStream, 
            Encoding encoding, String connectionString)
        at MySql.Data.MySqlClient.NativeDriver.Open()
        at MySql.Data.MySqlClient.Driver.Open()
        at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
        at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()
        at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
        at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
        at MySql.Data.MySqlClient.MySqlPool.GetConnection()
        at MySql.Data.MySqlClient.MySqlConnection.Open()
        at DatabaseTestConnection.Program.Main(String[] args)

So I wrote the following test harness to try get more debug information...

using MySql.Data.MySqlClient;
using System;

namespace DatabaseTestConnection
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("MySQL Database Connection Test");
            Console.WriteLine("Please enter database IP");
            string dbIP = Console.ReadLine();

            string connectionString = @$"
                SERVER={dbIP};
                DATABASE=test;
                UID=xxxxx;
                PASSWORD=xxxxxx;
                DEFAULT COMMAND TIMEOUT = 180;";

            MySqlConnection connection = new MySqlConnection(connectionString);

            try
            {
                Console.WriteLine("Connecting to the database....");
                connection.Open();
                Console.WriteLine("Succesfully Connected to the database....");
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine(
                            "{0} {1} : Cannot connect to server. Contact adminis",
                            DateTime.Now.ToLongDateString(),
                            DateTime.Now.ToLongTimeString());
                        break;
                    case 1045:
                        Console.WriteLine(
                            "{0} {1} : Invalid username/password, please try again",
                            DateTime.Now.ToLongDateString(),
                            DateTime.Now.ToLongTimeString());
                        break;
                    default:
                        Console.WriteLine(
                            "{0} {1} : Unable to open connection: {2}",
                            DateTime.Now.ToLongDateString(),
                            DateTime.Now.ToLongTimeString(),
                            ex.Message);
                        break;
                }
                Console.WriteLine("Press any key to continue....");
                Console.ReadLine();
                System.Environment.Exit(1);
            }

            Console.WriteLine("Counting users table....");
            int Count = 0;
            
            //Create Mysql Command
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = connection;
                cmd.CommandText = "SELECT COUNT(*) FROM users";                    

                try
                {
                    //ExecuteScalar will return one value
                    Count = int.Parse(cmd.ExecuteScalar() + "");
                    Console.WriteLine(
                        $"Counted {Count.ToString()} users in the users table");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("{0} {1} : Database error on Select count: {2}",
                        DateTime.Now.ToLongDateString(),
                        DateTime.Now.ToLongTimeString(),
                        ex.Message);

                    Count = 0;
                    Console.WriteLine("Press any key to continue....");
                    Console.ReadLine();
                    System.Environment.Exit(1);
                }
            }

            try
            {
                Console.WriteLine("Closing the connection....");
                connection.Close();
                Console.WriteLine("Succesfully closed the connection");
                Console.WriteLine("Press any key to continue....");
                Console.ReadLine();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("{0} {1} : Unable to close connection: {2}",
                    DateTime.Now.ToLongDateString(),
                    DateTime.Now.ToLongTimeString(),
                    ex.Message);

                Console.WriteLine("Press any key to continue....");
                Console.ReadLine();
            }
        }
    }
}

The test harness works on a pc which is not in AD but fails with the above error on one within AD.

Am I missing some additional configuration within the connect string?

Answer 1

I experienced the very same issue in the ModDbExport module of Rapid SCADA. When exporting to MSSQL I've had no issues but exporting to MySQL wasn't straightforward.

1. the connection options were limited to server, database and login credentials. The connection string was the only way.

2. there is an obscure option, SslMode, that apparently defaults to "Preferred" and with which the connection didn't work. Note: the connection is 'not' secure, as security is provided by an SSH tunnel that forwards localhost port 3306... The export module believes it's connecting and sending data to the local machine, but instead it sends it to a db server by a secure channel).

The solution was to explicitly declare SslMode=None in the connection string. The data export is since working flawlessly.