SQL Server Encryption Implementation

What is Data Sensitivity?

Data protection and security is one of the most important aspects of a database design nowadays. Virtually no database today can run without having some kind of sensitive information which has to be protected.This sensitive information can be personal information like PAN card details, SSN numbers, bank account numbers, passwords, credit card data etc. Protecting this data is very important from an organization point of view. There are many data privacy laws that will, in fact, force the organization to follow some compliance guidelines to make sure that this data is protected and secured correctly. For example, Microsoft has SQL Server Encryption feature to implement Data Security to protect sensitive data.

What is Data encryption?

Data Encryption is one of these mechanisms to ensure that sensitive data is protected. Data encryption essentially masks the real data and stores it in an unintelligible format.

SQL Server Encryption:

SQL Server provides an elaborate structure of public and private keys that in total provide us the infrastructure to encrypt and decrypt the sensitive data.

To make the below-listed content more understandable, first, we need to look at the hierarchy of the encryption keys and certificate provided in SQL Server Encryption feature. We will be using these keys to encrypt and decrypt sensitive data.

Following image describes the encryption keys infrastructure provided by SQL Server.

As it can be seen in the above image, there are 3 distinct layers of encryption keys implemented for Database encryption by SQL Server Encryption.

Windows DP API:

The topmost level is the Windows DPAPI. This is the encryption layer at the server level. This is basically used to encrypt the Service Master Key which is the highest level key inside the SQL Server Data Encryption.

Service Master Key:

Service Master Key (SMK) is an encryption key at the SQL Server level. This key is automatically created whenever the SQL Server Service starts. We don’t need to do anything to configure this. This key is encrypted using Windows AP API.

Database Master Key:

Database Master Key (DMK) is an encryption key at the database level. Each database in the SQL Server instance will have its own database master key to implement Database Encryption. DMK is encrypted by either a password or using Service Master Key (SMK).

Unlike Service Master Key which is created automatically when the SQL Server Service starts, database master key (DMK) is not created automatically for Database encryption. We need to create this manually. This key is used to encrypt all the other lower level keys like symmetric keys, certificates and asymmetric keys.

Symmetric Keys, Certificates and Asymmetric Keys:

SMK and DMK are created to encrypt and protect other keys and certificates. But the real data is not encrypted using the DMK or SMK directly.

According to Microsoft, you can use DMK to encrypt data directly but it is not recommended. So for actually encrypting the real data, we need to use symmetric keys, asymmetric keys or certificates.

• Symmetric Keys use a single password for both encrypting and decrypting the data.
• Asymmetric keys use two separate passwords for encryption and decryption.
• While you can use certificates for encryption of data, generally you should use it for encrypting the symmetric key rather than using it directly to encrypt or decrypt the data.

Now that we have got some basic background in the infrastructure needed for implementing the Database Encryption, we will create these keys and actually encrypt and decrypt data using these keys.

Now we are going to use following 4 objects to encrypt and decrypt the data in SQL Server Encryption.

• Service Master Key
• Database Master Key
• Certificate
• Symmetric Key

Service Master Key is automatically created when SQL Server Service starts. SMK is stored in master database. We can confirm this by using the following query. Note that I am using master.sys.symmetric_keys.

This will get the symmetric keys from the master database which is where SMK is stored.

select * from master.sys.symmetric_keys

You will see something like the image shown below.

Now, we are sure that we have the first key (SMK) in place which is required to implement SQL Server encryption.

Now, we go one level below in encryption hierarchy and check if we have the database master key for Database Encryption. This will be inside the database in which we need to implement encryption.

For this demonstration, I am using a database named test. You will need to do this inside the database and create a database master key(DMK.) Note that SMK was already created when SQL Server Service started. But DMK will have to be created by DBA.

Before creating the DMK, we can check if there is already a DMK created by someone else. Note that I am using database test before running the query. This query checks if there is any symmetric key in the database with regard to Database Encryption.

use test
go
select * from sys.symmetric_keys

Since I don’t have any database master key, it will give me 0 records as shown below.

Now that we know that there is no DMK, we will create one.

Create master key encryption 
by password = 'Dqwe234234`234#$%!@#%!@#%R234234'

This will create DMK in my database and we can confirm it by using the same query as shown below.

select * from sys.symmetric_keys

This time it will show me a database master key in the result as shown below.

