Encryption

I want to do encryption of the password field in the database. I have read about the encrypt(), pwdencrypt(), pwdcompare() functions. Here are some questions that i am concerned about.

a) Does using a standard SQL function that could be called in a script make the data susceptible to dictionary-style attacks?

b) How long would the field need to be in the database and of what type to store the encrypted text?

But is there any other way other than the above three functions so that i can have the contents of the field encrypted?? Please let me know if there any encryption algorithms are available.

Please let me know.

(a) Any encryption scheme is vulnerable to a dictionary attack. It is basically a brute force technique, so the defence against this is to not use passwords that can be found in a dictionary.
(b) VARBINARY(255) ought to be long enough (at least most of the docs I've seen indicate this).

Be aware though that these functions are undocumented by Microsoft. So they don't have to necessarily support/port them to future versions of SQL Server. Also, the algorithm was changed between SQL Server 6.5 and 7.0, so data encrypted with 6.5's PWDENCRYPT would not equal the password which was compared with 7.0's PWDCOMPARE. This caused programs to break for people who were upgrading their databases from 6.5 to 7.0. Who knows, maybe Microsoft may change the encryption scheme again in the next version of SQL server.

You could always use the front end language (PHP, Perl etc.) to do an MD5 hash and store that into the database. This might be a safer alternative to using an undocumented MS function

More reading: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9809