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

ALTER PROCEDURE [dbo].[verify_un_and_pw_quick]
@UN VARCHAR(50) = NULL,
@PW NVARCHAR(128) = NULL



AS

BEGIN


DECLARE @HashThis2 nvarchar(128);
DECLARE @Final nvarchar(128);
SELECT @HashThis2 = (SELECT pws from tbl_login where user_name_27 = @un)

DECLARE @HashThis nvarchar(128);
SELECT @HashThis = CONVERT(nvarchar(128),@PW);
SELECT @Final=  HashBytes('SHA1', Convert(nvarchar(128),@HashThis + @HashThis2));


PRINT @Final



SELECT COUNT(*) 
FROM [tbl_login]
WHERE (user_name_27 = @UN) AND (pw = @Final) ;
END

DECLARE @Logon TABLE 
(
    UserName NVARCHAR(50), 
    HashedPassword VARBINARY(8000), 
    ActualPassword NVARCHAR(128), 
    PasswordSalt NVARCHAR(128)
)

INSERT @Logon
    (UserName , HashedPassword , ActualPassword , PasswordSalt)
VALUES    
    ('Fred', HASHBYTES('SHA1', 'Foo' + 'Salty'), N'foo', N'Salty')
    ,('Alice', HASHBYTES('SHA1', '12345Nhus_uhnk_' + N'123_Salt_My_Churro'), N'12345Nhus_uhnk_', '123_Salt_My_Churro')

SELECT *
FROM @Logon


SELECT *
FROM @Logon
    WHERE UserName = 'Alice'
    AND HashedPassword = HASHBYTES('SHA1', N'12345Nhus_uhnk_' + PasswordSalt)

quote:

---

Originally posted by tvb2727

Also, what does the N do again? Do I need to include that at the start of the hash? or just to help it out I guess you could say in the code?

---

SELECT HASHBYTES('SHA1', 'StringValue')
SELECT HASHBYTES('SHA1', N'StringValue') 

DECLARE @Logon TABLE 
(
    UserName NVARCHAR(50), 
    HashedPassword NVARCHAR(128), 
    ActualPassword NVARCHAR(128), 
    PasswordSalt NVARCHAR(128)
)

INSERT @Logon
    (UserName , HashedPassword , ActualPassword , PasswordSalt)
VALUES    
    ('Fred', CAST(HASHBYTES('SHA1', 'Foo' + 'Salty') AS NVARCHAR(128)), N'foo', N'Salty')
    ,('Alice', CAST(HASHBYTES('SHA1', '12345Nhus_uhnk_' + N'123_Salt_My_Churro') AS NVARCHAR(128)), N'12345Nhus_uhnk_', '123_Salt_My_Churro')

SELECT *
FROM @Logon


SELECT *
FROM @Logon
    WHERE UserName = 'Alice'
    AND HashedPassword = CAST(HASHBYTES('SHA1', N'12345Nhus_uhnk_' + PasswordSalt)  AS NVARCHAR(128))

Create PROCEDURE [dbo].[verify_un_and_pw_quick]
@UN VARCHAR(50) = NULL,
@PW NVARCHAR(128) = NULL

AS

BEGIN


SELECT COUNT(*) 
FROM [tbl_login]
WHERE (user_name_27 = @UN) AND (pw = = CAST(HASHBYTES('SHA1', N@PW + pws)  AS NVARCHAR(128))
END

 Private Shared Function CreatePasswordHash_ONLY(pwd As String) As String
        Dim hashedPwd As String = FormsAuthentication.HashPasswordForStoringInConfigFile(pwd, "sha1")

        Return hashedPwd
    End Function
    Protected Sub btnvalidatepw_Click(sender As Object, e As System.EventArgs)
        Try
            If txtUN.Text = "" Or txtPW.Text = "" Then
                MsgBox("Invalid data")
                Exit Sub
            End If
            
            
         
            Using cmd As New SqlCommand("verify_un_and_pw_HASH", mainconnection)
                cmd.CommandType = CommandType.StoredProcedure
                'specify that the sqlcommand will use a storedprocedure
                Dim param As New SqlParameter()
                param.ParameterName = "@UN"
                'this name needs to match your parameter name you used in the stored proc
                param.SqlDbType = SqlDbType.NVarChar
                param.Size = 25
                param.Direction = ParameterDirection.Input
                param.Value = txtUN.Text.Trim
                'can be textbox.text or w/e
                cmd.Parameters.Add(param)

                
                
                param = New SqlParameter()
                param.ParameterName = "@PW"
                'this name needs to match your parameter name you used in the stored proc
                param.SqlDbType = SqlDbType.NVarChar
                param.Size = 128
                param.Direction = ParameterDirection.Input
                param.Value = CreatePasswordHash_ONLY(txtPW.Text.Trim)
                'can be textbox.text or w/e
                cmd.Parameters.Add(param)

                
             
              
              
                'fill the dataset
                ' adapter.Fill(ds)
                mainconnection.Open()
                Dim iResults As Integer = cmd.ExecuteScalar()
                mainconnection.Close()
      
                If iResults = 1 Then
                    'The user was found in the DB
                    MsgBox("The user was found in the DB", MsgBoxStyle.Information, "password 2 step way")
                Else
                    'The user was not found in the DB
                    MsgBox("The user was not found in the DB", MsgBoxStyle.Exclamation, "password 2 step way")
                End If
            End Using
            
            
            
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

USE [timer_it_27]
GO
/****** Object:  StoredProcedure [dbo].[verify_un_and_pw_HASH]    Script Date: 08/05/2011 03:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[verify_un_and_pw_HASH]
@UN NVARCHAR(50) = NULL,
@PW NVARCHAR(128) = NULL
AS
BEGIN
SELECT  COUNT(*) 
FROM tbl_login_hash
    WHERE UserName = @UN
    AND HashedPassword = CAST(HASHBYTES('SHA1', @PW + PasswordSalt)  AS NVARCHAR(128))
END

USE [timer_it_27]
GO

/****** Object:  Table [dbo].[tbl_login_hash]    Script Date: 08/05/2011 04:39:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_login_hash](
	[UserName] [nvarchar](50) NOT NULL,
	[HashedPassword] [nvarchar](128) NULL,
	[PasswordSalt] [nvarchar](128) NULL,
	[last_login_27] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[UserName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO