Post
Ask Question

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 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 UI. As you have seen 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 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.

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 that need to be protected like SSN, Account Number, DOB, and Credit Card Number, 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 involve creating a Database master key, a Certificate, and a 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 in the 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 procedures, functions, and Views. The detailed steps are mentioned in the below section.

Solution

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

Step 1. Create a Master key in your database. (Use the below query).

USE SampleDemo --Database Name
CREATE MASTER KEY ENCRYPTION BY PASSWORD='DEMOSU#12345&SAMI#SU'
GO

Step 2. Create the backup for your master key.

BACKUP MASTER KEY
TO FILE = 'G:\Key\MyKey'
ENCRYPTION BY PASSWORD = 'DEMOSU#12345&SAMI#SU'
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 the ‘G:\Key’ path with the MyKey file name.

Key

Step 3. Create a Certificate in the database.

CREATE CERTIFICATE SampleCertificate
WITH SUBJECT ='DEMO',
EXPIRY_DATE='20251031'
GO

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

Step 4. Create a symmetric key in the database.

CREATE SYMMETRIC KEY DemoKey
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE SampleCertificate
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.

CREATE TABLE [dbo].[Customer](
    [CustomerID] [bigint] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](50) NULL,
    [CustomerDOB] [varbinary](max) NULL,
    [CustomerSSN] [varbinary](max) NULL,
    [CustomerPhone] [varchar](12) NULL
)

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.

CREATE PROC usp_AddCustomer  
    @CustomerName VARCHAR(50),  
    @CustomerDOB VARCHAR(50),  
    @CustomerSSN VARCHAR(10),  
    @CustomerPhone VARCHAR(12)  
AS  
BEGIN  
    OPEN SYMMETRIC KEY DemoKey DECRYPTION  
    BY CERTIFICATE SampleCertificate  
    INSERT INTO Customer(CustomerName,CustomerDOB,CustomerSSN,CustomerPhone)  
    VALUES(@CustomerName,ENCRYPTBYKEY(KEY_GUID('DemoKey'),@CustomerDOB),  
    ENCRYPTBYKEY(KEY_GUID('DemoKey'),@CustomerSSN),@CustomerPhone)  
END

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

Step 7. To add the record, run the below query.

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

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

Customer table

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

Step 8. Create a View for your Customer table.

CREATE VIEW vw_Customer 
AS 
SELECT 
    CustomerID, 
    CustomerName, 
    CONVERT(VARCHAR(50), DECRYPTByKeyAutoCert(cert_id('SampleCertificate'), NULL, CustomerDOB)) AS CustomerDOB, 
    CONVERT(VARCHAR(50), DECRYPTByKeyAutoCert(cert_id('SampleCertificate'), NULL, CustomerSSN)) AS CustomerSSN, 
    CustomerPhone 
FROM Customer

While creating the View, we used the SQL function to decrypt the data and only an authorized person can see it. DecryptByKeyAutoCer() function is used to decrypt the data with the help of a 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 the newly created View.

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. Create a stored procedure to get the Customer details.

CREATE PROC usp_GetCustomerByID    
@ID BIGINT  
AS  
BEGIN  
        SELECT CustomerID   
           ,CustomerName   
           ,CustomerDOB   
           ,CustomerSSN   
           ,CustomerPhone  
          FROM vw_Customer   
        WHERE CustomerID=@ID  
END

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

Customer ID

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 a function to mask the DOB.

CREATE FUNCTION [dbo].[ufn_DOB](@DOB VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
    RETURN 'XX-XX-' + CAST(YEAR(CAST(@DOB AS DATE)) AS VARCHAR(4))
END

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

Step 11. Create a function to mask the SSN.

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

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

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

ALTER PROC usp_GetCustomerByID
@ID BIGINT
AS
BEGIN
    SELECT CustomerID
          ,CustomerName
          ,dbo.ufn_DOB(CustomerDOB) AS CustomerDOB
          ,dbo.ufn_SSN(CustomerSSN) AS CustomerSSN
          ,CustomerPhone
    FROM vw_Customer
    WHERE CustomerID=@ID
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 a function for DOB and SSN.

Now, run this SP

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.

Conclusion

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

SQL


Similar Articles