How to properly connect MySQL database with C# application?

Question

I am trying to connect my MySQL database with C# but it's not getting connected.

I am using

static string connstring = @"server=my.live.ip;userid=user;password=123;database=db_name;port=3306";

but still I am getting

Authentication to host 'my.live.ip' for user 'user' using method 'mysql_native_password' failed with message: Access denied for user 'user'@'202.xxx.xxx.xxx' (using password: NO)`

I have searched on it but didn't find any suitable solution(s).

P.S: The live IP that I am using is of azure. i.e. MySQL database is hosted on azure server via xampp

Any help would be highly appreciated

Answer 1

Try this:

const string connStr = "Server=my.live.ip; User=user; Database=db_name; Password=123; port=3306; 
    Allow Zero Datetime=true; Convert Zero Datetime=true;"; 

Answer 2

Does your password start with # or similar character and you are storing it in some config file like ini

As some config types treat some of the special characters as either comments or something else, password becomes null.

To verify, change your password to alphanumeric once and test. I faced this with my passwords when I used like password=#strongP

Answer 3

using System;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace AzureMySqlExample
{
    class MySqlCreate
    {
        static async Task Main(string[] args)
        {
            var builder = new MySqlConnectionStringBuilder
            {
                Server = "YOUR-SERVER.mysql.database.azure.com",
                Database = "YOUR-DATABASE",
                UserID = "USER@YOUR-SERVER",
                Password = "PASSWORD",
                SslMode = MySqlSslMode.Required,
            };

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

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

                    command.CommandText = "CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);";
                    await command.ExecuteNonQueryAsync();
                    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 = await command.ExecuteNonQueryAsync();
                    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();
        }
    }
}

you can refer the link below to create and access mysql db using c# https://learn.microsoft.com/en-us/azure/mysql/connect-csharp

Answer 4

Couple of possible things to try -

1. Match your server name in the connection string against what is set on Azure portal.
2. Looks like the username and password you mentioned are dummy ones (understandably so). Check if your actual user name or password have any special characters, you will have to encode them appropriately to escape.

For example, if your password is my>Pass, connection string should look like this. static string connstring = @"server=servername;userid=user;password=my>Pass;database=db_name;port=3306";

Here is a full list if you would like to explore.

Answer 5

You can just look it up but here’s come code anyway that I found:

Replace all port # usernames and password database name and server ip with what you need.

Code:

using System;
using System.Windows;
using MySql.Data.MySqlClient;


namespace Deportes_WPF
{

public partial class Login : Window
{
private MySqlConnection connection;
private string server;
private string database;
private string user;
private string password;
private string port;
private string connectionString;
private string sslM;

public Login()
{
    InitializeComponent();

    server = "server_name";
    database = "database_name";
    user = "user_id";
    password = "password";
    port = "3306";
    sslM = "none";

    connectionString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);

    connection = new MySqlConnection(connectionString);
}

private void conexion()
{
    try
    {
        connection.Open();

        MessageBox.Show("successful connection");

        connection.Close();
    }
    catch (MySqlException ex)
    {
        MessageBox.Show(ex.Message + connectionString);
    }
}

private void btn1_Click(object sender, RoutedEventArgs e)
{
    conexion();
}
}

}

I hope this code works for you.

It also looks like for the error you gave me that the username or password is invalid.

Answer 6

(using password: NO) says that no password was provided. I am not familiar with the connection syntax for C#, but I would suspect that something is wrong with

static string connstring =
     @"server=my.live.ip;userid=user;password=123;database=db_name;port=3306";

Or perhaps connstring is not being used.

Also check the MySQL side. Do SHOW GRANTS FOR 'user'@'202%' or maybe SHOW GRANTS FOR 'user'@'202.xxx.xxx.xxx', depending on whether you used '202%' or '202.xxx.xxx.xxx' as the "host".

You should get back something like

GRANT ... ON dbname.* TO 'user'@'202%' WITH AUTHENTICATION 'mysql_native_password';

Note: having a hostname versus an IP address may be an issue.

Answer 7

using System;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace AzureMySqlExample
{
class MySqlCreate
{
    static async Task Main(string[] args)
    {
        var builder = new MySqlConnectionStringBuilder
        {
            Server = "YOUR-SERVER.mysql.database.azure.com",
            Database = "YOUR-DATABASE",
            UserID = "USER@YOUR-SERVER",
            Password = "PASSWORD",
            SslMode = MySqlSslMode.Required,
        };

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

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

                command.CommandText = "CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);";
                await command.ExecuteNonQueryAsync();
                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 = await command.ExecuteNonQueryAsync();
                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();
    }
}

}