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

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
Tony
I believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>
|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
..NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
David Portas
SQL Server MVP
|||I am Tony's coworker and using .NET to encrypt the password is not an
attractive option because it means that we cannot operate the database
without .NET. It makes the business tier and database tier tightly
coupled or to use some of my old C++ jargon "It breaks encapsulation!"
If we were using Yukon, then .NET would be the way to go.
Cheers
Ted
*** Sent via Developersdex http://www.codecomments.com ***

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
TonyI believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
.NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
--
David Portas
SQL Server MVP
--

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
TonyI believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
.NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
David Portas
SQL Server MVP
--|||I am Tony's coworker and using .NET to encrypt the password is not an
attractive option because it means that we cannot operate the database
without .NET. It makes the business tier and database tier tightly
coupled or to use some of my old C++ jargon "It breaks encapsulation!"
If we were using Yukon, then .NET would be the way to go.
Cheers
Ted
*** Sent via Developersdex http://www.codecomments.com ***