I am using the built in PWDCOMPARE function in SQL
server...

declare @password as char(7)
set @password = 'password'
Select PWDCOMPARE(@password, PWDENCRYPT(@password),0) as
encryptedcompare
from profiles

I get 0 everytime! (Even though the string 'password' is a
valid value in the DB)...however, if I use it as below..

Select PWDCOMPARE('password', PWDENCRYPT('password'),0) as
encryptedcompare
from profiles

it works! Can PWDCOMPARE function handle variables such in
my case @password?

declare @password as char(7)

vs

password
12345678

Bye,
Delbert Glass

"Yaheya Quazi" <anonymous@discussions.microsoft.com> wrote in message
news:12c5601c411cf$6e9c5c60$a401280a@phx.gbl...
> I am using the built in PWDCOMPARE function in SQL
> server...
>
> declare @password as char(7)
> set @password = 'password'
> Select PWDCOMPARE(@password, PWDENCRYPT(@password),0) as
> encryptedcompare
> from profiles
>
> I get 0 everytime! (Even though the string 'password' is a
> valid value in the DB)...however, if I use it as below..
>
> Select PWDCOMPARE('password', PWDENCRYPT('password'),0) as
> encryptedcompare
> from profiles
>
> it works! Can PWDCOMPARE function handle variables such in
> my case @password?
>
>
>

I see the issue here...

I have re-written the procedure I get 0 even when I pass a
valid password...

CREATE PROCEDURE login
@password as char(7)
as
Declare @enc_password as varbinary(85)
Select @enc_password = password from profiles where email
= 'yquazi@ucmerced.edu'

Select PWDCOMPARE(@password, @enc_password)
GO

Any idea??

length of "valid password"
vs
char(7)

char(7)
vs
varchar(7)

multiple rows with same email but different passwords
vs
each row has a unquie email (perferably enfored to be unique)

No row with email = 'yquazi@ucmerced.edu' in profile table.
No password value present on email = 'yquazi@ucmerced.edu' row.
Mistach between type/width of profile.password and what's (wrongfully) been
stored into the column.

As you can see it could be lot's of different things.

TIP:
You will need to carefully look to see what you have;
rather than, assume you know what you have.

For example.
Inspect what value *is received* for the parameter.
Inspect what value *is retreived* for profile.password.
Inspect that PWDCOMPARE functions properly.

Bye,
Delbert Glass

<anonymous@discussions.microsoft.com> wrote in message
news:1325801c411d6$2b414d70$a101280a@phx.gbl...
> I see the issue here...
>
> I have re-written the procedure I get 0 even when I pass a
> valid password...
>
> CREATE PROCEDURE login
> @password as char(7)
> as
> Declare @enc_password as varbinary(85)
> Select @enc_password = password from profiles where email
> = 'yquazi@ucmerced.edu'
>
> Select PWDCOMPARE(@password, @enc_password)
> GO
>
> Any idea??
>

ok here goes this is as plain as can be...I still get 0
everytime..

CREATE PROCEDURE login
as
Declare @enc_password as varbinary(85)
Declare @password as char(8)
Set @password = 'password'
Select @enc_password = encrypt('password')
print @enc_password
Select PWDCOMPARE('password', @enc_password)
GO

I think there is a reason Microsoft made the functions
encrypt, PWDCOMPARE undocumented...because it does not
work all the time :)

Thanks for your help anyway.