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:
- Create/Choose a DB and Table
- Verify/Create Service Master Key
- Create a SQL Server database master key
- Create a self-signed SQL Server certificate
- Configure a symmetric key
- Encrypt the column data
- 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!