Join

MS SQL Server Cell/Column Level Encryption & Decryption

Introduction

Database is a wonderful tool to store information and to look at the data in different ways to make decisions, present an opinion etc. However, the same data can pose a big problem should it fall in the hands of unscrupulous people and the impact can be more devastating if it has sensitive information such as Credit Card/Bank Account numbers, personal Telephone/Email details. For some organizations, it is mandatory to meet stringent data security regulations such as PCI-DSS, HIPAA/HITECH, or GLBA/FFIEC. Hence all such sensitive information must be encrypted in order to protect the customers and avoid data loss in the event of data breach.

This article focuses on how to do data encryption in Microsoft SQL Server by looking at the available options.

MS SQL Server – Data Encryption – Available Options

  • Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is available from version 2008 and above, which doesn’t require any programming knowledge. Entire database or an individual column can be encrypted using TDE which internally uses AES and 3DES algorithms. Backups of TDE encrypted databases are also encrypted.

However, TDE is available in Enterprise Edition only.

  • Column/Cell Level Encryption

This is available to all the editions of MS SQL from version 2005 onwards. Unlike TDE, this requires schema modification and affects performance as well. AES with 128,196,256 bit keys and 3DES algorithms are used to perform data encryption with the help of a passphrase, asymmetric/symmetric key or a certificate.

  • Custom Libraries

Can be implemented whether you use Microsoft Programming languages or others such as PHP/Java.

We’ll go with option-b, as TDE requires Enterprise edition and using custom libraries is dependent on the programming language of choice.

Cell Level Encryption

Any of the following methods can be used to encrypt the data:

  • Passphrase/Password

Considered to be least secured as the same password is used for both encryption and decryption.

  • Certificate

Provides strong protection with good performance. Certificate used must be signed by database master key (DMK).

  • Symmetric key

Considered to be strong enough with good performance. Same key is used for both encryption and decryption.

  • Asymmetric key

Different keys are used to encrypt/decrypt data, hence provides strong protection. However, it impacts performance. As per Microsoft’s guidelines, this should not be used to encrypt large values.

This blog explains data encryption using Symmetric key by using a test database, script for the same is provided below.

Data in the tblUsers is human readable since no encryption is in place, yet.

sql_encrypt_01

Cell Level Encryption – Symmetric Key

  • Execute the below T-SQL statement to create database master key (DMK), encrypted with the passphrase: P@$$W0RD.

 

Execute the below T-SQL statement to see the details of DMK created above.

sql_encrypt_02

Execute the below T-SQL statement to create a Certificate which will be used to encrypt the Symmetric key.

Use this query to check the certificate created:

sql_encrypt_02

You may see an error similar to the below if DMK is not created.

Msg 15581, Level 16, State 1, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

Create the DMK as mentioned above and then issue the CREATE CERTIFICATE statement.

It is possible to use external/third party certificates, which is not in the scope of this blog.

  • Use the below T-SQL statement to create a symmetric key with the certificate created above.

Check the symmetric_keys table, which should show both DMK and the Symmetric Key created above.

sql_encrypt_03

We’re done with the preliminaries of Symmetric Key setup and can move on to data encryption. Slightly roundabout way has to be used to implement cell level encryption since we’re using existing database with existing table/data.

Execute the below script to add a new column: EncryptedEmail to the table tblUsers, which will store the encrypted value of the User’s Email Id.

Now, use the below script to encrypt the Email Id data and put it in the newly created column: EncryptedEmail.

Check the content of tblUsers, which should show the encrypted value of email.

sql_encrypt_05

Since the encrypted column for Email is in place, we can drop the old column: EmailId.

Now, we’ve a database that supports cell level encryption. We’ve also ensured all the existing data is not lost and properly encrypted.

Use the below script to fetch decrypted email id.

Cell-Level Encryption Advantages 

  1. Provides the means to encrypt single cell in a table.
  2. Data is not in plain text when it is loaded into memory, and is decrypted only when it is queried.

 Cell-Level Encryption Dis-advantages

  1. Requires changes to the schema since encrypted data is stored in VARBINARY format. This is not an issue for new databases though.
  2. Encryption and Decryption affects the overall performance of the database.
  3. Table scans could be expensive, as indexes are also encrypted.
  4. Opening of Symmetric Key will not work in User Defined Functions (UDF); hence the below script must be called prior to invoking the UDF.

Posted By: Sairam Dhanalakota, Osmosee

Leave A Reply