I am looking for solution to use connection string without user name and password specified with MariaDB server. I am running app and DB server on same machine so I decided to use unix_socket authentication.
I have two connection strings where neither variant has a password property specified:
a)
string cnn_str = @"
Server=/run/mysqld/mysqld.sock;
Port=3306;
Database=mwsdb;
Protocol=unix;
AllowUserVariables=true;
UseAffectedRows=false;";
b) variant is the same as the first one, only extended with the uid property (inpired by exception)
// appends user name - user that runs the application
// in my case the user name is 'pi'
cnn_str += $"Uid={Environment.UserName};";
Code to connect to the database:
1) Only MySqlConnector
var connection = new MySqlConnection(cnn_str);
using (connection)
{
connection.Open();
}
2) The way how I configure db context service in Program.cs using Pomelo.EF and .UseMySql()
builder.Services.AddDbContext<MwsDbContext>(dbContextOptions => dbContextOptions
/* either 2.1) */ .UseMySql(cnn_str, ServerVersion.AutoDetect(cnn_str))
/* or 2.2) */ // .UseMySql(new MySqlConnection(cnn_str), ServerVersion.AutoDetect(cnn_str))
);
I used simple code (console app) to test conectivity to MariaDB and put results into table.
| Code | Connection string | Result |
|---|---|---|
| 1 | a) | exception |
| 1 | b) | works |
| 2.1 | a) | exception |
| 2.1 | b) | works |
| 2.2 | a) | exception |
| 2.2 | b) | works |
The exeption which is thrown is always the same and says:
fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
MySqlConnector.MySqlException (0x80004005): Access denied for user ''@'localhost'
...full exception output below...
I can see some information:
MySqlConnector.Questions:
MySqlConnection instance over connection string when passing as argument to .UseMySql(cnn_str vs. MySqlConnection).I appreciate any help or explanation thanks in advance.
HW: RaspberryPi 4 Model B
SW:
Application: .NET 6 WEB API and ConsoleApp (both tested) with packages:
<PackageReference Include="MySqlConnector" Version="2.1.8" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.1" />
Other details about my project setup:
Create system local user account
sudo useradd -r pi
MariaDB
Socket file path:
root@rpi:~# ls /run/mysqld
mysqld.pid mysqld.sock
Mariadb config file /etc/mysql/my.cnf contains:
[client-server]
socket = /run/mysqld/mysqld.sock
SQL select to locate file path:
MariaDB [(none)]> show variables like 'socket';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| socket | /run/mysqld/mysqld.sock |
+---------------+-------------------------+
1 row in set (0.002 sec)
SQL commands to create user and grant privileges to system user in MariaDB server database:
CREATE USER 'pi'@localhost IDENTIFIED VIA unix_socket;
GRANT ALL PRIVILEGES ON mwsdb.* TO 'pi'@localhost IDENTIFIED VIA unix_socket;
Exception
MySqlConnector.MySqlException (0x80004005): Access denied for user ''@'localhost'
at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int32 startTickCount, ILoadBalancer loadBalancer, IOBehavior ioBehavior, CancellationToken canc>
at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src>
at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/Co>
at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/Co>
at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection>
at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 406
at MySqlConnector.MySqlConnection.Open() in /_/src/MySqlConnector/MySqlConnection.cs:line 369
at Microsoft.EntityFrameworkCore.ServerVersion.AutoDetect(String connectionString)
at Program.<>c__DisplayClass0_0.<<Main>$>b__3(DbContextOptionsBuilder dbContextOptions) in /home/Prog>
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass1_0`2.<AddDbContext>b__0(IServiceProvider p, DbContextOptionsBuilder b)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.CreateDbContextOptions[TContext](IServiceProvider applicationServiceProvider, Action`2 optionsAction)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass17_0`1.<AddCoreServices>b__0(IServiceProvider p)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEng>
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEng>
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass2_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetService[T](IServiceProvider provider)
at Program.<Main>$(String[] args) in /home/Program.cs:line 72
Program.cs:line 72
Apply migrations.
l. 72: using MwsDbContext? context = app.Services.CreateScope().ServiceProvider.GetService<MwsDbContext>();
l. 73: context?.Database.Migrate();
Am I able to compose a connection string without login credentials?
No. MySqlConnector requires the UserID to be specified, even if using a Unix socket. This is covered in this issue.
The workaround is to set the username explicitly:
var builder = new MySqlConnectionStringBuilder("...connection string...");
builder.UserID = Environment.UserName;
using var connection = new MySqlConnection(builder.ConnectionString);
(Or just hard-code ;Username = pi in your connection string.)
explain to me what pros and potential cons has
MySqlConnectioninstance over connection string when passing as argument to.UseMySql(cnn_str vs. MySqlConnection).
The two methods seem pretty equivalent: connection string vs connection.
One uses the provided connection string; the other reads the DbConnection.ConnectionString property and uses that connection string. The former would be slightly more efficient because it's not creating an extra MySqlConnection object just to pass the connection string in; this is likely negligible in practice.