Solved

users table: password is encrypted , how to verify username and password in stored procedure

Posted on 2010-08-12
3
356 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:jxharding
  • 3
3 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 33423821
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))
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 33423833
it is a seperate statement

http://msdn.microsoft.com/en-us/library/ms190499.aspx
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))

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33423942
also check here

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

I guess you should close after you are done
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question