Mehedi's Earth

Welcome once again! Today we will explore one of the security aspects of SQL Server. How we can accomplish column level encryption. Let's jump to the topic.

Why Column Level Security

Assume you are storing a crucial data in a column. Let's say address of every students and you don't want to disclose it to others. Using column level security, we can easily accomplish it. So that only the allowed DB users will view the value.

Common Concepts

• In SQL Server, data are encrypted hierarchically with key management infrastructure
• Upper layer encrypts the below layer using combination of certificates, asymmetric and symmetric keys
• Keys (Asymmetric/symmetric) can be stored outside of SQL Server in EKM (Extensible Key Management) module
• In terms of performance, symmetric keys is better than asymmetric keys
• The SMK (Service Master Key) protects the master key. This is created at SQL Server setup time. Master key is encrypted using the Windows Data Protection API (DPAPI)
• The Service Master Key and all Database Master Keys are symmetric keys

Figure 1: SQL Server Encryption hierarchy and concepts (courtesy Microsoft.com)

How to implement Column Level Security

Column level security can be implemented through below steps:

1. Create/Choose a DB and Table
2. Verify/Create Service Master Key
3. Create a SQL Server database master key
4. Create a self-signed SQL Server certificate
5. Configure a symmetric key
6. Encrypt the column data   
7. Query and verify the encryption

Figure 2: Column Level Encryption Steps

1. Create/Choose a DB and Table

We will use a test database EncryptIt for this demo and a Students table. Let's create and populate data in the table.

use [EncryptIt]

IF OBJECT_ID('Students', 'U') IS NOT NULL
    DROP TABLE Students;
GO
-- Create the demo table
CREATE TABLE dbo.Students
(
  [pk_id] int NOT NULL PRIMARY KEY CLUSTERED
  , [sid] varchar(20) NOT NULL
  , [name] varchar(50) NOT NULL
  , [class] varchar(100) NOT NULL
  , [address] nvarchar(1024) NOT NULL
  , [encrypted_address] varbinary(MAX) NULL
)
GO
-- Populate dummy data in the demo table
INSERT INTO Students ([pk_id],[sid],[name],[class],[address])
VALUES (1, 'S_001', 'Bob', 'Class-1', 'Dhaka, Bangladesh')
, (2, 'S_002', 'Smith', 'Class-1', 'Dhaka, Bangladesh')
, (3, 'S_003', 'John', 'Class-1', 'Dhaka, Bangladesh');

GO

SELECT * FROM Students;

Figure 3: Dummy data

2. Verify SQL Server Service Master Key

As shown in above figure - 1, the SMK (Service Master Key) is the root of SQL Server encryption hierarchy. It is created during the SQL Server setup time. Below query will show the SMK.

USE master
GO
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##'

Figure 4: Service Master Key

3. Creating SQL Server Database Master Key

A DMK (Database Master Key) is a symmetric key which is used to protect the private keys of certificates and asymmetric keys that are stored in a database. Please preserve the DMK password in a safe place for future use.

USE [EncryptIt];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test@123';
GO

-- Verify the key

SELECT * FROM sys.symmetric_keys;

Figure 5: Database Master Key

4. Creating a self-signed SQL Server certificate

Now create a self signed certificate using the DMK.

USE [EncryptIt];
GO
CREATE CERTIFICATE CertEncryptIt_test WITH SUBJECT = 'ENCRYPT_MY_DATA';
GO
-- Verify the new certificate
SELECT * FROM sys.certificates;

Figure 6: Self signed certificate

5. Creating a Symmetric Key

In this step, we will create a symmetric key to encrypt and decrypt data using the same certificate created in step-4. It is faster to use symmetric key to encrypt and decrypt data for routine use.

CREATE SYMMETRIC KEY SymKeyEncryptIt_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CertEncryptIt_test;
GO
-- Verify the Symmetric key
SELECT * FROM sys.symmetric_keys WHERE [name] = 'SymKeyEncryptIt_test';
GO

Figure 7: Symmetric key

6. Encrypt the Column Data

Now, we will encrypt the data using the symmetric key. We need to keep in mind that:

• Encrypted data must be stored in VARBINARY(max) datatype. We already added a column encrypted_address in our Students table at creation time with VARBINARY(max) datatype.
• In query window, we will open the symmetric key and decrypt using the the same symmetric key and certificate name which was created earlier. In our case it is SymKeyEncryptIt_test and CertEncryptIt_test.
• Run the UPDATE statement using the symmetric key.
• Close the symmetric key using the CLOSE SYMMETRIC KEY statement. Otherwise, it will remain open until the session is closed.

-- Open the symmetric key
OPEN SYMMETRIC KEY SymKeyEncryptIt_test DECRYPTION BY CERTIFICATE CertEncryptIt_test;
GO
UPDATE Students SET
encrypted_address = EncryptByKey (Key_GUID('SymKeyEncryptIt_test'),address)

GO
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKeyEncryptIt_test;
GO

SELECT * FROM Students;
GO

Figure 8: Encrypted data

7. Query and Verify the Encrypted data

Now we will decrypt and verify the data using below steps:

• In query window, we will open the symmetric key and decrypt using the the same symmetric key and certificate name which was created earlier
• Run the select statement with decryption using the symmetric key
• Close the symmetric key using the CLOSE SYMMETRIC KEY statement

-- Open the symmetric key
OPEN SYMMETRIC KEY SymKeyEncryptIt_test DECRYPTION BY CERTIFICATE CertEncryptIt_test;
GO
SELECT pk_id, address, CONVERT(varchar , DecryptByKey([encrypted_address])) AS encrypted_address FROM Students;
GO
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKeyEncryptIt_test;
GO

Figure 9: Decrypted data

Allow to Access Encrypted Column

If we try to decrypt data using another DB user, it will not throw any error. It will just show NULL value in encrypted_address column. We need to grant permission the DB user to decrypt and view the data using below command:

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymKeyEncryptIt_test TO [MyDBUser];
GO
GRANT VIEW DEFINITION ON CERTIFICATE::CertEncryptIt_test TO [MyDBUser];
GO

Conclusion

In this article, we checked how to encrypt any column and decrypt it. We need to remember that encryption and decryption is resource intensive hence has performance issue. Before using it, make sure you really need it!