Now, we have got first 2 levels of hierarchy for encryption ready i.e. (SMK and DMK)

Now, we will create a certificate and finally a symmetric key that will be used to encrypt and decrypt the data.

The next command will create the certificate for Database Encryption. This is a self-signed certificate and doesn’t need any certificate authority for certificate creation.

Create certificate PANCertificate 
with subject = 'Certificate used to encrypt PAN card data'

We can confirm that certificate was created successfully by running the following command.

select * from sys.certificates

As can be seen in the screenshot, this certificate is encrypted by using the Database Master Key.

And now finally we need to create a symmetric key that will be actually used to encrypt the data in the column containing sensitive information. The certificate will be used to encrypt this symmetric key.

Create symmetric key PANsmkey
with algorithm = AES_256
encryption by certificate PANCertificate

Now, we will be able to see 2 symmetric keys in the symmetric keys table. One is the database master key (DMK) and the second is the symmetric key that we just created in the previous step.

select * from sys.symmetric_keys

Well, that’s great. Now we have our infrastructure ready for encryption of sensitive data. But how do we actually use this infrastructure for encrypting and decrypting the data?

First, we will create a new table. Obviously, in the Production atabase, this may be an existing table.

Create table employees(empid int , PAN varchar(10) , 
PANEncrypted varbinary(128))

Note that my table’s third column is the one that will store encrypted PAN card and it is binary.

This table is going to store PAN cards of employees. It has 3 columns. One is employee ID, second is the actual PAN number and the third one is encrypted PAN number. In the real-time scenario, you won’t store the sensitive data in its true form in Production server. You will only store the encrypted data. But I am storing original PAN too for this demonstration purpose.

Now, we have the table to store encrypted data. Now we will insert the data in this table and encrypt it while inserting it.

This few lines of code will do it.

open symmetric key PANsmkey decryption by certificate PANCertificate
insert into employees 
values (1 , 'XDESD3445F' , 
ENCRYPTBYKEY(KEY_GUID('PANsmkey') ,'XDESD3445F'))
select empid , PAN , PANEncrypted , 
convert(varchar,DECRYPTBYKEY(PANEncrypted) ) 
from employees
close symmetric key PANsmkey

The first line is opening the symmetric key which we have created. This is the lowest level of encryption hierarchy. We use this symmetric key for actually encrypting the data.

open symmetric key PANsmkey decryption by certificate PANCertificate

The second line is inserting the data into the table. Note that my table’s third column is the one that will store encrypted PAN card and it is binary. The part highlighted in yellow is the code that is actually encrypting the PAN card. In my case, I am encrypting a PAN card value of XDESD3445F. In reality, this would be PAN card number of your employees or customers or anything that you want to encrypt.

insert into employees 
values (1 , 'XDESD3445F' , 
ENCRYPTBYKEY(KEY_GUID('PANsmkey') ,'XDESD3445F'))

When we run these 2 commands, we will get one row in the table. We can confirm it by running it following code. As you can see in the screenshot below, our PAN card data has been encrypted and converted to binary in the third column.

So, now we have encrypted the sensitive data while inserting it.

How do we decrypt this data so that it can be shown in the application in a normal format? The query below will decrypt the data while selecting it. Just like we used encrypt function while inserting data, we will need to use decrypt function while selecting the data. This is highlighted in the yellow in the query below.

select empid , PAN , PANEncrypted , 
convert(varchar,DECRYPTBYKEY(PANEncrypted) ) 
[Decrypted PAN] from employees

As can be seen in the screenshot below, we have original PAN card, encrypted PAN card shown in binary and the decrypted PAN card which is matching the original PAN card.

Finally, we need to close the symmetric which we had opened in the first line of code.

close symmetric key PANsmkey

So, to summarize, in this article, we have implemented the following tasks using SQL Server Encryption.

• Created requisite infrastructure needed to implement encryption of sensitive data
• Used the infrastructure to actually encrypt and decrypt the data

Benefits of SQL Server Encryption:

• No external software/systems needed to encrypt sensitive data
• Granular control over the data that is encrypted.
• Robust and well-documented security infrastructure and encryption hierarchy
• Ease of implementation. You can start the implementation with just a few lines of code
• Multiple options to choose keys, certificates, encryption algorithms etc. based on the compliance requirement

Kick Start your learning on Udemy today!

AWS Certified Solutions Architect – Associate 2017
AWS Certified Developer – Associate