Passwords in SQL-Server C#

When a new coder/developer needs to store user name and passwords in a SQL-Server database they tend to create a table that stores passwords in clear text. For these coders/developers they may look at hashing and other methods and think this is way too much work right now and think they will get back to refactoring the code yet never do in most cases.

Using PWDENCRYPT

An easy method to store passwords is PWDENCRYPT which might not be supported in future releases of SQL-Server, the second example uses HASHBYTES which offers more options.

Using HASHBYTES

Another method provides more options using HASHBYTES. In the following example only the password is protected, if you like, the next step would be to concatenate the user name and password together.

Stored procedure

In this case a stored procedure is needed.

CREATE OR ALTER FUNCTION Password_Check ( @v1 VARCHAR(500) )
RETURNS VARCHAR(7)
AS
BEGIN
    DECLARE @result VARCHAR(7);

    SELECT @result = (CASE
                          WHEN [Password] = HASHBYTES('SHA2_512', @v1) THEN
                              'Valid'
                          ELSE
                              'Invalid'
                      END
                     )
    FROM Users1
    WHERE [Password] = HASHBYTES('SHA2_512', @v1);

    RETURN @result;

END;

Validation method

Method to validate user name and password.

public class DataOperations
{
    public static (bool, Exception) ValidateUserLogin(string username, SecureString password)
    {
        using var cn = new SqlConnection(ConfigurationHelper.ConnectionString());
        using var cmd = new SqlCommand() { Connection = cn };

        /*
         * Note:
         * (@Password) as ValidItem is used if you want to load results into a DataTable which
         * makes it easy for debugging, otherwise no need for the alias.
         */
        cmd.CommandText = "SELECT Id,[dbo].[Password_Check] (@Password) as ValidItem " + 
                          "FROM dbo.Users1 AS u  WHERE u.UserName = @UserName";

        cmd.Parameters.Add("@UserName", SqlDbType.NChar).Value = username;
        cmd.Parameters.Add("@Password", SqlDbType.NChar).Value = password.ToUnSecureString();

        try
        {
            cn.Open();

            var reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                return (reader.GetValue(1) != DBNull.Value, null)!;
            }
            else
            {
                return (false, null)!;
            }
        }
        catch (Exception exception)
        {
            return (false, exception);

        }
    }
}

Unit test

And a unit test method for testing the method above.

[TestMethod]
[TestTraits(Trait.PasswordCheck)]
public void ValidLoginTest()
{
    // arrange
    var userName = "payneoregon";
    var password = "!FirstOnMonday";

    // act
    var (success, exception ) = DataOperations
        .ValidateUserLogin(userName, password!.ToSecureString()!);

    // assert
    success.ShouldBeTrue();
}

How to add a record

Shown below, the key is HASHBYTES('SHA2_512', @Password).

Note that the second query returns the key to the newly added record.

public static int AddUser(User user)
{

    using var cn = new SqlConnection(ConfigurationHelper.ConnectionString());
    using var cmd = new SqlCommand() { Connection = cn };


    cmd.CommandText = "INSERT INTO Users1 (UserName, [Password]) VALUES (@UserName, HASHBYTES('SHA2_512', @Password));" +
                      "SELECT CAST(scope_identity() AS int);";

    cmd.Parameters.Add("@UserName", SqlDbType.NChar).Value = user.Name;
    cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = user.Password.ToUnSecureString();

    cn.Open();

    return Convert.ToInt32(cmd.ExecuteScalar());

}

Summary

With the information provided a coder/developer need not have to store passwords as clear text which compromises user information.

Source code

See the following GitHub repository which includes

• A class project for
  • Validating user name/password
  • Adding a new user
• Console project to test code from above and a script to create the database
• A simple unit test project for validating a login.