사용자 "@localhost"에 대한 액세스가 거부되었습니다(비밀번호: no 사용).

사용자 "@localhost"에 대한 액세스가 거부되었습니다(비밀번호: no 사용).

안녕, 이 글을 읽고 있는 여러분.현재 마리아DB 데이터베이스를 문제없이 작동하는 라즈베리 파이로 포팅하고 있습니다.단, C# ASP를 포팅하면Pi에 대한 Net Core Backend에서도 다음 오류 메시지가 나타납니다.

 Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
      Route matched with {action = "Validate", controller = "User"}. Executing controller action with signature System.Threading.Tasks.Task`1[Microsoft.AspNetCore.Mvc.JsonResult] Validate(feedback_app_backend.Models.User) on controller feedback_app_backend.Controllers.UserController (feedback_app_backend).
Access denied for user ''@'localhost' (using password: NO)
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
      Executed action feedback_app_backend.Controllers.UserController.Validate (feedback_app_backend) in 783.1495ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint 'feedback_app_backend.Controllers.UserController.Validate (feedback_app_backend)'
fail: Microsoft.AspNetCore.Server.Kestrel[13]
      Connection id "0HM0IVJDTQRVM", Request id "0HM0IVJDTQRVM:00000001": An unhandled exception was thrown by the application.
MySql.Data.MySqlClient.MySqlException (0x80004005): Access denied for user ''@'localhost' (using password: NO)
   at feedback_app_backend.Controllers.UserController.Validate(User user) in C:\Users\NoChance\Desktop\feedback_app_backend\Controllers\UserController.cs:line 88
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 1351.7589ms 500

내 연결 문자열에서 사용자가 올바르게 읽지 않은 것 같습니다.이 문제는 없습니다.PC에서 백엔드를 호스트 할 때 pi의 백엔드에 접속하려고 할 때만 발생합니다.왜 그런지 알아요?

appsettings.json에 있는 연결 문자열입니다.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=feedbackapp;User Id=root;Password=mypass"
  },
  "AllowedHosts": "*"
}

다른 연결 스트링을 시도했지만 모두 작동하지 않는 것 같습니다.

편집: 연결 문자열을 다음과 같이 업데이트했습니다.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=3306;Database=feedbackapp;Uid=root;Pwd=mypass;"
  },
  "AllowedHosts": "*"
}

같은 에러가 아직 표시됩니다./

다음은 내 연결 구조입니다.

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=3307;Database=feedbackapp;Uid=root;Pwd=mypass;"
  },
  "AllowedHosts": "*"
}

사용자 쿼리

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Threading.Tasks;
using feedback_app_backend.Disposables;
using feedback_app_backend.Models;
using MySql.Data.MySqlClient;

namespace feedback_app_backend.Querys
{
    public class UserQuery
    {
        public UserDB Db { get; }

        public UserQuery(UserDB db)
        {
            Db = db;
        }

        public async Task<User> AddUserAsync(User user, CustomQuestionnaireDB questionnaireDB, FeedbackQuestionDB questionDB)
        {
            try
            {
                await questionnaireDB.Connection.OpenAsync();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            CustomQuestionnaireQuery query = new CustomQuestionnaireQuery(questionnaireDB);
            User userExists = await GetUserAsync(user, questionnaireDB, questionDB).ConfigureAwait(true);

            if (userExists == null)
            {
                //Get Questionnaire ID
                CustomQuestionnaire questionnaire = new CustomQuestionnaire();
                questionnaire.QuestionnareID = await query.GetNewQuestionnaireIDAsync();

                User newUser = new User();
                //Remove all null values
                newUser = user;
                newUser.QuestionnaireID = questionnaire.QuestionnareID;
                newUser.Given = new List<CustomQuestionnaire>();
                newUser.Received = new List<CustomQuestionnaire>();
                newUser.UUID = Guid.NewGuid().ToString();

                //Prepare the database
                using var cmd = Db.Connection.CreateCommand();
                cmd.CommandText = @"INSERT INTO user (uuid, first_name, last_name, email, pass, title, questionnaire_id, created_on) 
                VALUES (@uuid, @first_name, @last_name, @email, @password, @title, @questionnaire_id, @created_on);";
                BindParams(cmd, newUser);
                await cmd.ExecuteNonQueryAsync();

                questionnaire.UUID = newUser.UUID;

                FeedbackQuestionQuery questionQuery = new FeedbackQuestionQuery(questionDB);
                int index = await query.CreateQuestionnaire(questionnaire, questionQuery);

                if (index != -1)
                {
                    newUser.CustomQuestionnaire = questionnaire;
                    await UpdateAsync(newUser);
                    return newUser;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return userExists;
            }

        }

        public async Task<User> GetUserAsync(User user, CustomQuestionnaireDB queryDB, FeedbackQuestionDB questionDB)
        {

            try
            {
                await queryDB.Connection.OpenAsync();
            }
            catch (Exception)
            {
                Console.WriteLine("Connection is already open (UserQuery 80)");
            }
            CustomQuestionnaireQuery query = new CustomQuestionnaireQuery(queryDB);

            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"SELECT uuid,first_name,last_name,email,title,questionnaire_id FROM user WHERE `email` = @email AND `pass` = @pass";
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@email",
                DbType = DbType.String,
                Value = user.EMail,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@pass",
                DbType = DbType.String,
                Value = user.Password,
            });
            List<User> users = await ReadAllAsync(await cmd.ExecuteReaderAsync());

            //Check if any users where found
            if (users.Count > 0)
            {
                User _user = users[0];
                CustomQuestionnaire questionnaire = await query.GetCustomQuestionnaireByUUIDAsync(_user);
                //Check if user already has a questionnaire
                if (questionnaire != null)
                {
                    //Load in all Questions
                    questionnaire.Questions = await query.GetAllQuestionnaireQuestions(questionnaire, questionDB);
                    _user.CustomQuestionnaire = questionnaire;

                    //Set received feedback
                    List<CustomQuestionnaire> received = await query.GetReceivedQuestionnairesAsync(_user, questionDB);
                    if (received != null)
                    {
                        _user.Received = received;
                    }
                }
                return _user;
            }
            else
            {
                return null;
            }


        }

        public async Task<User> GetUserByUUIDAsync(String uuid, CustomQuestionnaireDB queryDB, FeedbackQuestionDB questionDB)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"SELECT uuid,first_name,last_name,email,title,questionnaire_id FROM user WHERE `uuid` = @uuid;";
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@uuid",
                DbType = DbType.String,
                Value = uuid,
            });

            List<User> users = await ReadAllAsync(await cmd.ExecuteReaderAsync());
            CustomQuestionnaireQuery query = new CustomQuestionnaireQuery(queryDB);
            if (users.Count > 0)
            {
                User _user = users[0];
                CustomQuestionnaire questionnaire = await query.GetCustomQuestionnaireByUUIDAsync(_user);
                if (questionnaire != null)
                {
                    questionnaire.Questions = await query.GetAllQuestionnaireQuestions(questionnaire, questionDB);
                    _user.CustomQuestionnaire = questionnaire;
                }
                return _user;
            }
            else
            {
                return null;
            }
        }


        public async Task<User> GetUserByEMailAsync(String email, CustomQuestionnaireDB queryDB, FeedbackQuestionDB questionDB)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"SELECT uuid,first_name,last_name,email,title,questionnaire_id FROM user WHERE `email` = @email;";
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@email",
                DbType = DbType.String,
                Value = email,
            });

            List<User> users = await ReadAllAsync(await cmd.ExecuteReaderAsync());
            CustomQuestionnaireQuery query = new CustomQuestionnaireQuery(queryDB);
            if (users.Count > 0)
            {
                User _user = users[0];
                CustomQuestionnaire questionnaire = await query.GetCustomQuestionnaireByUUIDAsync(_user);
                if (questionnaire != null)
                {
                    questionnaire.Questions = await query.GetAllQuestionnaireQuestions(questionnaire, questionDB);
                    _user.CustomQuestionnaire = questionnaire;
                }
                return _user;
            }
            else
            {
                return null;
            }
        }

        public async Task<List<User>> GetAllUsersAsync(User user)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"SELECT uuid, first_name, last_name, email, title, questionnaire_id FROM user WHERE `email` <> @email";
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@email",
                DbType = DbType.String,
                Value = user.EMail,
            });
            List<User> result = await ReadAllAsync(await cmd.ExecuteReaderAsync());
            return result.Count > 0 ? result : null;
        }

        private async Task<List<User>> ReadAllAsync(DbDataReader reader)
        {
            List<User> users = new List<User>();
            using (reader)
            {
                while (await reader.ReadAsync())
                {
                    var user = new User()
                    {
                        UUID = reader.GetString(0),
                        FirstName = reader.GetString(1),
                        LastName = reader.GetString(2),
                        EMail = reader.GetString(3),
                        Title = reader.GetString(4),
                        QuestionnaireID = reader.GetInt32(5)
                    };
                    users.Add(user);
                }
            }
            return users;
        }

        public async Task UpdateAsync(User user)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"UPDATE user SET `first_name` = @first_name, `last_name` = @last_name, `email` = @email, `title` = @title, "
            + "`questionnaire_id` = @questionnaire_id, `created_on` = @created_on WHERE `uuid` = @uuid;";
            BindParams(cmd, user);
            await cmd.ExecuteNonQueryAsync();
        }

        public async Task DeleteUserAsync(User user)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"DELETE FROM user WHERE `uuid` = @uuid;";
            BindParams(cmd, user);
            await cmd.ExecuteNonQueryAsync();
        }

        private void BindParams(MySqlCommand cmd, User user)
        {
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@uuid",
                DbType = DbType.String,
                Value = user.UUID,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@first_name",
                DbType = DbType.String,
                Value = user.FirstName,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@last_name",
                DbType = DbType.String,
                Value = user.LastName,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@email",
                DbType = DbType.String,
                Value = user.EMail,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@password",
                DbType = DbType.String,
                Value = user.Password,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@title",
                DbType = DbType.String,
                Value = user.Title,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@questionnaire_id",
                DbType = DbType.Int32,
                Value = user.QuestionnaireID,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@created_on",
                DbType = DbType.Int64,
                Value = user.CreatedOn,
            });
        }


    }
}

사용자 데이터베이스

using System;
using MySql.Data.MySqlClient;

namespace feedback_app_backend.Disposables
{
    public class UserDB : IDisposable
    {
        public MySqlConnection Connection { get; }
        public UserDB(string connectionString)
        {
            Connection = new MySqlConnection(connectionString);
        }

        public void Dispose() => Connection.Dispose();
    }
}

Startup.cs -> Configure Service

public void ConfigureServices(IServiceCollection services)
{
    services.AddSingleton<IConfiguration>(Configuration);
    services.AddControllers();
    services.AddTransient<UserDB>(_ => new UserDB(Configuration["ConnectionStrings:DefaultConnection"]));
    services.AddTransient<CustomQuestionnaireDB>(_ => new CustomQuestionnaireDB(Configuration["ConnectionStrings:DefaultConnection"]));
    services.AddTransient<FeedbackQuestionDB>(_ => new FeedbackQuestionDB(Configuration["ConnectionStrings:DefaultConnection"]));
}

EDIT 3: 조금 더 컨텍스트를 위해 오류를 업데이트했습니다.

편집 4:

앱 설정에서 연결 문자열을 읽을 때 로컬로 작동하지만 연결 문자열을 문자열로 직접 넣으면 오류를 재현할 수 있습니다(appsettings.json에서 읽지 않음).이렇게 하면 내 산딸기와 같은 오류가 발생합니다.

public void ConfigureServices(IServiceCollection services)
{
    services.AddSingleton<IConfiguration>(Configuration);
    services.AddControllers();
    services.AddTransient<UserDB>(_ => new UserDB(Configuration["Server=localhost;Port=3307;Database=feedbackapp;Uid=root;Pwd=mypass;"]));
    services.AddTransient<CustomQuestionnaireDB>(_ => new CustomQuestionnaireDB(Configuration["Server=localhost;Port=3307;Database=feedbackapp;Uid=root;Pwd=mypass;"]));
    services.AddTransient<FeedbackQuestionDB>(_ => new FeedbackQuestionDB(Configuration["Server=localhost;Port=3307;Database=feedbackapp;Uid=root;Pwd=mypass;"]));
}

드디어 고쳤어요.appsettings.json 부분에서 판독치를 삭제하고 db를 이렇게 조정했습니다.

using System;
using MySql.Data.MySqlClient;

namespace feedback_app_backend.Disposables
{
    public class CustomQuestionnaireDB : IDisposable
    {
        MySqlConnection conn = new MySqlConnection("server=localhost;user id=user;password=mypass;database=mydb");
        public MySqlConnection Connection { get; }
        public CustomQuestionnaireDB()
        {
            Connection = new MySqlConnection(conn.ConnectionString);
        }

        public void Dispose() => Connection.Dispose();
    }
}

언급URL : https://stackoverflow.com/questions/62436757/access-denied-for-user-localhost-using-password-no