How To Secure PII Data In SQL Server Using CLE (Column Level Encryption)

PII (Personally Identifiable Information) is used to identify individual identity such as SSN, DOB, Bank Account Number etc. When you are working with a financial application, you face the 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 to UI. As you have seen 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 techniques can be used.

  1. .NET Assembly
  2. CLE (Column-level encryption)
  3. TDE (Transparent Data Encryption)

We have already implemented the PII with .NET assembly in the previous article. So now, let’s talk about the CLE.


Column level encryption is also known as Cell Level Encryption. It is the inbuilt feature of SQL Server introduced in SQL Server 2005. And, it is much faster compared to the .NET assembly encryption.

As we have a few columns which need to be protected like SSN, Account Number, DOB, Credit Card Number, so it does not make sense to encrypt the complete database. To implement CLE in your table, identify the column and make sure the column data type is VARBINARY. The process will have involved creating Database master key, Certificate, and symmetric key. When you save the PII data, it will be stored in the encrypted format, and you can create the View to show the PII data into original format because you can provide the specific role/ access to view the PII data.

Here, there is a scenario where the customer data needs to be stored in the encrypted format and the data also needs to be shown in obfuscated/ masked on some UI screens and Reports. To achieve this functionality, I have created a few SQL objects, such as stored procedure, functions, and View. The detailed steps are mentioned in the below section.


Perform the following steps to secure the Customer PII data using CLE.

Step 1

Create Master key in your database. (Use below query).

  1. USE SampleDemo --Database Name  
  4. GO  

Step 2

Create the backup for your master key. 

  2. TO FILE ='G:\Key\MyKey'  
  4. GO  

Creating a backup of the master key will help us to move the database from one server to another server. When you move the database from one server to another server, you may not be able to retrieve the encrypted data, so in that case, either you have to restore the master key through the backup which you have created or you have to use the query to open the master key. In the above query, I have created the backup in ‘G:\Key’ path with MyKey file name.


Step 3

Create Certificate in the database.

  1. CREATE CERTIFICATE SampleCertificate  
  3. EXPIRY_DATE='20251031'  
  4. GO  

‘SampleCertificate’ is created with expiry date 31-10-2025.

Step 4

Create a symmetric key in the database.

  3. BY CERTIFICATE SampleCertificate  
  4.     GO  

Once the Symmetric key is created, we can use this key to store the PII data in encrypted format.

Step 5

