Cannot connect to Azure MySQL database from .NET Connector

Question

I tried to connect to the Azure MySQL database using MySQL Workbench and MySQL Shell and it works fine. Now I am trying to connect using following C# code:

var connStringBuilder = new MySqlConnectionStringBuilder
{
    Server = "creatur-db.mysql.database.azure.com",
    Database = "test",
    UserID = "creatur_db_main@creatur-db",
    Password = "{my_password}",
    SslMode = MySqlSslMode.Preferred,
};

using (MySqlConnection connection = new MySqlConnection(connStringBuilder.ToString()))
{
    connection.Open();

    connection.Close();
}

Here I replaced {my_password} with password to the database and it gives me an exception inside Open method:

MySql.Data.MySqlClient.MySqlException: 'Authentication to host 'creatur-db.mysql.database.azure.com' for user 'creatur_db_main@creatur-db' using method 'mysql_native_password' failed with message: The connection string may not be right. Please visit portal for references.

I also tried different connection strings:

Server=creatur-db.mysql.database.azure.com; Port=3306; Database=test; Uid=creatur_db_main@creatur-db; Pwd={my_password}; SslMode=Preferred

and

Database=test; Data Source=creatur-db.mysql.database.azure.com; User Id=creatur_db_main@creatur-db; Password={my_password}

But none of them worked.

The same exception occurs when I create new connection using Server Explorer in Visual Studio 2013. It seems the error has something to do with .NET Connector. I tried to use different versions of MySQL.Data.dll, .NET Framework and Visual Studio but no luck.

I remember having the same problem. It is the .Net connector. — Frank Boucher
According to your mentioned The connection string may not be right. exception, it seems that the userId format is not correct. But accroding to your code and userId, it is right format. So it is very odd. I also try your code on my side, it works correctly. I use the VS 2017, net framework 4.6.1 .and library MySql.Data . — Tom Sun - MSFT
@n-necropoliess it was with PowerBi and MySql was on its first day... Now it works. Try to update your references. I wrote you a sample. — Frank Boucher

Frank Boucher Frank Boucher · Accepted Answer · 2018-04-02T09:49:15

I just created a console application using VS2017 I used Nuget package MySql.Data and .Net Framework 4.6.1

It works perfectly here What I did.

After Creating the MySQL server I used the CloudShell to connect to the SERVER (not a database).

Using this code:

mysql --host franktest.mysql.database.azure.com --user frank@franktest -p

I got an error

ERROR 9000 (HY000): Client with IP address '40.76.202.47' is not allowed to connect to this MySQL server.

So I add that IP and at the same time my IP from where I'm connected.

So once the IP were saved. I executed the previous command, and this time it worked perfectly. I created a database named: frankdemo using this command:

CREATE DATABASE frankdemo;

Then, back in VisualStudio used this code as my Main method, copied from the documentation.

static void Main(string[] args)
    {
        var builder = new MySqlConnectionStringBuilder
        {
            Server = "franktest.mysql.database.azure.com",
            Database = "frankdemo",
            UserID = "frank@franktest",
            Password = "gr3enRay14!",
            SslMode = MySqlSslMode.Required,
        };

        using (var conn = new MySqlConnection(builder.ConnectionString))
        {
            Console.WriteLine("Opening connection");
            conn.Open();

            using (var command = conn.CreateCommand())
            {
                command.CommandText = "DROP TABLE IF EXISTS inventory;";
                command.ExecuteNonQuery();
                Console.WriteLine("Finished dropping table (if existed)");

                command.CommandText = "CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);";
                command.ExecuteNonQuery();
                Console.WriteLine("Finished creating table");

                command.CommandText = @"INSERT INTO inventory (name, quantity) VALUES (@name1, @quantity1),
                    (@name2, @quantity2), (@name3, @quantity3);";
                command.Parameters.AddWithValue("@name1", "banana");
                command.Parameters.AddWithValue("@quantity1", 150);
                command.Parameters.AddWithValue("@name2", "orange");
                command.Parameters.AddWithValue("@quantity2", 154);
                command.Parameters.AddWithValue("@name3", "apple");
                command.Parameters.AddWithValue("@quantity3", 100);

                int rowCount = command.ExecuteNonQuery();
                Console.WriteLine(String.Format("Number of rows inserted={0}", rowCount));
            }

            // connection will be closed by the 'using' block
            Console.WriteLine("Closing connection");
        }

        Console.WriteLine("Press RETURN to exit");
        Console.ReadLine();
    }

Runed it and it works

The documentation I'm referring to is:

Cannot connect to Azure MySQL database from .NET Connector

1 Answers