I am wondering if anyone can help me to solve the problem I am facing. Thanks!
My task is to encrypt the user passwords which are already stored in the table named [test].
In a store procedure, I used the pwdencrypt and pwdcompare functions and updated the user's password in the [test] table with the encrypted one.
Here is my problem,if user comes back to the login screen again, he can't login. I am thinking I should fetch the encrypted password from the [test] table and decrypt it first, then compare it with the one user entered. After read some articles from web, I realized the
pwdencrypt is one way hash function, I can't decrypt the password....

Any idea appreciated!


flyfish

__________________
flyfish

The idea with hashed passwords is that they cannot be decrypted. That's because they are not encrypted; only a hash is retrieved from the password.

The usual way to fix this is to also hash the password the user is entering. Then you can compare the two hashes and see if you have a match....

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thank you for you advice.
I just tried the way to hash the password entered by the user and compared it with the hashed password stored in the table. It doesn't work!
The reason is that the result from the pwdencrypt function is always different even if you use a same plain text string as the parameter. Any idea?

flyfish

Not really. I am not too familiar with pwdencrypt.

However, from what I know of it, shouldn't it be used in conjunction with Pwdcompare to compare an unencrypted password with an encrypted one?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Blindspot by Bitmonx (Track 3 from the album: Irresistible Meltdown Vol. 2) What's This?

I tested if I just compared two encypted passwords(the user input one and the one from the table)
and didn't upgrade the password in the table with the encrypted one, everything is fine.
In my project, I need to store an encrypted password instead the plain text one, so, I still need
some more input from you.

I have two stored procedures: first: upgrade_password; second: access_login

first exec "upgrade_password": update the password stored in the table first
(
    declare @pwd varchar(255)
    set @pwd =(
    select T.[password]
    from test T
    ....
    )

    set @EncryptedPin = CONVERT(varbinary(255),pwdencrypt(@pwd))
        update T
        set [password] = @encryptedPin
        from test T
        where ............
)

Then, exec "access_login stored procedure"
(
    @password varchar -- input parameter

     declare @pwd varchar(255)
    set @pwd =(
            select T.[password]
            from test T
                where --------

/* -- for password encryption test

    declare @temp varchar(255)
    declare @temp1 varbinary(255)
    set @temp = @password
    set @temp1 = CONVERT(varbinary(255),pwdencrypt(@temp))

    if (select pwdcompare(@temp, @temp1, 0)) = 1
    begin
        select 'user input password encryption OK' = 1
    end
    else
        select 'user input password encryption failure' = 0


    if @temp1 = @pwd
    begin
        select 'passed' = 1
    end
    else
        select 'failed' = 10
)

After exec "access_login" I got message "failed = 10'. Any idea?

flyfish

I could be overlooking something but I think you're making things way too difficult with datatypes like varbinary.

Check out this simple example: http://www.devx.com/tips/Tip/14407

It encrypts a password and stores it in a column. Then it used pwdcompare to check the password.

Isn't this all you need?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks!
I have been working on this now, I will have some feedback when I am done.

flyfish

Sorry, I still have some problems, details as follows:

first exec "upgrade_password": update the password stored in the table "test'
(
    declare @pwd varchar(255)
    set @pwd =(
    select T.[password]
    from test T
    ....
    )

    set @EncryptedPin = CONVERT(varbinary(255),pwdencrypt(@pwd))
        update T
        set [password] = @encryptedPin
        from test T
        where ............

    PRINT pwdcompare(@pwd,@encryptedPin );

)
return 1, so this part works.

-------
Then, exec "access_login stored procedure"
(
    @password varchar -- input parameter

    declare @pwd varchar(255)
    set @pwd =(
            select T.[password]
            from test T
            where --------

/* -- for password encryption test
   PRINT pwdcompare(@password,@pwd);

)
retun 0, I didn't know why ? Can you explain to me the reason? Thanks!

Do you get any results back from our SELECT statement? Maybe pwd is null when you try to compare it....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks Imar! It worked.

flyfish