Question Encrypt/decrypt a column in a table

[raysefo]

Hi,

I am using SQL Server 2016 and I wonder if there is a way to encrypt/decrypt a column in a table? Let's say I have a table and in this table, I have serials column. I would like to encrypt serials when I save it into this table and I want to decrypt serials when I query this column.

Best Regards.

 

[jmcilhinney]

I didn't know the answer to your question so I went to Bing (I find Bing superior to Google for .NET develeopment) and searched for "sql server encryption" and it took me less than a minute to be reading this:

Encrypt a Column of Data - SQL Server & Azure Synapse Analytics & Azure SQL Database & SQL Managed Instance

Learn how to encrypt a column of data by using symmetric encryption in SQL Server using Transact-SQL, sometimes known as column-level or cell-level encryption.

docs.microsoft.com

I don't want to discourage people from using this forum when they encounter a genuine issue but if you're not even bothering to use a search engine before posting then you're not really trying to help yourself. If you want to be a good developer then you need to learn how to find information and searching for "sql server encryption" when you want to know how to perform encryption in SQL Server is a rather obvious first step. For the stuff you can't find or the stuff you find that you can't understand, we're here to help.

 

[NoUserHere]

I don't know if this is what you're after, but assuming you are looking to encrypt at the application level, you could use something like this and insert a encrypted text into the field through a command statement ::

Pay attention to the highlighted lines. If your key is two hundred and fifty six as I have defined it, you will want to divide by 8, so when passing what you need encrypted/decrypted to the methods, ensure int divideby is set to 8, unless you change the kysize constant. This IV in bytes must be equal to (keysize divided by eight). As the default keysize is two hundred and fifty six, so then the initVector must also be thirty two bytes long. With a string of sixteen characters, thus gives us 32 bytes when converted to a byte array.

   private const int intKeySize = 256;
    private const string initVector = "Goe4GxPqZhKe31N1";
    public static string Encrypt(string plainText, string passPhrase, int divideby)
    {
        byte[] vectorByteArray = Encoding.UTF8.GetBytes(initVector);
        byte[] plainByteArray = Encoding.UTF8.GetBytes(plainText);
        PasswordDeriveBytes derivePassBytes = new PasswordDeriveBytes(passPhrase, null);
        byte[] keyBytes = derivePassBytes.GetBytes(intKeySize / divideby);
        RijndaelManaged metricKey = new RijndaelManaged();
        metricKey.Mode = CipherMode.CBC;
        ICryptoTransform doEncrypt = metricKey.CreateEncryptor(keyBytes, vectorByteArray);
        MemoryStream mStream = new MemoryStream();
        CryptoStream cryptoWrite = new CryptoStream(mStream, doEncrypt, CryptoStreamMode.Write);
        cryptoWrite.Write(plainByteArray, 0, plainByteArray.Length);
        cryptoWrite.FlushFinalBlock();
        byte[] cipherTextBytes = mStream.ToArray();
        mStream.Close();
        cryptoWrite.Close();
        return Convert.ToBase64String(cipherTextBytes);
    }

To decrypt you can use the following when receiving the encrypted field through ie a reader or when using executescaler etc.

    public static string Decrypt(string cipherthis, string passTechnique, int divideby)
    {
        byte[] vectorByteArray = Encoding.UTF8.GetBytes(initVector);
        byte[] cipherByteArray = Convert.FromBase64String(cipherthis);
        PasswordDeriveBytes dpb = new PasswordDeriveBytes(passTechnique, null);
        byte[] keyBytes = dpb.GetBytes(intKeySize / divideby);
        RijndaelManaged metricKey = new RijndaelManaged();
        metricKey.Mode = CipherMode.CBC;
        ICryptoTransform doDecrypt = metricKey.CreateDecryptor(keyBytes, vectorByteArray);
        MemoryStream mStream = new MemoryStream(cipherByteArray);
        CryptoStream cryptoRead = new CryptoStream(mStream, doDecrypt, CryptoStreamMode.Read);
        byte[] textByteArray = new byte[cipherByteArray.Length];
        int decryptedByteCount = cryptoRead.Read(textByteArray, 0, textByteArray.Length);
        mStream.Close();
        cryptoRead.Close();
        return Encoding.UTF8.GetString(textByteArray, 0, decryptedByteCount);
    }

You would encrypt like this : Encrypt(Password, Username, 8); *Example*
And decrypt the call like this : Decrypt(result2.ToString(), result1.ToString(), 8)); *Example*

You could also make it a bit more secure by adding extra security. Anyway, hope this helps.