none
Limited attempts RRS feed

  • Question

  • I need to limit attempts to connect to SQL Server, for instance, if someone 3 times enters wrong password or login, more

    attempts are not allowed. Write code to insert to my code, please

    using System;
    using System.Windows.Forms;
    
    namespace MinimalApproachExample
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void UserNameTextBox_TextChanged(object sender, EventArgs e)
            {
    
            }
    
            private void checkBox1_CheckedChanged(object sender, EventArgs e)
            {
                if (checkBox1.Checked)
                {
                    PasswordTextBox.UseSystemPasswordChar = true;
                }
                else
                {
                    PasswordTextBox.UseSystemPasswordChar = false;
                }
            }
    
            private void ConnectButton_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(UserNameTextBox.Text) && !string.IsNullOrWhiteSpace(PasswordTextBox.Text))
                {
                    var ops = new DatabaseUser("N10468000115\\SQLHUNTER", "demo");
                    var loginResults = ops.SqlCredentialLogin(UserNameTextBox.Text, PasswordTextBox.Text);
                    if (loginResults)
                    {
                        
                            var successValue = ops.DoWork(UserNameTextBox.Text, PasswordTextBox.Text);
                                            
                        var workResult = string.IsNullOrWhiteSpace(successValue);
                        if(workResult)
                        {
                            MessageBox.Show("Operation is successful");
                        }
                        else
                        {
                            MessageBox.Show(successValue);
                        }
                    }
                    else
                    {
                        MessageBox.Show("login is failed");
                    }
                }
                else
                {
                    MessageBox.Show("Incomplete information to continue");
                }
            }
            
    
            private void CancelButton_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
    

    Tuesday, November 12, 2019 3:22 PM

Answers

  • Hi pavlob,

    Thanks for your feedback.

    It’s my fault.

    I made some minor changes to the code.

            public int failureTimes = 0;
            private void ConnectButton_Click(object sender, EventArgs e)
            {
                // Put the code that limit the number of failures at the beginning of the method
                if (failureTimes >= 3)
                {
                    MessageBox.Show("You have failed 3 times, the account has been locked!");
                    return;
                }
    
                if (!string.IsNullOrWhiteSpace(UserNameTextBox.Text) && !string.IsNullOrWhiteSpace(PasswordTextBox.Text))
                {
                    var ops = new DatabaseUser("localhost", "test");
    
                    var loginResults = ops.SqlCredentialLogin(UserNameTextBox.Text, PasswordTextBox.Text);
                    if (loginResults)
                    {
                        MessageBox.Show("Connection Success.");
                    }
                    else
                    {
                        failureTimes++;
                        MessageBox.Show($"You have failed {failureTimes} time and there are {3 - failureTimes} chances left.");
                        return;
                    }
                    failureTimes = 0;
                    var successValue = ops.DoWork(UserNameTextBox.Text, PasswordTextBox.Text);
    
                    var workResult = string.IsNullOrWhiteSpace(successValue);
    
                    if (workResult)
                    {
                        MessageBox.Show("Operation is successful");
                    }
                    else
                    {
                        MessageBox.Show(successValue);
                    }
                }
            }
    

    It works well now.

    However, if the user closes the program and then open it again, the judgment condition will be reset.

    One idea is to create a table in the administrator's database to record user information that has failed to login more than 3 times.

    Each time the user logs in, the program reads the table to determine if the user is allowed to login.

    Or you can create a file in windows to record it.

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Decompressor Wednesday, November 13, 2019 10:22 AM
    Wednesday, November 13, 2019 9:50 AM

