goodk
asked on
password and verify store procedures - not working -
After I exec and create a new user.
then I try to verify it but it is always returning "Failed"
am I doing it right?
----------
USE [SA02012011]
GO
/****** Object: StoredProcedure [dbo].[pAddUser] Script Date: 02/28/2011 10:56:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pAddUser](
@UserName VARCHAR(20),
@eMail VARCHAR(20),
@Algorithm VARCHAR(16),
@Pass VARCHAR(16)
)
AS
BEGIN
set nocount on
INSERT INTO PersonID (UserName, eMail, passwordID)
select @UserName, @eMail, hashbytes(@Algorithm,@Pass )
END
USE [SA02012011]
GO
/****** Object: StoredProcedure [dbo].[pVerifyCredentials] Script Date: 02/28/2011 10:55:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pVerifyCredentials] (
@UserName VARCHAR(20),
@Algorithm VARCHAR(16),
@Pass VARCHAR(16)
)
AS
BEGIN
set nocount on
select case num when 1 then 'OK'
else 'Failed'
end as Result
from (
SELECT count(*) as Num
FROM PersonID
WHERE UserName = @UserName
AND passwordID = convert(binary(16),HASHBYT ES(@Algori thm, @Pass))
) AS X
END
return
then I try to verify it but it is always returning "Failed"
am I doing it right?
----------
USE [SA02012011]
GO
/****** Object: StoredProcedure [dbo].[pAddUser] Script Date: 02/28/2011 10:56:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pAddUser](
@UserName VARCHAR(20),
@eMail VARCHAR(20),
@Algorithm VARCHAR(16),
@Pass VARCHAR(16)
)
AS
BEGIN
set nocount on
INSERT INTO PersonID (UserName, eMail, passwordID)
select @UserName, @eMail, hashbytes(@Algorithm,@Pass
END
USE [SA02012011]
GO
/****** Object: StoredProcedure [dbo].[pVerifyCredentials]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pVerifyCredentials]
@UserName VARCHAR(20),
@Algorithm VARCHAR(16),
@Pass VARCHAR(16)
)
AS
BEGIN
set nocount on
select case num when 1 then 'OK'
else 'Failed'
end as Result
from (
SELECT count(*) as Num
FROM PersonID
WHERE UserName = @UserName
AND passwordID = convert(binary(16),HASHBYT
) AS X
END
return
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks - it was so timely!
and CORRECT
and CORRECT
You need to compare password in the same way that you saved to database
Changed
passwordID = convert(binary(16),HASHBYT
to
passwordID = HASHBYTES(@Algorithm, @Pass)
Raj