CODEWITHSUNDEEP
c#sqlsql-serverencryption
user3727001
user3727001

Reputation: 59

Storing sensitive data in SQL server db with C#

I am working on a Winforms application in C# that will need to access employee SSNs. We store the data in a SQL Server database. Obviously we can't store the numbers in plaintext in the database. We need to store them in some sort of encrypted format.

What is the best way to go about storing the data in an encrypted way but then allowing my application to decrypt the data?

It is important to note that this is an in house application and no data will be transmitted over the internet.

Upvotes: 4

Views: 2005

Answers (2)

Swomble
Swomble

Reputation: 909

You should probably double encrypt the data especially if you've also got the names in the same data table. The above method will secure the data from a code point of view but if you've got a malicious developer on your staff it'd be easy for them to get the data.

In addition to the solution by user3806621 you should also look at encryption on the SQL server - see this link MSDN article

However, you might also have a number of Data Protection issues to deal with depending on your geographical location.

Upvotes: 0

user3806621
user3806621

Reputation: 288

You can try MSDN cryptographic service, http://msdn.microsoft.com/en-us/library/system.security.cryptography%28v=vs.110%29.aspx , for a 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());
    }

Upvotes: 1

Related Questions