Resolved: How to implement ASP.NET Core Web API Basic Authentication using SQL Server database without Entity Framework

In this post, we will see how to resolve How to implement ASP.NET Core Web API Basic Authentication using SQL Server database without Entity Framework

Question:

In my ASP.NET Core Web API I have a basic authentication handler defined in BasicAuthenticationHandler.cs like this:

using MyAPI.Interfaces;
using Microsoft.AspNetCore.Authentication;
using Microsoft.Extensions.Options;
using System.Security.Claims;
using System.Text.Encodings.Web;
using System.Text;

namespace MyAPI.Data
{
    public class BasicAuthenticationHandler : AuthenticationHandler<AuthenticationSchemeOptions>
    {
        private readonly IUserRepository _userRepository;

        public BasicAuthenticationHandler(
            IOptionsMonitor<AuthenticationSchemeOptions> options,
            ILoggerFactory logger,
            UrlEncoder encoder,
            ISystemClock clock, IUserRepository userRepository) :
           base(options, logger, encoder, clock)
        {
            _userRepository = userRepository;
        }

        protected async override Task<AuthenticateResult> HandleAuthenticateAsync()
        {
            var authorizationHeader = Request.Headers["Authorization"].ToString();

            if (authorizationHeader != null && authorizationHeader.StartsWith("basic", StringComparison.OrdinalIgnoreCase))
            {
                var token = authorizationHeader.Substring("Basic ".Length).Trim();
                var credentialsAsEncodedString = Encoding.UTF8.GetString(Convert.FromBase64String(token));
                var credentials = credentialsAsEncodedString.Split(':');

                if (await _userRepository.Authenticate(credentials[0], credentials[1]))
                {
                    var claims = new[] { new Claim("name", credentials[0]), new Claim(ClaimTypes.Role, "Admin") };
                    var identity = new ClaimsIdentity(claims, "Basic");
                    var claimsPrincipal = new ClaimsPrincipal(identity);
                    return await Task.FromResult(AuthenticateResult.Success(new AuthenticationTicket(claimsPrincipal, Scheme.Name)));
                }
            }

            Response.StatusCode = 401;
            Response.Headers.Add("WWW-Authenticate", "Basic realm=\".com\"");

            return await Task.FromResult(AuthenticateResult.Fail("Invalid Authorization Header"));
        }
    }
}

IUserRepository is this:

using MyAPI.Models;

namespace MyAPI.Interfaces
{
    public interface IUser
    {
        public int Id { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
    }

    public interface IUserRepository
    {
        Task<bool> Authenticate(string username, string password);
    }
}

This is User.cs:

using MyAPI.Interfaces;

namespace MyAPI.Models
{
    public class User : IUser
    {
        private string _username;
        private string _password;

        public User()
        {
            _username = String.Empty;
            _password = String.Empty;
        }

        public int Id { get; set; }
        public string Username { get => _username; set => _username = value; }
        public string Password { get => _password; set => _password = value; }
    }
}

Here’s my UserRepository:

using MyAPI.Interfaces;

namespace MyAPI.Models
{
    public class UserRepository : IUserRepository
    {
        private List<User> _users = new List<User>
        {
            new User
            {
                Id = 1, Username = "user1", Password = "password1"
            },
            new User
            {
                Id = 2, Username = "user2", Password = "password2"
            }
        };

