Storing sensitive data in db SQL server using c #
I am working on a C # Winforms application that needs to access the SSN of employees. We store data in a SQL Server database. Obviously, we cannot store numbers in plaintext in the database. We need to store them in some kind of encrypted format.
What's the best way to keep data encrypted but then allow my application to decrypt the data?
It is important to note that this is an internal application and no data will be transmitted over the Internet.
source to share
You can try MSDN Cryptography Service, http://msdn.microsoft.com/en-us/library/system.security.cryptography%28v=vs.110%29.aspx , for example:
using System.Security.Cryptography;
private string Crypt(string s_Data, bool b_Encrypt)
{
string s_Password = "... your password ...";
byte[] u8_Salt = new byte[] { 0x26, 0x19, 0x81, 0x4E, 0xA0, 0x6D, 0x95, 0x34, 0x26, 0x75, 0x64, 0x05, 0xF6 };
PasswordDeriveBytes i_Pass = new PasswordDeriveBytes(s_Password, u8_Salt);
Rijndael i_Alg = Rijndael.Create();
i_Alg.Key = i_Pass.GetBytes(32);
i_Alg.IV = i_Pass.GetBytes(16);
ICryptoTransform i_Trans = (b_Encrypt) ? i_Alg.CreateEncryptor() : i_Alg.CreateDecryptor();
MemoryStream i_Mem = new MemoryStream();
CryptoStream i_Crypt = new CryptoStream(i_Mem, i_Trans, CryptoStreamMode.Write);
byte[] u8_Data;
if (b_Encrypt) { u8_Data = Encoding.Unicode.GetBytes(s_Data); }
else
{
try { u8_Data = Convert.FromBase64String(s_Data); }
catch { return null; }
}
try
{
i_Crypt.Write(u8_Data, 0, u8_Data.Length);
i_Crypt.Close();
}
catch { return string.Empty; }
if (b_Encrypt) return Convert.ToBase64String(i_Mem.ToArray());
else return Encoding.Unicode.GetString(i_Mem.ToArray());
}
source to share
You should probably double the encryption of the data, especially if you also got names in the same data table. The above method will provide data from a code point of view, but if you have a malicious developer in your employees it will be easy for them to get the data.
In addition to user3806621's solution, you should also look at encryption on SQL Server - see link MSDN article
However, you may also have a number of data protection issues that may arise depending on your geographic location.
source to share