View Encrypted SQL Data in Bold BI

Published:

In Bold BI you can view the encrypted SQL data by following the below steps. To handle the encryption and decryption of data, SQL Cryptographic functions with Symmetric Keys are used here.

Step 1: Table creation

Create a table in which the data should be encrypted. In this example, column named SSN is used to store the encrypted data.

CREATE TABLE [dbo]. [CustomerDetails](
[CUS ID] [nvarchar](10) NOT NULL,
[First Name] [nvarchar](300) NOT NULL,
[Last Name] [nvarchar](300) NULL,
[DOB] [date] NOT NULL,
[SSN] [varbinary](max) NULL
)

Step 2: Symmetric Key Generation

Now create a master key and certificate which is used to create symmetric key.
Master Key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword@123';

Certificate:
Here the password parameter is optional, which can be used to differentiate between user/customer

CREATE CERTIFICATE DBCert   
ENCRYPTION BY PASSWORD = 'MyPassword@123'
WITH SUBJECT = 'Encrypt data';  
GO

Symmetric Key:

CREATE SYMMETRIC KEY Cust_SymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE DBCert
GO

You can provide access to the certificate and symmetric key for other users using below steps,

Create User:

CREATE LOGIN [testuser] WITH PASSWORD= 'Test@123'
GO

CREATE USER testuser
FOR LOGIN testuser
WITH DEFAULT_SCHEMA = dbo

Grant Permission to Key and Certificate:

GRANT CONTROL ON CERTIFICATE :: DBCert TO testuser
Go

GRANT REFERENCES ON SYMMETRIC KEY::Cust_SymmetricKey TO testuser
Go

GRANT VIEW DEFINITION ON SYMMETRIC KEY::Cust_SymmetricKey TO testuser
Go

Step 3: Insert Data with Column Level Encryption

Create a stored procedure to insert the data into the table with encryption as below,
To encrypt data, you must open the symmetric key before inserting the data into table using OPEN SYMMETRIC KEY
Now, use the ENCRYPTBYKEY function with the name of the symmetric key and the value to be encrypted i.e. SSN.

Close the symmetric key using CLOSE SYMMETRIC KEY statement.

  CREATE PROCEDURE sp_insert_cusdetails
  
  @CustomerId VARCHAR (10),
  @FirstName NVARCHAR (300),
  @LastName NVARCHAR (300),
  @DOB DATE,
  @SSN NVARCHAR (20)
  AS
  BEGIN
  
  SET NOCOUNT ON;
  
  OPEN SYMMETRIC KEY Cust_SymmetricKey DECRYPTION BY CERTIFICATE DBCert WITH PASSWORD =
  'MyPassword@123'
  
  INSERT INTO CustomerDetails VALUES (@CustomerId, @FirstName, @LastName, @DOB, ENCRYPTBYKEY
  (KEY_GUID('Cust_SymmetricKey'), @SSN));
  
  CLOSE SYMMETRIC KEY Cust_SymmetricKey;
  END

Now execute the stored procedure with required input as below,

EXECUTE [dbo].[sp_insert_cusdetails]
@CustomerId = '1',
@FirstName = 'Tommy',
@LastName = 'Crabber',
@DOB = '9/22/2021',
@SSN = '10-0008631'
GO

Table Data:
You can see that column SSN is encrypted and stored here

Step 4: Decrypt Data

Create a stored procedure to decrypt and view the data.
Now, use the DECRYPTBYKEY function with the name of encrypted column i.e. SSN.

As mentioned in previous step, you must open the symmetric key before decrypting data and close the symmetric key after decryption.

  CREATE PROCEDURE sp_get_cusdetails
  @CustomerId NVARCHAR (10),
  AS
  BEGIN
  SET NOCOUNT ON;
  OPEN SYMMETRIC KEY Cust_SymmetricKey DECRYPTION BY CERTIFICATE DBCert WITH PASSWORD =
  'MyPassword@123'
  SELECT [EMP ID],
  [FIRST NAME],
  [LAST NAME],
  [DOB],
  CONVERT (NVARCHAR (MAX), DECRYPTBYKEY(SSN)) AS SSN
  FROM CustomerDetails WHERE [CUS ID]= @CustomerId
  CLOSE SYMMETRIC KEY Cust_SymmetricKey;
  END
  Go

Step 5: View Encrypted Data in Bold BI

To view the data in Bold BI Create a SQL Live data source as below.

Drag and drop the stored procedure created in the previous step and enter the customer id to be viewed.

Click the Update option in Data Preview, Now the data is decrypted and shown as below,