Encrypt and Decrypt Passwords in SQL Server 2008.

Riddhi Valecha

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.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar Choudhary11y ago
Use
using System.Security.Cryptography;
on the top of ur page.
Riddhi Valecha11y ago
Hi...

I am getting an error at line - using (Aes encryptor = Aes.Create())

Please explain what is Aes encryter.
Manish Kumar Choudhary11y ago
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 Valecha11y ago
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 Choudhary11y ago
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 Banger11y ago
Hi Riddhi

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

link