pwdencrypt vs sql server 2005

Hi,
I store application passwords in a sql server 2000 table. The passwords are
encrypt with the pwdencrypt function.
I have to migrate the database to sql server 2005. I would like to use the
new crypt function in sql server 2005
(EncryptByPassPhrase/DecryptByPassPhrase).
How can I decrypt old password (crypt with pwdencrypt) and recrypt them with
the new sql server 2005 function ? I know that pwdencrypt is still support i
n
sql server 2005, but for how long ?
Thanks"shwac" <shwac@.discussions.microsoft.com> wrote in message
news:8C9DDD58-17B0-4E9C-A2DC-D72CBBB4ECD0@.microsoft.com...
> Hi,
> I store application passwords in a sql server 2000 table. The passwords
> are
> encrypt with the pwdencrypt function.
> I have to migrate the database to sql server 2005. I would like to use the
> new crypt function in sql server 2005
> (EncryptByPassPhrase/DecryptByPassPhrase).
> How can I decrypt old password (crypt with pwdencrypt) and recrypt them
> with
> the new sql server 2005 function ? I know that pwdencrypt is still support
> in
> sql server 2005, but for how long ?
> Thanks
>
You cannot decrypt the output of pwdencrypt because, despite the name, it
isn't an encryption function at all - it's a hash function. The
complementary function is pwdcompare which compares a hash with the hash of
a password string you supply. "Supported" isn't quite right either. These
functions are undocumented so Microsoft is under no obligation to support
them. I believe that breaking changes have applied to these functions in the
past and may well do so again.
Passwords should generally be hashed rather than encrypted. Use the
HashBytes function rather than the Encrypt functions and implement policies
for password length, quality and expiry. That's assuming you need to create
your own password authentication mechanism. If you can use integrated
security, certificates or other mechanisms then do so.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Pwdcompare()

I have a Oracle view which has user-info : user-name , passwords. Passwords in this view are encrypted. I exported this view to SQL Server. Can i use PWDCOMPARE(<plaintext>, <encryptedtext>) function on exported table to compare the Passwords?I don't think this is going to resolve your issue. Encryption done by Oracle is *proprietary* to Oracle. Pdwcompare() is proprietary to SQL. I also want to note that the use of this undocumented function is not supported by MS and they could change its behavior at anytime.|||With pwd.. functions and large strings, ANY user can crash pre-SP3 sql server!|||So how can i secure the user-info table which has "user-name & password" in SQL Server. Is thier a way that i can hide the password?

Thanks|||Do not save passwords in open format, even encrypted.
If your table is used to authorize users, store hash of password,
for example SHA1. Try http://www.activecrypt.com.

Good luck !|||Hi ispaleny,

Thank you for the link. It was really helpful. I just went through the link. It defines some encrytion algorithms. I am using SQL Server 7, and it doesn't allow "CREATE FUNCTION". Is their any other alternative?

Thanks|||Use stored procedure to encrypt row by row.|||I am able to use the stored procedure to encrypt each row one by one. But i am facing problem comparing the password.

I tried using "exec <storedprocedure name> " in the select statement and it doesn't allow me to do so. i want to use the return value of the stored procedure in the SELECT statement. I wrote stored procedure with two input variable and one output variable. I want to use the value of this output variable in the select statement.

Thanks|||Is this what you want ?

declare @.Ret bit
exec usp_YourSP '&^%','@.$#%^^$^#',@.Ret OUTPUT
select * from YourTable where YourCol=@.Ret|||No I was trying to use something like this

Select * from table1 where col1 = exec <storedprocedure> @.Ret OUTPUT.

I wanted call this stored procedure in my servlet program. I got that working, by using CallableStatement. Link you posted http://www.activecrypt.com. was very helpful. Thank you.