Create the table where the PII data needs to be stored.

  1. CREATE TABLE [dbo].[Customer](  
  2.         [CustomerID] [bigintPRIMARY KEY IDENTITY(1,1) NOT NULL,  
  3.         [CustomerName] [varchar](50) NULL,  
  4.         [CustomerDOB] [varbinary](maxNULL,  
  5.         [CustomerSSN] [varbinary](maxNULL,  
  6.         [CustomerPhone] [varchar](12) NULL  
  7.         )  

The important thing is here. We need to make sure that the data type of the column is VARBINARY if the PII data are required to be stored in encrypted format. In the above query, you can see that the DOB and SSN column data types are VARBINARY.

Step 6

After creating the Customer table, create the stored procedure to insert the record.

  1. CREATE PROC usp_AddCustomer  
  2.         @CustomerName VARCHAR(50),  
  3.         @CustomerDOB VARCHAR(50),  
  4.         @CustomerSSN VARCHAR(10),  
  5.         @CustomerPhone VARCHAR(12)  
  6. AS  
  7. BEGIN  
  9.         BY CERTIFICATE SampleCertificate  
  10.            INSERT INTO Customer(CustomerName,CustomerDOB,CustomerSSN,CustomerPhone)   
  11.            VALUES(@CustomerName,ENCRYPTBYKEY(KEY_GUID('DemoKey'),@CustomerDOB),  
  12.        ENCRYPTBYKEY(KEY_GUID('DemoKey'),@CustomerSSN),@CustomerPhone)  
  13. END  

The usp_AddCustomer SP is used to insert the record into Customer table. In this SP, you can observe, we are using symmetric key and certificate to encrypt the PII data. The EncryptedByKey() SQL function is used to encrypt the data. In customer table, only two columns, DOB and SSN, are the PII data.

Step 7

To add the record, run the below query.

  1. [dbo].[usp_AddCustomer] 'Sam','1990-01-01','888899995','9876543212'  

You can see the below record inserted into the Customer table.


Note: When we work with PII data, then somewhere in the database, we need to store the actual data and provide the limited access to that. To storing the actual data, usually, we create the SQL Views.

Step 8

Create View for your Customer table.

  1. CREATE VIEW vw_Customer  
  2. AS  
  3. SELECT  
  4. CustomerID,  
  5. CustomerName,  
  6. CONVERT(VARCHAR(50),DECRYPTByKeyAutoCert(cert_id('SampleCertificate'),NULL,CustomerDOB)) AS CustomerDOB,  
  7. CONVERT(VARCHAR(50),DECRYPTByKeyAutoCert(cert_id('SampleCertificate'),NULL,CustomerSSN)) AS CustomerSSN,  
  8. CustomerPhone  
  9. FROM Customer  

While creating the View, we have used the SQL function to decrypt the data and only authorized person can see it. DecryptByKeyAutoCer() function is used to decrypt the data with the help of certificate. The important thing here is to provide the authorization for your SQL Views so that the PII data can be restricted.

When we select the View, we can see the actual data below. We are fetching the data from newly created View.


Once the View is created, we can create the stored procedure to get customer details and send that back to UI.

Step 9

Crea a stored procedure to get the Customer details.

  1. CREATE PROC usp_GetCustomerByID   
  2. @ID BIGINT  
  3. AS  
  4. BEGIN  
  5.         SELECT CustomerID  
  6.            ,CustomerName  
  7.            ,CustomerDOB  
  8.            ,CustomerSSN  
  9.            ,CustomerPhone  
  10.          FROM vw_Customer   
  11.         WHERE CustomerID=@ID  
  12. END  

This SP fetches the data based on customer ID. Let’s run this SP.


Now, our stored procedure is ready to fetch the record based on Customer ID but just observe the above screen. We are sending the actual PII data to the UI screen or reports. Actually, the data should be obfuscated/masked before sending to the UI. To achieve this functionality, we need to create the SQL function to obfuscate/ mask the DOB and SSN.

Step 10

Create function to mask the DOB.

  1. CREATE FUNCTION [dbo].[ufn_DOB](@DOB VARCHAR(50))  
  3. AS  
  4. BEGIN  
  6. END  

This function takes the DOB as input and returns the masked data.

Step 11

Create function to mask the SSN.

  1. CREATE Function [dbo].[ufn_SSN](@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  

This function take SSN number as input and returns the masked SSN.

Step 12

Now, change your stored procedure which is used to fetch the customer data.

  1. ALTER PROC usp_GetCustomerByID   
  2. @ID BIGINT  
  3. AS  
  4. BEGIN  
  5.         SELECT CustomerID  
  6.            ,CustomerName  
  7.            ,dbo.ufn_DOB(CustomerDOB) AS CustomerDOB  
  8.            ,dbo.ufn_SSN(CustomerSSN) AS CustomerSSN  
  9.            ,CustomerPhone  
  10.         FROM vw_Customer   
  11.         WHERE CustomerID=@ID  
  12. END  

Here, we are modifying the usp_getCustomerById SP to fetch the customer data with masking. You can see that in the observer in the above query, we have applied function for DOB and SSN.

Now, run this SP.


The final obfuscated/masked data can be sent to the UI screen or reports.

 In this complete scenario, there are two stored procedures used to insert and get the record.

  • usp_AddCustomer
  • usp_GetCustomerById

When we are working with C#, we can use these stored procedures to insert the PII data and retrieve the PII data.


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


Up Next
    Ebook Download
    View all
    View all