Although it is 1) undocumented, and 2) less secure than other libraries, it is the only method that SQL Server provides, and is certainly better than storing passwords in clear text.

Although the password must be transmitted in clear text, it would need to be whether or not you encrypt the password in the database.  What these functions do, however, is eliminate or reduce a point-of-failure: an unauthorized database user reading the passwords from the database (or a backup, which happens too often for comfort).

A better method for an enterprise-level solution would be to handle all the encryption and comparisons in the application itself, using more secure libraries, and only store and retrieve the encrypted value in the database.