How to Store PII Data in a Database | A Complete Guide

The best practices to store PII data in a database include identifying which data classifies as PII data and which doesn’t. Once identified, the steps are to create a solid data architecture and encrypt the PII data.

Generally, there are three methods to encrypt PII data: .NET Assembly, Column Level Encryption (CLE), and Transparent Data Encryption (TDE). Don’t worry if all that sounds a bit intimidating. Let’s break things down to understand the steps involved in PII data storage.

How Should PII Be Stored in a Database?

The following practices provide an overview of how to handle PII data in a database.

Determine PII and Non-PII Data

Before storing PII data in a database, it is necessary to distinguish between PII and non-PII data. But first, you need to identify the type of data you have at hand.

You can identify PII data by asking yourself, “Can I use this data to trace or distinguish an individual from others?”. If the answer is yes, it is most likely PII data. Examples of such information include name, address, age, credit card details, social security numbers, and medical records.

Build a Data Architecture

Creating a data architecture is a way of organizing your data in the database. Usually, it involves making a plan that states specific responsibilities and data destinations. In turn, this plan helps prevent data from ending up in a mistaken location while boosting its quality.  

Encrypt PII Data

Encrypting PII data before storing it in the database ensures no harm to the end user, even if a data breach occurs. Some modern encryption algorithms are the RSA, Advanced Encryption Standard (AES), and Twofish method. However, you can opt for the older DES, as some encryption is better than no encryption.

Allow Necessary Permissions Only

Make sure people with access to the database can only retrieve information relevant to their work. That way, you can prevent accidental modification of PII data and thereby reduce the risk. Also, in case of an attack, the intruder will get access to only a portion of the total data.

How to Encrypt PII Data in SQL Server

One of the best ways to encrypt PII data in your SQL server is to use a column-level encryption (CLE). It is a built-in feature of the SQL server itself, therefore being both secure and fast. 

Here’s the CLE encryption process in detail.

1. Create a master key using the following query.

USE SampleDemo –Database Name  

CREATE MASTER KEY ENCRYPTION   
BY PASSWORD='DEMOPASS123#'

2. Back up the master key you just created using:

BACKUP MASTER KEY
TO FILE = 'G:\Key\MyKey'
WITH ENCRYPTION BY PASSWORD = 'DEMOPASS123';

3. Generate a certificate within the database.

CREATE CERTIFICATE SampleCertificate
WITH SUBJECT = 'NEW_SUBJECT',
EXPIRY_DATE = '2030-12-31';

4. Create a symmetric key to store the Personally Identifiable Information (PII) data in encrypted form.

CREATE SYMMETRIC KEY Key
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE Certificate;

5. Construct the table to accommodate the storage of PII data.

CREATE TABLE [dbo].[Tablename](
    [ID] [bigint] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [DOB] [varbinary](max) NULL,
    [SSN] [varbinary](max) NULL,
    [Phone] [varchar](12) NULL
);

6. Create a stored procedure that inserts a record into the table.

CREATE PROC AddCustomer  
        @Name VARCHAR(50),  
        @DOB VARCHAR(50),  
        @SSN VARCHAR(10),  
        @Phone VARCHAR(12)  
AS  
BEGIN  
        OPEN SYMMETRIC KEY Key DECRYPTION   
        BY CERTIFICATE Certificate  
           INSERT INTO Tablename(Name, DOB, SSN, Phone)   
           VALUES(@Name,ENCRYPTBYKEY(KEY_GUID('Key'),@DOB),  
       ENCRYPTBYKEY(KEY_GUID('Key'),@SSN),@Phone)  
END

7. Add the record.

[dbo].[AddCustomer] 'Xyz','1992-09-09','123456789','1234567890'  

8. For the table, create a view.

CREATE VIEW vw_Data  
AS  
SELECT  
ID,  
Name,  
CONVERT(VARCHAR(50), DECRYPTByKeyAutoCert(cert_id('Certificate'), NULL, DOB)) AS DOB,  
CONVERT(VARCHAR(50), DECRYPTByKeyAutoCert(cert_id('Certificate'), NULL, SSN)) AS SSN,  
Phone  
FROM [TableName];

9. Create a stored procedure to retrieve the details of a customer.

CREATE PROC usp_GetDataByID
@ID BIGINT
AS
BEGIN
    SELECT ID
        ,Name
        ,DOB
        ,SSN
        ,Phone
    FROM vw_Data
    WHERE ID = @ID
END

10. Build a method for masking the Date of Birth and the Social Security Number.

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  
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

11. Modify the stored procedure that retrieves the data for users.

ALTER PROC usp_GetDataByID
@ID BIGINT
AS
BEGIN
    SELECT ID
        ,Name
        ,dbo.ufn_DOB(DOB) AS DOB
        ,dbo.ufn_SSN(SSN) AS SSN
        ,Phone
    FROM vw_Data
    WHERE ID = @ID
END

FAQs (Frequently Asked Questions and Answers)

How to identify PII data in the database?

PII includes distinguishable information linked to a person, be it identity, social security number, address, date of birth, financial information, or biometrics. Besides, email, IP address, and MAC address are also PII data.

Should PII be encrypted in the database?

Although not all, some sensitive PII should go through some encryption process. Such information includes credit card numbers and social security numbers. Encrypting can ensure a safe transit and protect individuals in case of a data breach.

What is the best encryption for PII?

The best encryption algorithm for PII or confidential information is AES, the highest encryption standard according to the National Institute of Standards and Technology. It uses variable key lengths ranging from 128 bits to 256 bits. Another capable encryption process for PII is RSA.

Conclusion

The CLE encryption is at the heart of securely storing PII data in a database. Remember that, for PII data, we only need to encrypt specific columns, and doing so for the whole database is unnecessary. Generally, the process requires the data type of the columns to be VARBINARY.