Storing Password in the Sql server database in a secure way.

"Forgor password" functionality does not mean sending old plain text password to the user. Not at all. Actually this is the most vulnerable point in an application, since you have no control over what's happening. Generating one-time-password and sending that one to the user and forcing the user to change it is common approach, although not more secure than sending the old password. Why? Because there is only one channel and one component is involved.

If you want to identify somebody, you have three components to use, you can ask three kind of "questions".
You can be identified using these components:
1) what you have - a hard or soft token, smart card, a phone
2) what you know - a password, an answer to a question
3) what you are - biometrical data
For a general web application it is hard to implement the third component. Still, you have two left. You should use these two.

Still, most pages use only the second component, but at least they are using at least two different aspects of it, like passwords and secret questions. If your application is not that critical, you can do the same.

This is a good starting point: https://www.owasp.org/index.php/Forgot_Password_Cheat_Sheet[^], and some more thoughts here: http://www.securabit.com/wp-content/uploads/2010/08/self-service-password-reset_v5-1.pdf[^]

Encrypt using the master key..

-- Create database Key
USE Hassan_Web_Project_DB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

-- Create self signed certificate
USE Hassan_Web_Project_DB;
GO
CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO

-- Create symmetric Key - This is used to both encrypt and decrypt our data
USE Hassan_Web_Project_DB;
GO
CREATE SYMMETRIC KEY SymmetricKey1
 WITH ALGORITHM = AES_128
 ENCRYPTION BY CERTIFICATE Certificate1;
GO

Now insert by encrypting

INSERT INTO dbo.User_Login (User_ID, User_Name, User_Password)
VALUES (25665, 'mssqltips4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'4545-58478-1245') ) )

And select by decrypting

SELECT User_ID, User_Password AS 'Encrypted User_Password',
CONVERT(varchar, DecryptByKey(User_Password)) AS 'Decrypted User_Password'
FROM dbo.User_Login;

Generates a random password, assigns it to the user account, sends an email informing the user of their new password
- http://www.asp.net/web-forms/tutorials/security/admin/recovering-and-changing-passwords-cs[^]
- http://www.fishnetsecurity.com/6labs/resource-library/white-paper/best-practices-secure-forgot-password-feature[^]