How To Secure PII Data In SQL Server Using .NET Assembly

PII (Personally Identifiable Information) is used to identify individual identity markers such as SSN, DOB, Bank Account Number, etc. When you are working with a financial application, you face a scenario where you have to store the data in an encrypted format so that no one can see the actual data.

While showing this data on the UI screen, it needs to be decrypted and masked as well. It means we need to mask the PII data before sending it to the UI. As you have seen before, your account number, credit card number, mobile number, and SSN, all are obfuscated (ex-Mobile SSN- XXXXX2398).

To secure the PII data in the database, the following technique can be used.

.NET Assembly
CLE (Column-level Encryption)
TDE (Transparent Data Encryption)

In this article, we will see how we can use the .NET assembly in SQL Server to secure the information.

Rijndael algorithm is used in the below example. To encrypt and decrypt, we use cryptography concepts, and there are many algorithms that are used to encrypt and decrypt the data.

The only thing is that we need to secure the key. The key can be read from a flat file, app. config, etc.

I have hard-coded the key in the library file itself but you can use it as per your requirement. I have also used the console application to demonstrate whether the Encryption/Decryption is working or not. Once the library project is created, the assembly can be used in SQL Server.

Column Level Encryption with real time scenarios will be explained in the next article.

Step 1. Create your library project.

SQL server

Step 2. Write the below code. So, your class will have two public methods: Encrypt and Decrypt.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace EncryptDecrypt.Library
{
    /// <summary>
    ///    
    /// </summary>
    public sealed class AES_EncryptDecrypt
    {
        Static readonly string keyData = "su@@@###su&BLSKF";

        #region Encrypt

        /// <summary>
        ///    
        /// </summary>
        /// <param name="inputData"></param>
        /// <param name="pass"></param>
        /// <returns></returns>
        public static string Encrypt(string inputData)
        {
            try
            {
                return Convert.ToBase64String(EncryptStringToBytes(inputData, Encoding.Default.GetBytes(keyData)));
            }
            catch (Exception ex)
            {
                return "";
            }

        }

        /// <summary>
        ///    
        /// </summary>
        /// <param name="plainText"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static byte[] EncryptStringToBytes(string plainText, byte[] key)
        {
            return EncryptStringToBytes(plainText, key, null);
        }

        /// <summary>
        ///    
        /// </summary>
        /// <param name="plainText"></param>
        /// <param name="key"></param>
        /// <param name="IV"></param>
        /// <returns></returns>
        public static byte[] EncryptStringToBytes(string plainText, byte[] key, byte[] IV)
        {
            if ((plainText == null) || (plainText.Length <= 0))
            {
                throw (new ArgumentNullException("PlainText"));
            }
            if ((key == null) || (key.Length <= 0))
            {
                throw (new ArgumentNullException("PlainText"));
            }
            RijndaelManaged rijManaged = new RijndaelManaged();
            rijManaged.Key = key;
            if (!(IV == null))
            {
                if (IV.Length > 0)
                {
                    rijManaged.IV = IV;
                }
                else
                {
                    rijManaged.Mode = CipherMode.ECB;
                }
            }
            else
            {
                rijManaged.Mode = CipherMode.ECB;
            }

            byte[] encryptedData = null;
            ICryptoTransform iCryptoTransform = rijManaged.CreateEncryptor();
            encryptedData = iCryptoTransform.TransformFinalBlock(Encoding.Default.GetBytes(plainText), 0, plainText.Length);

            return encryptedData;

        }

        #endregion

        #region Decrypt

        /// <summary>
        ///    
        /// </summary>
        /// <param name="input"></param>
        /// <param name="pass"></param>
        /// <returns></returns>
        public static string Decrypt(string inputData)
        {
            try
            {
                return DecryptStringFromBytes(Convert.FromBase64String(inputData), Encoding.Default.GetBytes(keyData));
            }
            catch (Exception ex)
            {
                return "";
            }

        }

        /// <summary>
        ///    
        /// </summary>
        /// <param name="cipherText"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string DecryptStringFromBytes(byte[] cipherText, byte[] key)
        {
            return DecryptStringFromBytes(cipherText, key, null);
        }

        /// <summary>
        ///    
        /// </summary>
        /// <param name="cipherText"></param>
        /// <param name="key"></param>
        /// <param name="IV"></param>
        /// <returns></returns>
        public static string DecryptStringFromBytes(byte[] cipherText, byte[] key, byte[] IV)
        {
            if ((cipherText == null) || (cipherText.Length <= 0))
            {
                throw (new ArgumentNullException("cipherText"));
            }
            if ((key == null) || (key.Length <= 0))
            {
                throw (new ArgumentNullException("key"));
            }
            RijndaelManaged rijManaged = new RijndaelManaged();
            rijManaged.Key = key;
            rijManaged.Mode = CipherMode.CBC;
            if (!(IV == null))
            {
                if (IV.Length > 0)
                {
                    rijManaged.IV = IV;
                }
                else
                {
                    rijManaged.Mode = CipherMode.ECB;
                }
            }
            else
            {
                rijManaged.Mode = CipherMode.ECB;
            }

            string PlainData = null;
            ICryptoTransform iCryptoTransform = rijManaged.CreateDecryptor(rijManaged.Key, rijManaged.IV);
            MemoryStream msDecrypt = new MemoryStream(cipherText);
            CryptoStream csDecrypt = new CryptoStream(msDecrypt, iCryptoTransform, CryptoStreamMode.Read);
            StreamReader srDecrypt = new StreamReader(csDecrypt);
            PlainData = srDecrypt.ReadToEnd();

            return PlainData;

        }

        #endregion
    }
}

