Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-08-12
3
Medium Priority
?
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 59

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 59

Accepted Solution

by:
HainKurt earned 2000 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 59

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 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