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 the 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 to the UI. As you have seen before,  your account number, credit card number, mobile number, and SSN, these all are obfuscated (ex-Mobile SSN- XXXXX2398).

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

  1. .NET Assembly
  2. CLE (Column-level Encryption)
  3. 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 which 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 flat file, app.config etc.

I have hard-coded the key in the library file itself but you can use 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 scenario will be explained in the next article.

Step 1

Create you library project.

SQL server
Step 2

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

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.IO;  
  4. using System.Linq;  
  5. using System.Security.Cryptography;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8.   
  9. namespace EncryptDecrypt.Library  
  10. {  
  11.     /// <summary>  
  12.     ///   
  13.     /// </summary>  
  14.     public sealed class AES_EncryptDecrypt  
  15.     {  
  16.         Static readonly string keyData = "su@@@###su&BLSKF";  
  17.  
  18.         #region Encrypt  
  19.   
  20.         /// <summary>  
  21.         ///   
  22.         /// </summary>  
  23.         /// <param name="inputData"></param>  
  24.         /// <param name="pass"></param>  
  25.         /// <returns></returns>  
  26.         public static string Encrypt(string inputData)  
  27.         {  
  28.             try  
  29.             {  
  30.                 return Convert.ToBase64String(EncryptStringToBytes(inputData, Encoding.Default.GetBytes(keyData)));  
  31.             }  
  32.             catch (Exception ex)  
  33.             {  
  34.                 return "";  
  35.             }  
  36.   
  37.         }  
  38.   
  39.         /// <summary>  
  40.         ///   
  41.         /// </summary>  
  42.         /// <param name="plainText"></param>  
  43.         /// <param name="key"></param>  
  44.         /// <returns></returns>  
  45.         public static byte[] EncryptStringToBytes(string plainText, byte[] key)  
  46.         {  
  47.             return EncryptStringToBytes(plainText, key, null);  
  48.         }  
  49.   
  50.         /// <summary>  
  51.         ///   
  52.         /// </summary>  
  53.         /// <param name="plainText"></param>  
  54.         /// <param name="key"></param>  
  55.         /// <param name="IV"></param>  
  56.         /// <returns></returns>  
  57.         public static byte[] EncryptStringToBytes(string plainText, byte[] key, byte[] IV)  
  58.         {  
  59.             if ((plainText == null) || (plainText.Length <= 0))  
  60.             {  
  61.                 throw (new ArgumentNullException("PlainText"));  
  62.             }  
  63.             if ((key == null) || (key.Length <= 0))  
  64.             {  
  65.                 throw (new ArgumentNullException("PlainText"));  
  66.             }  
  67.             RijndaelManaged rijManaged = new RijndaelManaged();  
  68.             rijManaged.Key = key;  
  69.             if (!(IV == null))  
  70.             {  
  71.                 if (IV.Length > 0)  
  72.                 {  
  73.                     rijManaged.IV = IV;  
  74.                 }  
  75.                 else  
  76.                 {  
  77.                     rijManaged.Mode = CipherMode.ECB;  
  78.                 }  
  79.             }  
  80.             else  
  81.             {  
  82.                 rijManaged.Mode = CipherMode.ECB;  
  83.             }  
  84.   
  85.             byte[] encryptedData = null;  
  86.             ICryptoTransform iCryptoTransform = rijManaged.CreateEncryptor();  
  87.             encryptedData = iCryptoTransform.TransformFinalBlock(Encoding.Default.GetBytes(plainText), 0, plainText.Length);  
  88.   
  89.             return encryptedData;  
  90.   
  91.         }  
  92.  
  93.         #endregion  
  94.  
  95.         #region Decrypt  
  96.   
  97.         /// <summary>  
  98.         ///   
  99.         /// </summary>  
  100.         /// <param name="input"></param>  
  101.         /// <param name="pass"></param>  
  102.         /// <returns></returns>  
  103.         public static string Decrypt(string inputData)  
  104.         {  
  105.             try  
  106.             {  
  107.                 return DecryptStringFromBytes(Convert.FromBase64String(inputData), Encoding.Default.GetBytes(keyData));  
  108.             }  
  109.             catch (Exception ex)  
  110.             {  
  111.                 return "";  
  112.             }  
  113.   
  114.         }  
  115.   
  116.         /// <summary>  
  117.         ///   
  118.         /// </summary>  
  119.         /// <param name="cipherText"></param>  
  120.         /// <param name="key"></param>  
  121.         /// <returns></returns>  
  122.         public static string DecryptStringFromBytes(byte[] cipherText, byte[] key)  
  123.         {  
  124.             return DecryptStringFromBytes(cipherText, key, null);  
  125.         }  
  126.   
  127.         /// <summary>  
  128.         ///   
  129.         /// </summary>  
  130.         /// <param name="cipherText"></param>  
  131.         /// <param name="key"></param>  
  132.         /// <param name="IV"></param>  
  133.         /// <returns></returns>  
  134.         public static string DecryptStringFromBytes(byte[] cipherText, byte[] key, byte[] IV)  
  135.         {  
  136.             if ((cipherText == null) || (cipherText.Length <= 0))  
  137.             {  
  138.                 throw (new ArgumentNullException("cipherText"));  
  139.             }  
  140.             if ((key == null) || (key.Length <= 0))  
  141.             {  
  142.                 throw (new ArgumentNullException("key"));  
  143.             }  
  144.             RijndaelManaged rijManaged = new RijndaelManaged();  
  145.             rijManaged.Key = key;  
  146.             rijManaged.Mode = CipherMode.CBC;  
  147.             if (!(IV == null))  
  148.             {  
  149.                 if (IV.Length > 0)  
  150.                 {  
  151.                     rijManaged.IV = IV;  
  152.                 }  
  153.                 else  
  154.                 {  
  155.                     rijManaged.Mode = CipherMode.ECB;  
  156.                 }  
  157.             }  
  158.             else  
  159.             {  
  160.                 rijManaged.Mode = CipherMode.ECB;  
  161.             }  
  162.   
  163.             string PlainData = null;  
  164.             ICryptoTransform iCryptoTransform = rijManaged.CreateDecryptor(rijManaged.Key,rijManaged.IV);  
  165.             MemoryStream msDecrypt = new MemoryStream(cipherText);  
  166.             CryptoStream csDecrypt = new CryptoStream(msDecrypt, iCryptoTransform, CryptoStreamMode.Read);  
  167.             StreamReader srDecrypt = new StreamReader(csDecrypt);  
  168.             PlainData = srDecrypt.ReadToEnd();  
  169.   
  170.             return PlainData;  
  171.   
  172.         }  
  173.  
  174.         #endregion  
  175.     }  
  176. }  

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.

  1. using EncryptDecrypt.Library;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Text;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace EncryptDecrypt.ConsoleUI  
  9. {  
  10.     class Program  
  11.     {  
  12.         static void Main(string[] args)  
  13.         {  
  14.               
  15.             Console.WriteLine("Enter the string to be encrypted");  
  16.             string value = Console.ReadLine();  
  17.             string encryptedData = AES_EncryptDecrypt.Encrypt(value);  
  18.   
  19.             Console.WriteLine("Encrypted Data:- " +encryptedData);  
  20.   
  21.             string decryptedData = AES_EncryptDecrypt.Decrypt(encryptedData);  
  22.   
  23.             Console.WriteLine("Decrypted Data:- " + decryptedData);  
  24.         }  
  25.     }  
  26. }  

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

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

