0

I am bringing up an ASP.NET Core MVC (ASP.NET 7.0) application and on my development environment I was so far successfully working with a local instance of mysql (MySQL server version 8.0.32), but using the root user, with no password. I decided to get to something more safe and I setup a new user using MySQLWorkbench. When it comes to the ASP.NET application, though, I always get a

"Access denied for user 'myuser'@'localhost' (using password: NO)"

For the connection string, as I have two different schemas, I wrote a simple helper function that takes the data from the appsettings.Development.json file and just adds the password that is retrieved (successfully) from dotnet's secret storage.

So the appsettings.Development.json file looks like this:


{
    "ConnectionStrings": {
        "IdentityDataContextConnection": "Server=localhost;Database=application-identity;user=myuser;",
        "ApplicationDataContextConnection": "Server=localhost;Database=application;user=myuser;"
    }

}

The secrets.json file looks like this:

{
    "AppDBPassword": "myUserPassword"
}

and finally the user function just looks like this (so it reads the config in appsettings.Developments.json, then it instantiates the MySqlConnectionStringBuilder object from MySqlConnector and it adds to it the password:

public static String GetSecureConnectionString(WebApplicationBuilder builder, String connectionName)
{
    var connectionString = builder.Configuration.GetConnectionString(connectionName);

    if (connectionString == null)
        throw new InvalidOperationException($"Connection string {connectionName} not found.");

    var connectionStringBuilder = new MySqlConnectionStringBuilder(connectionString);
    connectionStringBuilder.Password = builder.Configuration["AppDBPassword"];

    return connectionStringBuilder.ToString();
}

Indeed, the string I get seems to be OK:

"Server=localhost;User ID=myuser;Password=myUserPassword;Database=application-identity"

The first thing I tried was to exclude that the user had issues (saw here on SO that for instance localhost and 127.0.0.1 are not the same for MySQL) and indeed I manage to connect using either the command line via the command "mysql -u myuser -p" (and then entering the password) and also via a client such as DBEaver.

What else should I do?

EDIT: It seems that this is related to ConnectionString loses password after connection.Open, but even if I add the following instruction to "GetSecureConnectionString":

connectionStringBuilder.PersistSecurityInfo = true;

nothing changes :(

mikap83
  • 23
  • 4

1 Answers1

0

OK, found the solution. Thanks a lot to https://stackoverflow.com/users/5741643/poul-bak and his answer in https://stackoverflow.com/a/69832754/2073934

I set a base connection in the connection strings, without database:

"ConnectionStrings": {
    "BaseConnection": "Server=localhost;user=myuser;"
  }

and then used the interceptor class + override of the OnConfigure method of each DbContext to achieve the usage of the same connection, with switching to the desired schema

mikap83
  • 23
  • 4