Link to home
Start Free TrialLog in
Avatar of jxharding
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),Password(varbinary(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),DECRYPTBYKEY(Password))= @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),DECRYPTBYKEY(Password)) 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),DECRYPTBYKEY(EPassword)) = @Password),0))


please help me, i want to learn and if i have to do a different method, i will gladly accept and learn.
Avatar of HainKurt
HainKurt
Flag of Canada image

try this

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),DECRYPTBYKEY(Password))= @Password),0))
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
also check here

http://msdn.microsoft.com/en-us/library/ms181860(SQL.90).aspx

I guess you should close after you are done