Store, retrieve and validate password (SecureString) in SQL Server

Question

I have a login window that gets the username and password from the user and I would like to know the best way to handle the password. The username is just a regular textbox, but the password is a PasswordBox. I pass the username directly to the ViewModel, but only set a SecureString property on the ViewModel once the Login button is clicked using Code-Behind. After the Password SecureString is set I want to Validate.

I'm writing the LoginBox now, but I don't have the Model fully worked out yet. How should I store the password in SQL Server? Do I just write the contents of the SecureString to SQL and then try to compare it when the user tries to login?

Something like this? http://stackoverflow.com/questions/1191112/password-hashing-salt-and-storage-of-hashed-values — CodeCaster, Sep 05 '11 at 16:42
That helps with how to hash the password, but what datatype do I store it in and how do I work with the SecureStrings to do comparison afterward? — John the Ripper, Sep 05 '11 at 16:52
Datatype should probably just be a varchar, and a SecureString isn't a magical password saving solution, it's simply a String that's somewhat protected from attacks, you can't read its contents from memory from outside your program, but that's about it. — CodeCaster, Sep 05 '11 at 16:54

Yahia · Accepted Answer · 2011-09-05T17:14:45.410

4

You should NEVER store a password - not even encrypted...

Just store a hash of the password (which prevents the pasword from being ever retrieved as long as the hashing is implemented in a secure manner) and for validation you hash the user supplied password the same way and compare the results...

There are standards to do so:

The above standard makes it hard to use rainbow tables etc. because it makes the calculation very expensive since it uses several rounds in addition to a salt... thus hashing is for example 1000 times slower (with 1000 rounds) but this is exactly what you want - the attacker will need to do the same calculation and thus will need 1000 times the precessing power or time to achieve the goal by brute force...

You can store the result either as VARBINARY directly or as VARCHAR after Base64- or HEX-encoding the bytes... you will need to store the salt along with it (which is no security risk as long as every password gets its own distinct cryptographically secure generated random salt).

edited Sep 05 '11 at 17:14

answered Sep 05 '11 at 17:03

Yahia

69,653
9
115
144

That's what I was getting at. I was just wondering how to do it. I think what CodeCaster put in the comments will work. – John the Ripper Sep 05 '11 at 17:05
How do I store the salt? What datatype? – John the Ripper Sep 05 '11 at 17:32
the salt is just a `byte[]` and can be either stored as VARBINARY or as VARCHAR after Base64- or HEX-encoding it... – Yahia Sep 05 '11 at 17:34
Thanks, this along with the info from CodeCaster will do the trick. – John the Ripper Sep 05 '11 at 17:36
@Yahia In many cases it's just impossible. For example when you are connecting to a file share using `WNetUseConnection`, you cannot provide a hash instead of password, you should pass it as plain text. How then should I store a password for a file share in database? – Alex Zhukovskiy Aug 26 '16 at 09:35
@AlexZhukovskiy There are always some exceptions, but even then you need to apply the maximum possible security the respective user should have as little permissions as possible... the password should be generated in a secure way etc. – Yahia Sep 01 '16 at 17:25

Aaron Bertrand · Answer 2 · 2011-09-05T17:20:51.843

At my previous gig we stored the password as a hashed/encrypted/salted value (using MD5 at the time) as VARBINARY(32). To compare the password later, rather than try to decrypt the password, we would compared the encrypted + salted value that we stored to the encrypted + salted value of the password being attempted. If they matched, they got in, if they didn't match, they didn't get in.

The hashing work was done in the middle tier (both for saving the password initially and for comparing later), but a SQL Server-based example (to stop @Yahia's grumbling, this is not meant to tell you the most secure way possible, I am just illustrating the methodology with a very lightweight example. MD5 not strong enough for you? You can use a different and more complex algorithm along with more advanced salting techniques, especially if you perform the hashing in the application tier):

CREATE TABLE dbo.Users
(
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(255) NOT NULL UNIQUE,
    PasswordHash VARBINARY(32) NOT NULL
);

A procedure to create a user (no error handling or dupe prevention, just pseudo).

CREATE PROCEDURE dbo.User_Create
    @Username NVARCHAR(255),
    @Password NVARCHAR(16)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @salt NVARCHAR(16) = '$w0rdf1$h';

    INSERT dbo.Users(Username, Password)
        SELECT @Username, 
          CONVERT(VARBINARY(32), HASHBYTES('MD5', @Password + @Salt));
END
GO

Now a procedure to authenticate a user.

CREATE PROCEDURE dbo.User_Authenticate
    @Username NVARCHAR(255),
    @Password NVARCHAR(16)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @salt NVARCHAR(16) = '$w0rdf1$h';

    IF EXISTS 
    (
      SELECT 1 FROM dbo.Users
        WHERE Username = @Username AND 
        PasswordHash = CONVERT(VARBINARY(32), HASHBYTES('MD5', @Password + @salt))
    )
    BEGIN
        PRINT 'Please, come on in!';
    END
    ELSE
    BEGIN
        PRINT 'You can keep knocking but you cannot come in.';
    END
END
GO

In reality you would likely perform the hashing within the application, and pass the hash values in as VARBINARY(32) - this makes it much harder to "sniff" the actual clear-text password from anywhere. And you maybe wouldn't store the salt in plain text with the code either, but retrieve it from elsewhere.

This is definitely more secure than storing the password unencrypted, but it removes the ability to ever retrieve the password. Win-win in my opinion.

that works but I would really recommend using some more advanced hasing with several rounds etc. like PBKDF2 or bcrypt... — Yahia, Sep 05 '11 at 17:08
Yes, I indicated in my answer that we used MD5 at the time - I meant that ti imply that there are definitely more secure ways to do it. Note that we're likely talking about storing passwords for a forum site or recipe database or something, not the CIA. — Aaron Bertrand, Sep 05 '11 at 17:15
I understand that but even a modestly known site could have some damage in reputation if the user/pw DB is cracked esp. since a lot of users use the same PW for different sites :-( — Yahia, Sep 05 '11 at 17:16
AGAIN, this was just a trivial example to show the basic methodology, not meant as a *this is the most secure way possible* directive. — Aaron Bertrand, Sep 05 '11 at 17:18
10 years ago you wrote this post, and today it helped me tremendously. Thank you! — Ole M, Nov 04 '21 at 09:09

Store, retrieve and validate password (SecureString) in SQL Server

2 Answers2