Example Code Help - followup

Hi Hari,
I just made a modification in the DDL for the code u
posted yesterday.
In the below code if u observe, I did not Insert any
record for a user 'blabla' into the table FLogin but I get
the QA output "Login Successfully" when i query.
CREATE TABLE [dbo].[FLogin] (
[UserName] [nvarchar] (16) NOT NULL,
[WebPassword] [varbinary] (128) NOT NULL,
) ON [PRIMARY]
GO
-- INSERTING ENCRYPTED value
-- hard coded strings should be replaced by a text box
value from a user interface
INSERT INTO FLogin VALUES ('Chip',PWDENCRYPT ('unclechip'))
-- the Decryption phase
DECLARE @.pwd VARBINARY(128), @.chk TINYINT
-- trying to select password for a user whose record
doesnt exist
SELECT @.pwd=WebPassword from FLogin where UserName
='blabla'
-- comparing : 1 is success, 0 is failure
select @.chk=PWDCOMPARE ('unclechip',@.pwd)
-- do a check inside application , if the value returned
is "1" allow to login
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Whats going wrong?
Also I thank Tom for pointing the DPAPI but I have to
agree that I am not a application programmer and moreover
I didnt find examples on how to use the DPAPI from ASP 3.0
Sincerely
ChipHi,
There was a small bug in the code, Say if you do not have record for that
user, it returns a NULL values, While comparing NULL with 'unclechip' you
get a NULL and it reults in the failure. I have included a check in the
code.
if @.@.rowcount < 1
begin
print 'No User found'
return
end
New code is given below,
DECLARE @.pwd VARBINARY(128), @.chk TINYINT
-- trying to select password for a user whose record doesnt exist
SELECT @.pwd=WebPassword from FLogin where UserName ='blabla'
if @.@.rowcount < 1
begin
print 'No User found'
return
end
-- comparing : 1 is success, 0 is failure
select @.chk=PWDCOMPARE ('unclechip',@.pwd)
-- do a check inside application , if the value returned is "1" allow to
login
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Thanks
Hari
MCDBA
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:f4e901c40cc1$28b1aa10$a401280a@.phx.gbl...
> Hi Hari,
> I just made a modification in the DDL for the code u
> posted yesterday.
> In the below code if u observe, I did not Insert any
> record for a user 'blabla' into the table FLogin but I get
> the QA output "Login Successfully" when i query.
>
> CREATE TABLE [dbo].[FLogin] (
> [UserName] [nvarchar] (16) NOT NULL,
> [WebPassword] [varbinary] (128) NOT NULL,
> ) ON [PRIMARY]
> GO
> -- INSERTING ENCRYPTED value
> -- hard coded strings should be replaced by a text box
> value from a user interface
> INSERT INTO FLogin VALUES ('Chip',PWDENCRYPT ('unclechip'))
> -- the Decryption phase
> DECLARE @.pwd VARBINARY(128), @.chk TINYINT
> -- trying to select password for a user whose record
> doesnt exist
> SELECT @.pwd=WebPassword from FLogin where UserName
> ='blabla'
> -- comparing : 1 is success, 0 is failure
> select @.chk=PWDCOMPARE ('unclechip',@.pwd)
> -- do a check inside application , if the value returned
> is "1" allow to login
> if @.chk ! = 1
> Print 'Wrong Password Entered! Try Again'
> else
> Print 'Login Successfully'
>
> Whats going wrong?
> Also I thank Tom for pointing the DPAPI but I have to
> agree that I am not a application programmer and moreover
> I didnt find examples on how to use the DPAPI from ASP 3.0
> Sincerely
> Chip
>|||Hi,
Thanks for the Help!. Do you have any idea of how people
handle the existing encrypted data to avoid application
breaks, if any algorithm changes happen in the PWDENCRYPT
and PWDCOMPARE.
Also If any algorithm changes are done in any Un-
Documented functions, Do microsoft mention them in the
change list of Service Pack?.
Also I heard that Yukon will be a great product, Does
microsoft have any plans to inculde a password datatype?.
Sorry If my expectations are high.
Sincerely
Chip
>--Original Message--
>Hi,
>There was a small bug in the code, Say if you do not have
record for that
>user, it returns a NULL values, While comparing NULL
with 'unclechip' you
>get a NULL and it reults in the failure. I have included
a check in the
>code.
>if @.@.rowcount < 1
>begin
> print 'No User found'
> return
>end
>New code is given below,
>DECLARE @.pwd VARBINARY(128), @.chk TINYINT
>-- trying to select password for a user whose record
doesnt exist
>SELECT @.pwd=WebPassword from FLogin where UserName
='blabla'
>if @.@.rowcount < 1
>begin
> print 'No User found'
> return
>end
>-- comparing : 1 is success, 0 is failure
>select @.chk=PWDCOMPARE ('unclechip',@.pwd)
>-- do a check inside application , if the value returned
is "1" allow to
>login
>if @.chk ! = 1
>Print 'Wrong Password Entered! Try Again'
>else
>Print 'Login Successfully'
>Thanks
>Hari
>MCDBA
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f4e901c40cc1$28b1aa10$a401280a@.phx.gbl...
get
('unclechip'))
moreover
3.0
>
>.
>|||Chip,
No, you cannot count on any help or info from MS if and when they change the
implementation or usage of these procedures. This is one of the reasons to
not use undocumented functionality. And, indeed the interface and
implementation of these did change between (I think it was) 6.5 and 7.0. So
it is not unlikely it will happen again.
Regarding Yukon, I have not seen anything in this area. You can always wish
at sqlwish@.microsoft.com, but I doubt they will have time to add such a
feature at this late stage. I can have missed it, of course.
I recommend that you use something external to SQL Server, for above
reasons.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
news:c88201c40cd4$2155d5d0$a601280a@.phx.gbl...
> Hi,
> Thanks for the Help!. Do you have any idea of how people
> handle the existing encrypted data to avoid application
> breaks, if any algorithm changes happen in the PWDENCRYPT
> and PWDCOMPARE.
> Also If any algorithm changes are done in any Un-
> Documented functions, Do microsoft mention them in the
> change list of Service Pack?.
> Also I heard that Yukon will be a great product, Does
> microsoft have any plans to inculde a password datatype?.
> Sorry If my expectations are high.
> Sincerely
> Chip
> record for that
> with 'unclechip' you
> a check in the
> doesnt exist
> ='blabla'
> is "1" allow to
> message
> get
> ('unclechip'))
> moreover
> 3.0|||Hi Tibor,
Thanks for the valuable information.Do you have idea of
any third party encryption tools for SQL Server database
tables ?
Sincerely
Chip
>--Original Message--
>Chip,
>No, you cannot count on any help or info from MS if and
when they change the
>implementation or usage of these procedures. This is one
of the reasons to
>not use undocumented functionality. And, indeed the
interface and
>implementation of these did change between (I think it
was) 6.5 and 7.0. So
>it is not unlikely it will happen again.
>Regarding Yukon, I have not seen anything in this area.
You can always wish
>at sqlwish@.microsoft.com, but I doubt they will have time
to add such a
>feature at this late stage. I can have missed it, of
course.
>I recommend that you use something external to SQL
Server, for above
>reasons.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
>news:c88201c40cd4$2155d5d0$a601280a@.phx.gbl...
PWDENCRYPT
datatype?.
have
included
returned
I
box
returned
ASP
>
>.
>|||I'm afraid not. I suggest you search the ng archives for that and if you
don't find it, you might wan to create a new post with appropriate subject
(unless someone else jumps in here, of course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:b04201c40d09$41d71d20$a001280a@.phx.gbl...
> Hi Tibor,
> Thanks for the valuable information.Do you have idea of
> any third party encryption tools for SQL Server database
> tables ?
> Sincerely
> Chip