Encrypt and Decrypt Passwords in SQL Server 2008.

Question

Hi..

I want to form a query in SQL.

My Requirement is-
I have a table - (Table Name - Users)

ID	UserName	Password	EncryptedPassword	Location	IsActive
1	abc	abc	0x7890000	US	1
2	def	def	0x88776655	UK	1
3	ghi	ghi	0x1122334455	India	1
4	jkl	jkl	0x987654321234	Australia	1

In EncryptedPassword field, for inserting data, I am passing PWDENCRYPT(@Password).
This has some varbinary values.

I want to fire a select query for checking whether the username and password are correct or not.

My query is-

select Username, PWDCOMPARE(Password, PWDENCRYPT(EncryptPassword)) PasswordCompare, Location from Users where IsActive=1

Here, even if I pass correct password, I get the PasswordCompare field as 0.

How to encrypt and decrypt passwords in SQL Server??

Please help.

Manish Kumar Choudhary · Answer

Use using System.Security.Cryptography; on the top of ur page.

Riddhi Valecha · Answer

Hi... I am getting an error at line - using (Aes encryptor = Aes.Create()) Please explain what is Aes encryter.

Manish Kumar Choudhary · Answer

Hi Riddhi Valecha  use above class EncryptDecrept. Encrypt( ) it will return password in encrypted format stored that to ur table. When u access that password in login either decrepit that password and compare to the password enter by user or u can also encrypt the enter password and then compare both password. Try this it will work .

Riddhi Valecha · Answer

Hi Manish... My target is to keep the passwords in UserMaster table in encrypted format. I tried using PWDENCRYPT and PWDCOMPARE but I failed in using them.

Manish Kumar Choudhary · Answer

Use this class for Encrypt/ Decrypt public class EncryptDecrept { #region Encrypt Password /// /// This function is used for Encrypt password for user. /// /// public static string Encrypt( string clearText) { string EncryptionKey = ConfigurationManager.AppSettings[ Keywil ].ToString(); byte [] clearBytes = Encoding.Unicode.GetBytes(clearText); using (Aes encryptor = Aes.Create()) { Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey new byte [] { 0x49 0x76 0x61 0x6e 0x20 0x4d 0x65 0x64 0x76 0x65 0x64 0x65 0x76 }); encryptor.Key = pdb.GetBytes(32); encryptor.IV = pdb.GetBytes(16); using (MemoryStream ms = new MemoryStream()) { using (CryptoStream cs = new CryptoStream(ms encryptor.CreateEncryptor() CryptoStreamMode.Write)) { cs.Write(clearBytes 0 clearBytes.Length); cs.Close(); } clearText = Convert .ToBase64String(ms.ToArray()); } } return clearText; } #endregion #region Decrypt Password /// /// This function is used for Decrypt password for user. /// /// public static string Decrypt( string cipherText) { string EncryptionKey = ConfigurationManager.AppSettings[ Keywil ].ToString(); cipherText = cipherText.Replace(    + ); byte [] cipherBytes = Convert .FromBase64String(cipherText); using (Aes encryptor = Aes.Create()) { Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey new byte [] { 0x49 0x76 0x61 0x6e 0x20 0x4d 0x65 0x64 0x76 0x65 0x64 0x65 0x76 }); encryptor.Key = pdb.GetBytes(32); encryptor.IV = pdb.GetBytes(16); using (MemoryStream ms = new MemoryStream()) { using (CryptoStream cs = new CryptoStream(ms encryptor.CreateDecryptor() CryptoStreamMode.Write)) { cs.Write(cipherBytes 0 cipherBytes.Length); cs.Close(); } cipherText = Encoding.Unicode.GetString(ms.ToArray()); } } return cipherText; } #endregion }  /> add it in web.config

Joginder Banger · Answer

Hi Riddhi try link have two function one is encrypt and another decrypt. link