How much overhead does encryption add to a SQL Server query

By: Dallas Snider   |   Comments (4)   |   Related: > Encryption

Problem

From visual inspection alone, one can deduce that decrypting an encrypted column using the DecryptByKey function in a SELECT statement will take longer than selecting the column stored in plain text. If you have been in the information technology industry long enough, you have probably heard the expression "this adds overhead" when discussing any extra processing added on to what is considered normal processing. I have heard this expression also, which leads me to ask the question "How can we quantify this additional overhead?"

Solution

In this tip we will explore how much additional processing time is added when decrypting an encrypted column in a T-SQL query. To run this experiment, I created a table using the command below. One column will hold a fictitious 16 digit credit card number while the other column will hold the encrypted version of the credit card number.

CREATE TABLE dbo.tblCustomerData
(
   CustomerID  int identity(1,1) NOT NULL,
   CreditCardNumberPlainText varchar(16) NOT NULL,
   CreditCardNumberEncrypted varbinary(MAX) NULL
)

For the next few steps, I used Nitansh Agarwal's tip on SQL Server Column Level Encryption Using Symmetric Keys as a guide.

I inserted 10,000,000 records into the table by running the following T-SQL code.

declare @i int;
set @i=0;
begin transaction 
   while @i<10000000
   begin
      INSERT INTO dbo.tblCustomerData with (tablock) 
      VALUES (cast((1000000000000000+@i) as varchar(16)),NULL)
      set @i=@i+1
   end
commit
GO

Next, I created the master encryption key. Of course, my password should be more complicated than the one shown below.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcdefghijklmnopqrstuvwxyz0123456789';
GO

Then, I created a certificate named Certificate1.

CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Encrypt Credit Card Number';
GO

Next, I created a symmetric key named SymmetricKey1 using the AES_128 algorithm and the certificate created in the previous step.

USE Tips;
GO
CREATE SYMMETRIC KEY SymmetricKey1 
WITH ALGORITHM = AES_128 
ENCRYPTION BY CERTIFICATE Certificate1;
GO

After creating the symmetric key, I updated the CreditCardNumberEncrypted column with the encrypted credit card number using the EncryptByKey function as shown in the follow T-SQL query.

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE tblCustomerData
SET CreditCardNumberEncrypted=
    EncryptByKey(Key_GUID('SymmetricKey1'),CreditCardNumberPlainText)
FROM dbo.tblCustomerData;
GO
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Now, with our table loaded with plain text and encrypted credit card numbers, I created two SSIS packages to read from the table and write to a file. The only difference between the two packages is that one package selects the plain text column while the other package decrypts the encrypted column using the DecryptByKey function and then converts the result to a varchar(16). This results in the same amount of data to be written to the disk. For both packages, a variable will be used to pass in the number of records to select from the table.

The image below shows the query in the OLE DB Source Editor from the package that selects the plain text column. Notice how the query is stored as a string variable within the package.

The next image shows the query in the OLE DB Source Editor from the package that decrypts the encrypted column. Notice how the symmetric key must be opened before the SELECT statement and the use of the DecryptByKey function.

A SQL Server Agent Job was created to run the packages with each step alternating between the package that had to perform the decryption of the credit card number and the package that selected the plain text credit card number. Both packages were run three times each with a specified number of records beginning with 1 million and incrementing in steps of 1 million until the packages were run with 10 million records. The image below shows the first 10 steps of the job.

Results

The average processing times for each package for each different number of selected records were calculated by querying the dbo.sysjobhistory table in the msdb database. The results are shown in the table below.

The chart below visualizes the processing times. As shown in the chart, the processing times when calling the DecryptByKey function were more than doubled when averaged across all volumes of data.

Summary

As we have seen in this tip, the additional processing time when using the DecryptByKey function becomes quite noticeable when working with large volumes of data, such as performing an extract of all records from a table containing over 1 million records. However, the additional processing time encountered when using the DecryptByKey function on one column for a single record will be virtually unnoticeable.

Next Steps

• SQL Server Column Level Encryption Using Symmetric Keys
• Querying SQL Server Agent Job History Data

About the author

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips