What is the procedure for using SQL Server database for Basic Authentication in ASP.NET Core Web API without Entity Framework?

edit

The procedure for using SQL Server database for Basic Authentication in ASP.NET Core Web API without Entity Framework involves the following steps:

1. Create a new ASP.NET Core Web API project.
2. Add the required NuGet packages: Microsoft.AspNetCore.Authentication.Basic and Microsoft.Extensions.Configuration.Json.
3. In the appsettings.json, add a connection string for the SQL Server database.
4. Create a new interface for the user repository with methods for authentication and authorization.
5. Implement the user repository interface with SQL Server database connectivity code.
6. In the Startup.cs, configure basic authentication using AddAuthentication() and AddBasic() methods, and set the User Repository as the authentication provider.
7. Use the [Authorize] attribute in the controller or method to enforce authentication.

Here's sample code for the user repository interface:

public interface IUserRepository
{
    bool Authenticate(string username, string password);
    bool Authorize(string username, string role);
}

And here's sample code for its implementation:

public class UserRepository : IUserRepository
{
    private readonly string connectionString;

    public UserRepository(IConfiguration configuration)
    {
        connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public bool Authenticate(string username, string password)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand("SELECT Password FROM Users WHERE Username=@Username", connection))
            {
                command.Parameters.AddWithValue("@Username", username);

                string dbPassword = (string)command.ExecuteScalar();

                if (dbPassword == null)
                    return false;

                return BCrypt.Net.BCrypt.Verify(password, dbPassword);
            }
        }
    }

    public bool Authorize(string username, string role)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Users WHERE Username=@Username AND Role=@Role", connection))
            {
                command.Parameters.AddWithValue("@Username", username);
                command.Parameters.AddWithValue("@Role", role);

                int count = (int)command.ExecuteScalar();

                return count > 0;
            }
        }
    }
}

In the Startup.cs, configure basic authentication as follows:

public void ConfigureServices(IServiceCollection services)
{
    ...

    services.AddAuthentication("BasicAuthentication")
        .AddBasic(options =>
        {
            options.Realm = "My API";
            options.Events = new BasicAuthenticationEvents
            {
                OnValidateCredentials = context =>
                {
                    var userRepository = context.HttpContext.RequestServices.GetService<IUserRepository>();

                    if (userRepository.Authenticate(context.UserName, context.Password))
                    {
                        List<Claim> claims = new List<Claim>
                        {
                            new Claim(ClaimTypes.Name, context.UserName),
                            new Claim(ClaimTypes.Role, "Admin")
                        };

                        context.Principal = new ClaimsPrincipal(new ClaimsIdentity(claims, "Basic"));
                        context.Success();
                    }
                    else
                    {
                        context.Fail("Invalid credentials.");
                    }

                    return Task.CompletedTask;
                }
            };
        });

    services.AddScoped<IUserRepository, UserRepository>();

    ...
}

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    ...

    app.UseRouting();

    app.UseAuthentication();
    app.UseAuthorization();

    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllers().RequireAuthorization();
    });

    ...
}

Finally, enforce authorization using the [Authorize] attribute in the controller or method as follows:

[ApiController]
[Route("[controller]")]
public class TestController : ControllerBase
{
    [HttpGet]
    [Authorize(Roles = "Admin")]
    public IActionResult Get()
    {
        return Ok("Authorized.");
    }
}