Encrypted password is not saving in table column

Sivakumar

11y1.2k

Hi,

This is my stored procedure :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspAddMember]
@MemberName NVARCHAR(50),
@LoginName NVARCHAR(50),
@LoginPassword NVARCHAR(4000)
AS BEGIN

DECLARE @HashThis nvarchar(4000);
SET @HashThis = CONVERT(nvarchar(4000),@LoginPassword);

SELECT HASHBYTES('SHA2_256', @HashThis);

DECLARE @MemberID INT;

INSERT INTO Member(MemberName, LoginPassword)
VALUES(@MemberName, @HashThis);

SET @MemberID = SCOPE_IDENTITY();

INSERT INTO MemberLogin(MemberID, MemberLoginName)
VALUES(@MemberID, @LoginName);

RETURN @MemberID;

END

when I execute this procedure generate a encrypted password but not saving in table column.

Please give me a correct query for that.

Thank you.

17 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mukesh Kumar11y ago
Hi,

Is this saving nothing or saving in different mode like text or something else?

thanks
Khrizz11y ago
You did not included LoginPassword in your Insert Statement

INSERT INTO MemberLogin(MemberID, MemberLoginName)
VALUES(@MemberID, @LoginName);

should be

INSERT INTO MemberLogin(MemberID, MemberLoginName,LoginPassword)
VALUES(@MemberID, @LoginName,HASHBYTES('SHA2_256', @HashThis));
Raja T11y ago
Please check like, insert into users(pwd) SELECT HASHBYTES('SHA2_256', 'test'); --(1 row(s) affected)
Bhushan Bhasme11y ago
Try One thing
nvarchar(4500)
Check Once..I think password might get saved ..drag the column and increase the column width and check once...that your password is saved in proper format or not
Sivakumar11y ago
There is no error but it save only normal password
Bhushan Bhasme11y ago
What Error You Are Getting While Saving The password into the database
Please paste It Here
Sivakumar11y ago
Yes, but not working. not saving encrypted password
Bhushan Bhasme11y ago
try nvarchar(max)
Sivakumar11y ago
I change the stored procedure like this :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspAddMember]
@MemberName NVARCHAR(50),
@LoginName NVARCHAR(50),
@LoginPassword NVARCHAR(4000)
AS BEGIN

DECLARE @HashThis nvarchar(4000);
SET @HashThis = CONVERT(nvarchar(4000),@LoginPassword);

DECLARE @MemberID INT;

INSERT INTO Member(MemberName, LoginPassword)
VALUES(@MemberName, HASHBYTES('SHA2_256', @HashThis));

SET @MemberID = SCOPE_IDENTITY();

INSERT INTO MemberLogin(MemberID, MemberLoginName)
VALUES(@MemberID, @LoginName);

RETURN @MemberID;

END

Then save the password like this format :
Sivakumar11y ago
0x09378D3AD99A79950044763A65F4C192B3BD43C374682E26BA0484A85BE6FCC0
Bhushan Bhasme11y ago
Please Provide The Format Of Password Which You Are Getting
Ravi Kumar11y ago
change loginpassword data type nvarchar(max)
Sivakumar11y ago
When I execute my procedure then it returns a encrypted password. but not saving in my table
Ravi Kumar11y ago
give loginpassword data type nvarchar(max)
Bhushan Bhasme11y ago
Hi shivkumar,
What output You Are Getting??
Sivakumar11y ago

This is my table
Ravi Kumar11y ago
what is the data type given in data base