SQL server

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

SQL server

Step 6

Enable CLR in your database. 

  1. use SampleDemo  
  2. GO  
  3. SP_Configure 'clr enable', 1  
  4. Go  
  5. Reconfigure  
  6. GO  

Step 7

Create the Encrypt and Decrypt function in your Database. 

  1. USE SampleDemo  
  2. GO  
  3. CREATE FUNCTION Encrypt(@string NVARCHAR(100))  
  4. RETURNS NVARCHAR(100) WITH EXECUTE AS CALLER  
  5. AS  
  6. EXTERNAL NAME [EncryptDecryptLibrary].[EncryptDecrypt.Library.AES_EncryptDecrypt].[Encrypt]  
  7. GO  
  8.   
  9. USE SampleDemo  
  10. GO  
  11. CREATE FUNCTION Decrypt(@string NVARCHAR(100))  
  12. RETURNS NVARCHAR(100) WITH EXECUTE AS CALLER  
  13. AS  
  14. EXTERNAL NAME [EncryptDecryptLibrary].[EncryptDecrypt.Library.AES_EncryptDecrypt].[Decrypt]  
  15. 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 Customer table with four columns - ID, Name, AccountNumber, and SSN. ID is the identity column and is set to primary key.

Step 8

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

SQL server

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.

  1. CREATE Function SSN_Masking(@input nvarchar(100))  
  2. returns nvarchar(100)  
  3. AS   
  4. BEGIN  
  5.     DECLARE @data nvarchar(100)  
  6.     SELECT @data= 'XXXXX'+SUBSTRING(@input,6,4)  
  7.     return @data  
  8. END  

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

Step 10

Create the stored procedure to get the masked and decrypted data based on the id. 

  1. CREATE proc usp_GetCustomerById   
  2. (  
  3.    @ID bigint  
  4. )  
  5. AS  
  6. BEGIN  
  7.     Select ID,Name,dbo.Decrypt(accountnumber) AS AccountNumber,dbo.SSN_Masking(dbo.Decrypt(SSN)) AS SSN from customer where ID=@ID  
  8. END   

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

 SQL server

Conclusion

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