Step 3. Just to ensure that your library project is working fine, you can create a Console application and add the project reference into it.

using EncryptDecrypt.Library;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EncryptDecrypt.ConsoleUI
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Enter the string to be encrypted");
            string value = Console.ReadLine();
            string encryptedData = AES_EncryptDecrypt.Encrypt(value);

            Console.WriteLine("Encrypted Data: " + encryptedData);

            string decryptedData = AES_EncryptDecrypt.Decrypt(encryptedData);

            Console.WriteLine("Decrypted Data: " + decryptedData);
        }
    }
}

Step 4. Your library is ready. Now, you can use it in your SQL Server.

Step 5. Open SQL Server and select the database where you want to have the assembly.

SQL server database

First, create the assembly as shown in the above screen. The path I have given is as per my location.

My location

The path may be different in your case. So, create the assembly in the specific database from the given path. It will create the assembly like in the below screen.

Create the assembly

Step 6. Enable CLR in your database.

USE SampleDemo
GO

SP_Configure 'clr enable', 1
GO

Reconfigure
GO

Step 7. Create the Encrypt and Decrypt function in your Database.

USE SampleDemo
GO

CREATE FUNCTION Encrypt(@string NVARCHAR(100))
RETURNS NVARCHAR(100) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [EncryptDecryptLibrary].[EncryptDecrypt.Library.AES_EncryptDecrypt].[Encrypt]
GO

USE SampleDemo
GO

CREATE FUNCTION Decrypt(@string NVARCHAR(100))
RETURNS NVARCHAR(100) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [EncryptDecryptLibrary].[EncryptDecrypt.Library.AES_EncryptDecrypt].[Decrypt]
GO

Now, you have assembly and function in your database. You can use these functions to encrypt and decrypt your table data. I have created a Customer table with four columns - ID, Name, AccountNumber, and SSN. ID is the identity column and is set to the primary key.

Step 8. Create a stored procedure to insert the data into your table.

Insert data in your table

So in this case, when you are calling your stored procedure from your code, the data will be encrypted and stored in the table.

Step 9. To decrypt and mask the data, first, we create a function for masking the SSN number.

CREATE FUNCTION SSN_Masking(@input nvarchar(100))
RETURNS nvarchar(100)
AS
BEGIN
    DECLARE @data nvarchar(100)
    SELECT @data = 'XXXXX' + SUBSTRING(@input, 6, 4)
    RETURN @data
END

Once the function is created, we can create a stored procedure to get the decrypted and masked data which can be shown on the UI screen.

Step 10. Create the stored procedure to get the masked and decrypted data based on the ID.

CREATE PROC usp_GetCustomerById
(
    @ID bigint
)
AS
BEGIN
    SELECT ID, Name, dbo.Decrypt(accountnumber) AS AccountNumber, dbo.SSN_Masking(dbo.Decrypt(SSN)) AS SSN FROM customer WHERE ID = @ID
END

You can see the below output when you call the stored procedure. This data can be shown on the UI screen.

UI screen

Conclusion

We can leverage the C# and SQL Server features to secure the PII data and show it in an obfuscated format on the user's screen.

3 Comments

Join the conversation! Your thoughts help the community grow.

Daniel Aravinth6y ago
How to store the key data as secure instead of using in the class file?
Sarang Kanfade8y ago
Is this work same in MYSQL also and for a special character?
- Nitesh Jha8y ago
  I have not tried for MYSQL. If there is a way to support .net assembly in MYSQL then It should work.