DEV Community

Cover image for Passwords in SQL-Server C#
Karen Payne
Karen Payne

Posted on

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.


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.


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) )
    DECLARE @result VARCHAR(7);

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

    RETURN @result;

Enter fullscreen mode Exit fullscreen mode

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();


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

Enter fullscreen mode Exit fullscreen mode

Unit test

And a unit test method for testing the method above.

public void ValidLoginTest()
    // arrange
    var userName = "payneoregon";
    var password = "!FirstOnMonday";

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

    // assert
Enter fullscreen mode Exit fullscreen mode

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();


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

Enter fullscreen mode Exit fullscreen mode


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.

Top comments (0)