Example code Help!

Dear folks,
I am searching for example codes for encryption and
decryption of a value for a column in SQL Server 2000.
Last week Roji P Thomas suggested me the Undocumented
functions PWDENCRYPT and PWDCOMAPRE but I dint find any
example code in Google search on how to use them. I badly
need some examples as I already have 30 thousand Password
values in clear text and soon they increase in lakhs. Here
is a scenario at present.
The user will register for a web based service and his
username and passcode details are stored in clear text :-(
I need some guidance on how to encrypt for a given clear
text input and store in DB table and decrypt the same when
needed.
Thanks for all the help
sincerely
ChipSee if this helps
http://www.sswug.org/searchresults...crypt%20Encrypt
Ray Higdon MCSE, MCDBA, CCNA
--
"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
news:e99201c40c0c$ca210900$a301280a@.phx.gbl...
> Dear folks,
> I am searching for example codes for encryption and
> decryption of a value for a column in SQL Server 2000.
> Last week Roji P Thomas suggested me the Undocumented
> functions PWDENCRYPT and PWDCOMAPRE but I dint find any
> example code in Google search on how to use them. I badly
> need some examples as I already have 30 thousand Password
> values in clear text and soon they increase in lakhs. Here
> is a scenario at present.
> The user will register for a web based service and his
> username and passcode details are stored in clear text :-(
> I need some guidance on how to encrypt for a given clear
> text input and store in DB table and decrypt the same when
> needed.
> Thanks for all the help
> sincerely
> Chip
>|||Hi,
The PWDENCRYPT and PWDCOMPARE functions are used to encrypt and compare DATA
passwords are not visible in anywhere in the database.
Example code
Create table users ( userid int identity (1,1) not null, pswd varbinary
(128))
-- INSERTING ENCRYPED value
-- hard coded string should be replace
-- by a text box value from screen
Insert into users values (PWDENCRYPT ('hari prasad'))
declare @.pwd varbinary(128) , @.chk tinyint
-- the dencryption phase
select @.pwd=pswd from users where userid = 1
-- comparing : 1 is success, 0 is not
select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
do a check inside application , if the value returned is "1" allow to login
.
The only pre-requisite to do the password encrytion is, the password column
(field) should be defined with 'varbinary' data type.
Thanks
Hari
MCDBA
"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
news:e99201c40c0c$ca210900$a301280a@.phx.gbl...
> Dear folks,
> I am searching for example codes for encryption and
> decryption of a value for a column in SQL Server 2000.
> Last week Roji P Thomas suggested me the Undocumented
> functions PWDENCRYPT and PWDCOMAPRE but I dint find any
> example code in Google search on how to use them. I badly
> need some examples as I already have 30 thousand Password
> values in clear text and soon they increase in lakhs. Here
> is a scenario at present.
> The user will register for a web based service and his
> username and passcode details are stored in clear text :-(
> I need some guidance on how to encrypt for a given clear
> text input and store in DB table and decrypt the same when
> needed.
> Thanks for all the help
> sincerely
> Chip
>|||To add to the other responses, I strongly suggest you reconsider using this
undocumented functionality in production code. The algorithms may change
between SQL Server versions/service packs and break your application.
There are third-party tools available to do this or you can roll your own.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
news:e99201c40c0c$ca210900$a301280a@.phx.gbl...
> Dear folks,
> I am searching for example codes for encryption and
> decryption of a value for a column in SQL Server 2000.
> Last week Roji P Thomas suggested me the Undocumented
> functions PWDENCRYPT and PWDCOMAPRE but I dint find any
> example code in Google search on how to use them. I badly
> need some examples as I already have 30 thousand Password
> values in clear text and soon they increase in lakhs. Here
> is a scenario at present.
> The user will register for a web based service and his
> username and passcode details are stored in clear text :-(
> I need some guidance on how to encrypt for a given clear
> text input and store in DB table and decrypt the same when
> needed.
> Thanks for all the help
> sincerely
> Chip
>|||Hi Ray & Hari & Dan!
Thanks for the inputs. Dan has planted a bomb in my heart,
which makes sense. where should i go now? Do u guys
encountered any thirdpaty tools at low cost or somebody
developed such tool for free to favour SQL Community.
Sincerely
Chip
>--Original Message--
>To add to the other responses, I strongly suggest you
reconsider using this
>undocumented functionality in production code. The
algorithms may change
>between SQL Server versions/service packs and break your
application.
>There are third-party tools available to do this or you
can roll your own.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
>news:e99201c40c0c$ca210900$a301280a@.phx.gbl...
badly
Password
Here
text :-(
when
>
>.
>|||If you search the MSDN website for DPAPI, you'll find some very useful
articles.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:eb5f01c40c29$55e91e70$a301280a@.phx.gbl...
Hi Ray & Hari & Dan!
Thanks for the inputs. Dan has planted a bomb in my heart,
which makes sense. where should i go now? Do u guys
encountered any thirdpaty tools at low cost or somebody
developed such tool for free to favour SQL Community.
Sincerely
Chip
>--Original Message--
>To add to the other responses, I strongly suggest you
reconsider using this
>undocumented functionality in production code. The
algorithms may change
>between SQL Server versions/service packs and break your
application.
>There are third-party tools available to do this or you
can roll your own.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
>news:e99201c40c0c$ca210900$a301280a@.phx.gbl...
badly
Password
Here
text :-(
when
>
>.
>|||Hi Hari,
I just made a modification in the DDL for the code u
posted.
In the below code if u observer, I did not Insert any
record for a user 'blabla' into the table FLogin but I get
the QA output "Login Successfully".
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 API from ASP 3.0.
Sincerely
Chip
>--Original Message--
>Hi,
>The PWDENCRYPT and PWDCOMPARE functions are used to
encrypt and compare DATA
>passwords are not visible in anywhere in the database.
>Example code
>Create table users ( userid int identity (1,1) not null,
pswd varbinary
>(128))
>-- INSERTING ENCRYPED value
>-- hard coded string should be replace
>-- by a text box value from screen
>Insert into users values (PWDENCRYPT ('hari prasad'))
>declare @.pwd varbinary(128) , @.chk tinyint
>-- the dencryption phase
>select @.pwd=pswd from users where userid = 1
>-- comparing : 1 is success, 0 is not
>select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
>if @.chk ! = 1
>Print 'Wrong Password Entered! Try Again'
>else
>Print 'Login Successfully'
>do a check inside application , if the value returned
is "1" allow to login
>..
>The only pre-requisite to do the password encrytion is,
the password column
>(field) should be defined with 'varbinary' data type.
>Thanks
>Hari
>MCDBA
>"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
>news:e99201c40c0c$ca210900$a301280a@.phx.gbl...
badly
Password
Here
text :-(
when
>
>.
>