All replies

  • using System;
    using System.Data.SqlClient;
    using System.Security;
    
    namespace MinimalApproachExample
    {
        
        
            public class DatabaseUser
            {
            public string DoWork(string pUserNName, string pPassword)
            {
    
                string connectionString = $"Data Source={serverName};" +
                                          $"Initial Catalog={catalogName};";
    
    
                var securePassword = new SecureString();
    
                foreach (var character in pPassword)
                {
                    securePassword.AppendChar(character);
                }
                return "";
            }
                private string serverName;
                private string catalogName;
                public DatabaseUser(string pServerName, string pCatalogName)
                {
                    serverName = pServerName;
                    catalogName = pCatalogName;
                }
                public bool SqlCredentialLogin(string pUserName, string pPassword)
                {
    
                    string connectionString = $"Data source={serverName};" +
                        $"Initial catalog={catalogName};";
                    var securePassword = new SecureString();
                    foreach (var character in pPassword)
                    {
                        securePassword.AppendChar(character);
                    }
                    securePassword.MakeReadOnly();
                    var credentials = new SqlCredential(pUserName, securePassword);
                    using (var cn = new SqlConnection { ConnectionString = connectionString })
                    {
                        try
                        {
                            cn.Credential = credentials;
                            cn.Open();
                            return true;
                        }
                        catch (Exception e)
                        {
                            return false;
                        }
                    }
                }
    
           
    
        }        
    }

    Tuesday, November 12, 2019 3:23 PM
  • Hi pavlob,

    Thank you for posting here.

    According to your description, you want to limit attempts to connect to SQL Server.

    A simple idea is to add a variable to record the number of user failures, and then judge the variable in the appropriate place.

    Here is a code example, you can refer to it.

            public int failureTimes = 0;
            private void ConnectButton_Click(object sender, EventArgs e)
            {
    
                if (!string.IsNullOrWhiteSpace(UserNameTextBox.Text) && !string.IsNullOrWhiteSpace(PasswordTextBox.Text))
                {
                    var ops = new DatabaseUser("localhost", "test");
    
                    var loginResults = ops.SqlCredentialLogin(UserNameTextBox.Text, PasswordTextBox.Text);
                    if (loginResults)
                    {
                        MessageBox.Show("Connection Success.");
                    }
                    else
                    {
                        failureTimes++;
                        if (failureTimes >= 3)
                        {
                            MessageBox.Show("You have failed 3 times, the account has been locked!");
                            return;                    
                        }
                        MessageBox.Show($"You have failed {failureTimes} times and there are {3 - failureTimes} chances left.");
                        return;
                    }
    
                    var successValue = ops.DoWork(UserNameTextBox.Text, PasswordTextBox.Text);
    
                    var workResult = string.IsNullOrWhiteSpace(successValue);
    
                    if (workResult)
                    {
                        MessageBox.Show("Operation is successful");
                    }
                    else
                    {
                        MessageBox.Show(successValue);
                    }
                }
            }
    

    Result:

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 13, 2019 3:04 AM
  • As I've written higher, I need to limit attempts to 3 times, but code you've suggested doesn't limit those efforts,

    because, if after 3rd failed effort someone enters correct login and password - connection succeeded.

    Wednesday, November 13, 2019 9:07 AM
  • Hi pavlob,

    Thanks for your feedback.

    It’s my fault.

    I made some minor changes to the code.

            public int failureTimes = 0;
            private void ConnectButton_Click(object sender, EventArgs e)
            {
                // Put the code that limit the number of failures at the beginning of the method
                if (failureTimes >= 3)
                {
                    MessageBox.Show("You have failed 3 times, the account has been locked!");
                    return;
                }
    
                if (!string.IsNullOrWhiteSpace(UserNameTextBox.Text) && !string.IsNullOrWhiteSpace(PasswordTextBox.Text))
                {
                    var ops = new DatabaseUser("localhost", "test");
    
                    var loginResults = ops.SqlCredentialLogin(UserNameTextBox.Text, PasswordTextBox.Text);
                    if (loginResults)
                    {
                        MessageBox.Show("Connection Success.");
                    }
                    else
                    {
                        failureTimes++;
                        MessageBox.Show($"You have failed {failureTimes} time and there are {3 - failureTimes} chances left.");
                        return;
                    }
                    failureTimes = 0;
                    var successValue = ops.DoWork(UserNameTextBox.Text, PasswordTextBox.Text);
    
                    var workResult = string.IsNullOrWhiteSpace(successValue);
    
                    if (workResult)
                    {
                        MessageBox.Show("Operation is successful");
                    }
                    else
                    {
                        MessageBox.Show(successValue);
                    }
                }
            }
    

    It works well now.

    However, if the user closes the program and then open it again, the judgment condition will be reset.

    One idea is to create a table in the administrator's database to record user information that has failed to login more than 3 times.

    Each time the user logs in, the program reads the table to determine if the user is allowed to login.

    Or you can create a file in windows to record it.

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Decompressor Wednesday, November 13, 2019 10:22 AM
    Wednesday, November 13, 2019 9:50 AM