        public async Task<bool> Authenticate(string username, string password)
        {
            if (await Task.FromResult(_users.SingleOrDefault(x => x.Username == username && x.Password == password)) != null)
            {
                return true;
            }

            return false;
        }
    }
}

And in Program.cs, I have this:

builder.Services.AddScoped<IUserRepository, UserRepository>();

builder.Services.AddAuthentication("BasicAuthentication")
                .AddScheme<AuthenticationSchemeOptions, BasicAuthenticationHandler>("BasicAuthentication", null);

This works perfectly well for the given hard-code users, but now I want to have the List<User> to be filled from a database table, without using Entity Framework.
I have an appsettings.json file containing this connection string:

"ConnectionStrings": {
    "Users": "Data Source=.\SQLEXPRESS;Initial Catalog=Users;Trusted_Connection=True;"
},

but I have no DbContext descendent. Nor do I want one.
What I want to do, at some point in Program.cs, is load the records from the Users.dbo.UserList table into the List<User> property of the UserRepository instead of using the locally initialised version, but I just can’t seem to work out the dependency injections, the scopes, or the initialisations for it to work.
EDIT 31/3
Based on Jerry’s answer, I updated UserRepository.cs to this:

using MyAPI.Interfaces;
using Microsoft.Data.SqlClient;

namespace MyAPI.Models
{
    public class UserRepository : IUserRepository
    {
        public List<User> Users = new();

        private readonly IConfiguration _configuration;
        private readonly string _connectionstring;

        public UserRepository(IConfiguration configuration)
        {
            _configuration = configuration;
            _connectionstring = _configuration.GetConnectionString("Users") ?? string.Empty;

            try
            {
                using (SqlConnection conn = new(_connectionstring))
                {
                    conn.Open();

                    string sql = "SELECT * FROM UserList";
                    using (SqlCommand command = new SqlCommand(sql, conn))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var user = new User();

                                user.UserNo = reader.GetInt32(0);
                                user.UserName = reader.GetString(1).Trim();
                                user.Password = reader.GetString(2).Trim();

                                Users.Add(user);
                            }
                        }
                    }

                    conn.Close();
                }
            }
            catch (Exception)
            {
                // Ignore it for now
                // throw;
            }
        }

        public async Task<bool> Authenticate(string userName, string password)
        {
            if (await Task.FromResult(
                Users.SingleOrDefault(x => x.UserName.Equals(userName, StringComparison.OrdinalIgnoreCase) && x.Password.Equals(password, StringComparison.OrdinalIgnoreCase))
            ) != null)
            {
                return true;
            }
            return false;
        }
    }
}

This includes DI for the configuration to get the connection string and works fine. The only downside is that the constructor for UserRepository is called for every API request, which is a lot of database activity when the API is in heavy use.
What I intend to do is have a single API method for generating a token that uses Basic Authentication, and for all other methods to use Bearer Token Authentication instead. The token will have a set lifetime, after which Basic Authentication has to be used again to create a new token.

Best Answer:

You can change the UserRepository.cs like below. It reads the database without efcore and initialize the value of _user in construct function.

    public class UserRepository : IUserRepository
    {
        private List<User> _users = new List<User>
        {
        };
        public UserRepository()
        {
            using (SqlConnection connection = new SqlConnection("Server=192.168.2.68;Database=Users;User Id=sa;Password=xxxxx;"))
            {
                connection.Open();

                string sql = "SELECT * FROM UserList";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {

                            _users.Add(new User { Id = reader.GetInt32(0), Username = reader.GetString(1), Password = reader.GetString(2) });
                        }
                    }
                }

                connection.Close();
            }
        }

        public async Task<bool> Authenticate(string username, string password)
        {
            if (await Task.FromResult(_users.SingleOrDefault(x => x.Username == username && x.Password == password)) != null)
            {
                return true;
            }

            return false;
        }
    }

My testing database is like

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com

Resolved: How to implement ASP.NET Core Web API Basic Authentication using SQL Server database without Entity Framework

Resolved: How to stop a thread that has a blocking function from easygui in python

Resolved: Removing null keys from a json array of objects

Resolved: How can I generate at compile-time a separate OpenAPI Swagger.json file for each Controller in my ASP.NET project?

Question:

Best Answer:

Related Posts

Resolved: How to stop a thread that has a blocking function from easygui in python

Resolved: Removing null keys from a json array of objects

Resolved: How can I generate at compile-time a separate OpenAPI Swagger.json file for each Controller in my ASP.NET project?