Implementing Login Attempt Blocking and Unblocking in ASP.NET Using C# and SQL Server

Implementing a login attempt blocking feature in your ASP.NET web application can be a useful way to prevent brute-force attacks and unauthorized access attempts. In this article, we'll show you how to implement such a feature using C# and SQL Server.

To get started, we'll need to create a SQL Server database to store our login attempt data. Here's an example of a simple database schema that we can use:

CREATE TABLE LoginAttempts (
    UserId int NOT NULL,
    AttemptCount int NOT NULL,
    LastAttemptTime datetime NOT NULL,
    CONSTRAINT PK_LoginAttempts PRIMARY KEY (UserId)
);

This table will store the username of each login attempt and the time that the attempt was made. We'll use this information to determine whether a user should be blocked from further login attempts.

Here's an example of the complete C# code for implementing the login attempt blocking and unblocking mechanism using ASP.NET and a SQL Server database:

using System;
using System.Configuration;
using System.Data.SqlClient;
public partial class Login : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack) {
            int userId = // get the user's ID from the input form
            string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);
            string query = "SELECT AttemptCount, LastAttemptTime, BlockedTime FROM LoginAttempts WHERE UserId = @UserId";
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@UserId", userId);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            int attemptCount = 0;
            DateTime lastAttemptTime = DateTime.MinValue;
            DateTime? blockedTime = null;
            if (reader.Read()) {
                attemptCount = (int)reader["AttemptCount"];
                lastAttemptTime = (DateTime)reader["LastAttemptTime"];
                if (!reader.IsDBNull(reader.GetOrdinal("BlockedTime"))) {
                    blockedTime = (DateTime)reader["BlockedTime"];
                }
            }
            reader.Close();
            bool isBlocked = attemptCount >= 5 && (blockedTime == null || DateTime.Now - blockedTime.Value >= TimeSpan.FromMinutes(5));
            if (isBlocked) {
                query = "UPDATE LoginAttempts SET BlockedTime = @BlockedTime WHERE UserId = @UserId";
                command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@UserId", userId);
                command.Parameters.AddWithValue("@BlockedTime", DateTime.Now);
                command.ExecuteNonQuery();
                Response.Redirect("~/Blocked.aspx");
            }
            else {
                bool isValid = // validate the user's credentials (e.g. using Forms Authentication or Identity)
                if (isValid) {
                    query = "UPDATE LoginAttempts SET AttemptCount = 0, LastAttemptTime = @LastAttemptTime, BlockedTime = NULL WHERE UserId = @UserId";
                    command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@UserId", userId);
                    command.Parameters.AddWithValue("@LastAttemptTime", DateTime.Now);
                    command.ExecuteNonQuery();
                    Response.Redirect("~/Success.aspx");
                }
                else {
                    attemptCount++;
                    query = "UPDATE LoginAttempts SET AttemptCount = @AttemptCount, LastAttemptTime = @LastAttemptTime WHERE UserId = @UserId";
                    command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@UserId", userId);
                    command.Parameters.AddWithValue("@AttemptCount", attemptCount);
                    command.Parameters.AddWithValue("@LastAttemptTime", DateTime.Now);
                    command.ExecuteNonQuery();
                    isBlocked = attemptCount >= 5;
                    if (isBlocked) {
                        query = "UPDATE LoginAttempts SET BlockedTime = @BlockedTime WHERE UserId = @UserId";
                        command = new SqlCommand(query, connection);
                        command.Parameters.AddWithValue("@UserId", userId);
                        command.Parameters.AddWithValue("@BlockedTime", DateTime.Now);
                        command.ExecuteNonQuery();
                        Response.Redirect("~/Blocked.aspx");
                    }
                    else {
                        Response.Redirect("~/Failure.aspx");
                    }
                }
            }
            connection.Close();
        }
    }
    protected void UnblockAccounts()
    {
        string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        SqlConnection connection = new SqlConnection(connectionString);
        string query = "UPDATE LoginAttempts SET BlockedTime = NULL WHERE BlockedTime IS NOT NULL AND @Now - BlockedTime >= @BlockDuration";
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@Now", DateTime.Now);
        command.Parameters.AddWithValue("@BlockDuration", TimeSpan.FromMinutes(5));
        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }
}

Note that this code assumes that you have already set up the database with the LoginAttempts table and the necessary connection string in the web.config file. Here's an example of what the web.config file might look like:

<configuration>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(local);Initial Catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

The code above defines a Page_Load event handler for the login page, which is triggered when the user submits the login form. The code checks the number of login attempts made by the user in the past and blocks the user if they have made more than 5 attempts in the last 5 minutes.

If the user is not blocked, the code validates the user's credentials and either logs them in or increments the attempt count and redirects them to a failure page. If the user is blocked, the code redirects them to a separate "blocked" page.

The code also defines a UnblockAccounts method, which can be called periodically (e.g. once a minute) to unblock any accounts that have been blocked for more than 5 minutes.

You can call the UnblockAccounts method from a separate background task or from a global application event (e.g. Application_Start) to ensure that blocked accounts are unblocked in a timely manner.