Passwords in SQL-Server C#
Tags: csharp, database, security
Published: 2023-01-02 13:48:58

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](https://learn.microsoft.com/en-us/sql/t-sql/functions/pwdencrypt-transact-sql?view=sql-server-ver16) which might not be supported in future releases of SQL-Server, the second example uses [HASHBYTES](https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver16) which offers more options.

## Using HASHBYTES
Another method provides more options using [HASHBYTES](https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver16). 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.

```sql
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.

```csharp
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.

```csharp
[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.

```csharp
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](https://github.com/karenpayneoregon/sql-server-password-secure) 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.