It depends on your needs. There are various encryption methods that can be used both internal and external to SQL. Usually its a trade-off between performance and the quality of encryption.

From what I have read the use of Asymmetric keys is the most secure method, but its the slowest. So you could use that with the function EncryptByAsmKey(). Microsoft documentation indicates to use a symmetric key for better performance. And finally encrypt by password is the weakest.

For general password I have always used external code like .net to hash and store passwords as they are stored/retrievefrom the database. I always placed it there because until recent version it was never very robust to store using SQL Code.

If you don't want to be able to retrieve the password I think the HashBytes is a sufficient method to use inside the database for general security.