locked
Storing password in sql server RRS feed

  • Question

  • Hello,

    I am designing a website( for first time) and for the customer login access , I need to store the passwords in hash+salt form in sql server. How to do that? Any guidance? And which is the best hash algorithm to be used with sql server 2008.

     

    Tools used: visual web developer 2010 and sql server express 2008.

     

    Tuesday, May 10, 2011 2:00 PM

Answers

  • Hi Stefan,

     

    I cannot agree with you. 

    First, ramee said he was designing a website and needed to store salt and hashes in SQL, not create hashes in SQL. And what was the best way to create hashes in SQL. Although you can assume he was searching for a SQL solution he might not be aware of other, maybe better, options. It's part of contributing to the community.

    Second, the stored procedures you described send the password over in clear text unless you are using connection encryption like SSL. Only login credential exchanges are encrypted, but you  pass in a parameter in a stored procedure. This is never encrypted, unless you encrypt the connection.

     


    Robert Hartskeerl - blog - twitter
    • Marked as answer by Papy Normand Saturday, May 14, 2011 10:02 AM
    Wednesday, May 11, 2011 8:10 AM

All replies

  • You can use HashBytes funtion with SHA1 algorithm.

    "The following example returns the SHA1 hash of the nvarchar data stored in variable @HashThis."

    DECLARE @HashThis nvarchar(4000);
    SELECT @HashThis = CONVERT(nvarchar(4000),'UserName');
    SELECT HashBytes('SHA1', @HashThis);


    Make everything as simple as possible, but not simpler.
    Tuesday, May 10, 2011 3:19 PM
  • hi,

    a very simplified example:

    USE Test ;
    GO
    
    -- No one gets access to this tables except the stored procedures.
    CREATE TABLE Credentials
        (
          UserName NVARCHAR(255) NOT NULL
                                 PRIMARY KEY ,
          PasswordHash VARBINARY(MAX) NOT NULL ,
          PasswordSalt NVARCHAR(255) NOT NULL ,
          CONSTRAINT UQ_Credentials_PasswordSalt UNIQUE ( PasswordSalt )
        ) ;
    GO
    
    -- NEVER expose the value of the salt.
    CREATE VIEW UserList
    AS
        SELECT  UserName
        FROM    Credentials ;
    GO
    
    --http://xsqlsoftware.blogspot.com/2008/12/t-sql-random-string-generator.html
    CREATE PROCEDURE sp_GenerateRandomString
        (
          @sLength INT = 10 ,
          @randomString NVARCHAR(255) OUTPUT
        )
    AS
        BEGIN
            DECLARE @counter INT ;
            DECLARE @nextChar NCHAR(1) ;
            SET @counter = 1 ;
            SET @randomString = '' ;
    
            WHILE @counter <= @sLength
                BEGIN
                    SET @nextChar = NCHAR(ROUND(RAND() * 93 + 33, 0)) ;
                    IF ASCII(@nextChar) NOT IN ( 34, 39, 40, 41, 44, 46, 96, 58, 59 )
                        BEGIN
                            SET @randomString = @randomString + @nextChar ;
                            SET @counter = @counter + 1 ;
                        END ;
                END ;
        END ;
    GO
    
    CREATE PROCEDURE sp_CreateCredentials
        (
          @UserName NVARCHAR(255) ,
          @Password NVARCHAR(255)
        )
    AS
        BEGIN
            DECLARE @salt NVARCHAR(255) ;
            EXEC sp_GenerateRandomString 255, @salt OUTPUT ;
            INSERT  INTO Credentials
                    ( UserName ,
                      PasswordHash ,
                      PasswordSalt
                    )
            VALUES  ( @UserName ,
                      HASHBYTES('SHA1', @Password + @salt) ,
                      @salt
                    ) ;
            -- Further control logic (return values) and error handling required.
        END ;
    GO
    
    CREATE PROCEDURE sp_TestCredentials
        (
          @UserName NVARCHAR(255) ,
          @Password NVARCHAR(255)
        )
    AS
        BEGIN
            SELECT  UserName
            FROM    Credentials
            WHERE   UserName = @UserName
                    AND PasswordHash = HASHBYTES('SHA1', @Password + PasswordSalt) ;
            -- Further control logic (return values) and error handling required.
        END ;
    GO

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, May 10, 2011 3:31 PM
  • If you are using Visual Web and .NET why not use the builtin framework .NET provides. This will create a user table with classes to create and access logins.

    Second, I should never hash the passwords in SQL. This means I would have to send the password in text to the stored procedure where it can easily be picked up by network sniffers.

    This page has more information on the login and membership providers in .NET: http://msdn.microsoft.com/en-us/library/ff184050.aspx


    Robert Hartskeerl - blog - twitter
    • Proposed as answer by Mr. Wharty Wednesday, May 11, 2011 3:00 AM
    Tuesday, May 10, 2011 4:07 PM
  • hi Robert,

    If you are using Visual Web and .NET why not use the builtin framework .NET provides. This will create a user table with classes to create and access logins.

    I fully agree, but the question was asked in SQL Server forum, thus a SQL Server based solution.)

    Second, I should never hash the passwords in SQL. This means I would have to send the password in text to the stored procedure where it can easily be picked up by network sniffers.

    No, not easily. Every credential exchange is done over a encrypted channel. But this is another question.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, May 11, 2011 8:00 AM
  • Hi Stefan,

     

    I cannot agree with you. 

    First, ramee said he was designing a website and needed to store salt and hashes in SQL, not create hashes in SQL. And what was the best way to create hashes in SQL. Although you can assume he was searching for a SQL solution he might not be aware of other, maybe better, options. It's part of contributing to the community.

    Second, the stored procedures you described send the password over in clear text unless you are using connection encryption like SSL. Only login credential exchanges are encrypted, but you  pass in a parameter in a stored procedure. This is never encrypted, unless you encrypt the connection.

     


    Robert Hartskeerl - blog - twitter
    • Marked as answer by Papy Normand Saturday, May 14, 2011 10:02 AM
    Wednesday, May 11, 2011 8:10 AM
  • So whats the better solution? And thanks for your answers cem, stefen and robert.
    Wednesday, May 11, 2011 1:36 PM
  • To me, and other answers in similar treats, it is common practice to hash passwords on the client. That's where they are entered. If you encrypt the passwords on the client and then store the hashed password in SQL the real password is never send across the wire. You can store the hashed passwords as string but the preferred method is to store the binary. 

    There are numerous article on how to hash a password in .NET or you can use the membership provider in .NET 2.0. 


    Robert Hartskeerl - blog - twitter
    Wednesday, May 11, 2011 2:12 PM