Publishing ASP.NET Core MySQL Entity Framework Migrations to a remote machine fails

[fox-15]

Good afternoon!

MySQL version: MariaDB 11.2.1 Operating system: Windows Server 2019 Datacenter Pomelo.EntityFrameworkCore.MySql version: 7.0.0 (01/17/2023) Microsoft.EntityFrameworkCore.Tools version: 7.0.11 (12.09.2023)

I am using MariaDB 11.2.1 (MySQL) database server and Pomelo.EntityFrameworkCore.MySql 7.0.0 (01/17/2023) Connection string: server=localhost;user=root;password=1;database=DataBASE; I can log into MySQL manager HeidiSQL or DBeaver with root account and password 1, my ASP.NET Core application works great, connection string server=localhost;user=root;password=1;database=DataBASE; works too, yes access to the data meringue DataBASE data is added, modified and deleted as required. Migrations that make changes to the database architecture work well on your local machine.

But if I start to Publish an application to a remote server using "Entity Framework Migrations" then an error occurs when creating the database. I also use MariaDB 11.2.1 (MySQL) with the MSSQL Server database and connection string: Server=.;Database=MSDATABASE;User ID=user;Password=1;MultipleActiveResultSets=true;Encrypt=False;, and publishing works fine for it.

But why the error for MySQL? How to solve this problem:

An error occurred while logging in for user "root". Cause: A login name matching the passed name was not found. [CLIENT: <local machine>]

ERROR:
The Web Deploy task failed. An error occurred while processing the request on the remote computer.
An error occurred while processing the request on the remote computer.
Failed to create database "TelePoolDB". ---> System.Exception: An error occurred while logging in for user "root".
   in System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   in System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   in System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   in System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   in System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   in System.Data.SqlClient.SqlConnection.Open()
   in Microsoft.Web.Deployment.SqlServerDatabaseProvider.AddHelper(DeploymentObject source)
   --- End of inner exception stack trace --- 
   in Microsoft.Web.Deployment.DeploymentObject.HandleRetryException(DeploymentSyncContext syncContext, Int32& attempts, Boolean& retry, DeploymentRetryException ex, DeploymentOperationKind operationKind)
   in Microsoft.Web.Deployment.DeploymentObject.Add(DeploymentObject source, DeploymentSyncContext syncContext)
   in Microsoft.Web.Deployment.DeploymentSyncContext.HandleAdd(DeploymentObject destObject, DeploymentObject sourceObject)
   in Microsoft.Web.Deployment.DeploymentSyncContext.HandleUpdate(DeploymentObject destObject, DeploymentObject sourceObject)
   in Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)
   in Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)
   in Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable, Nullable`1 syncPassId, String syncSessionId)
   in Microsoft.Web.Deployment.DeploymentAgent.HandleSync(DeploymentAgentAsyncData asyncData, Nullable`1 passId, String user, String siteName)
An error occurred while logging in for user "root".
   in System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   in System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
  in System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   in System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   in System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   in System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   in System.Data.SqlClient.SqlConnection.Open()
   in Microsoft.Web.Deployment.SqlServerDatabaseProvider.AddHelper(DeploymentObject source)

[kayamax]

Unless manually changed, the default mariadb/mysql server user "root" is only allowed to log in from localhost, which could be the reason.

[lauxjpn]

@fox-15 From the logs that you posted, it looks like that your Web Deploy task executes some Microsoft SQL Server specific code path that triggers the exception.

I don't see anything using Pomelo or MySQL/MariaDB at all in the logs, only Microsoft SQL Server references. So looks like you get the exception, because the TelePoolDB database does not exist in your SQL Server instance.

[lauxjpn]

@fox-15 Do you need more support with this, or can we close this issue?