jxharding
asked on
users table: password is encrypted , how to verify username and password in stored procedure
hi, i need to write a stored procedure which would authenticate the username and password with the users table.
the users table's password field is encrypted
the users table looks like this:
UserID(decimal), UserName(nvarchar),Passwor d(varbinar y(Max))
this is what i tried, but it does not work
CREATE PROCEDURE sproc_Authenticate
(
@UserName varchar(50),
@Password varchar(50),
@Ret int OUTPUT
)
WITH ENCRYPTION
as
declare @UserID int
set @UserID = (Select isnull((Select UserID from Users Where UserName = @UserName and CONVERT(NVARCHAR(50),DECRY PTBYKEY(Pa ssword))= @Password),0))
SET @Ret = @UserID
GO
this query however returns the users and passwords
OPEN SYMMETRIC KEY MyTableKey DECRYPTION
BY CERTIFICATE EncryptCert
Select Username,CONVERT(NVARCHAR( 50),DECRYP TBYKEY(Pas sword)) as password from users
how do i modify the select portion of the procedure
i cant do this:
set @UserID = (OPEN SYMMETRIC KEY MyTableKey DECRYPTION
BY CERTIFICATE EncryptCert
Select isnull((Select UserID from Users Where UserName = @UserName and CONVERT(NVARCHAR(50),DECRY PTBYKEY(EP assword)) = @Password),0))
please help me, i want to learn and if i have to do a different method, i will gladly accept and learn.
the users table's password field is encrypted
the users table looks like this:
UserID(decimal), UserName(nvarchar),Passwor
this is what i tried, but it does not work
CREATE PROCEDURE sproc_Authenticate
(
@UserName varchar(50),
@Password varchar(50),
@Ret int OUTPUT
)
WITH ENCRYPTION
as
declare @UserID int
set @UserID = (Select isnull((Select UserID from Users Where UserName = @UserName and CONVERT(NVARCHAR(50),DECRY
SET @Ret = @UserID
GO
this query however returns the users and passwords
OPEN SYMMETRIC KEY MyTableKey DECRYPTION
BY CERTIFICATE EncryptCert
Select Username,CONVERT(NVARCHAR(
how do i modify the select portion of the procedure
i cant do this:
set @UserID = (OPEN SYMMETRIC KEY MyTableKey DECRYPTION
BY CERTIFICATE EncryptCert
Select isnull((Select UserID from Users Where UserName = @UserName and CONVERT(NVARCHAR(50),DECRY
please help me, i want to learn and if i have to do a different method, i will gladly accept and learn.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also check here
http://msdn.microsoft.com/en-us/library/ms181860(SQL.90).aspx
I guess you should close after you are done
http://msdn.microsoft.com/en-us/library/ms181860(SQL.90).aspx
I guess you should close after you are done
OPEN SYMMETRIC KEY MyTableKey DECRYPTION
BY CERTIFICATE EncryptCert
declare @UserID int
set @UserID = (Select isnull((Select UserID from Users Where UserName = @UserName and CONVERT(NVARCHAR(50),DECRY