Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Encryption Symmetric vs. Asymmetric Keys


By:   |   Last Updated: 2009-11-23   |   Comments (4)   |   Related Tips: More > Encryption

Problem

I need to encrypt my data within SQL Server and I plan on using the built-in encryption functionality in SQL Server 2005 and 2008. However, I'm looking at symmetric and asymmetric key algorithms and while I see information saying to use symmetric keys, I don't understand why. What's the difference between the two and why is a symmetric key algorithm preferred over the asymmetric key ones?

Solution

Both asymmetric and symmetric key algorithms are encryption algorithms. Both include mathematical operations to take a known piece of information, like a person's social security number or national ID, and render it effectively unusable, unless you have the secret to change that data back to its original form. We call any secrets "keys" and one of the main differences between the two types of algorithms are the number of secrets, or keys, involved.

Symmetric

In a symmetric key algorithm, there is but one key. That same key is used to encrypt the data and unencrypt, or decrypt, the data. If someone were to get possession of the key, that person could take anything you've encrypted, and decrypt it immediately.

Figure 1 shows a visual example of encrypting data using a symmetric key algorithm. Figure 2 shows an example of decrypting the data. Note that there is only one key in both cases.

Figure 1:

symmetric algorithm

Figure 2:

encrypted information

Asymmetric

In an asymmetric encryption algorithm, usually called a public-private key algorithm, there are two keys. One can be made public. So if anyone wanted to send you something and encrypt it, they would just need your public key. The other key should be kept safe, and is called the private key. The way an asymmetric encryption algorithm works is that if someone encrypts data using your public key, only your private key can be used to decrypt the data. So as long as you keep the private key safe, no one can decrypt the data even if they have your public key.

Figures 3 and 4 show this idea:

Figure 3:

public key

Figure 4:

private key

With respect to SQL Server, it can do the key handling for us. So if we let it, SQL Server's built-in encryption functionality keeps track of all these details and for practical purposes, there is not any difference between symmetric and asymmetric keys. You can encrypt data and you can decrypt data and both seem to work fine.

Asymmetric or Symmetric?

So why then is there a recommendation to use symmetric keys to encrypt data? Quite simply, performance. Symmetric key algorithms tend to be mathematically simpler, and as a result, faster.  The difference in speed can be significant even into the 100x faster range. Therefore, symmetric key algorithms are the way to go when encrypting data.

We can see this difference quite clearly with a simple example. We can set up two tables and create two keys. One set will be for an asymmetric key algorithm. The other will be for a symmetric key algorithm. And we can run through a number of rows of data and determine how much time it takes between the two algorithms. What we should see is that the symmetric key encryption is performed a noticeable amount faster.

First, let's do the setup:

USE MSSQLTips; 
GO 

/* Setup for Testing */    
CREATE SYMMETRIC KEY TestSymmKey 
WITH ALGORITHM = AES_256 
ENCRYPTION BY PASSWORD = 'TestP4ssw0rd!'; 
GO 

CREATE ASYMMETRIC KEY TestAsymmKey 
WITH ALGORITHM = RSA_512 
ENCRYPTION BY PASSWORD = 'TestP4ssw0rd!'; 
GO 

CREATE TABLE dbo.SymmKeyTest ( 
  EncryptedCol VARBINARY(256) 
); 
GO 

CREATE TABLE dbo.AsymmKeyTest ( 
  EncryptedCol VARBINARY(256) 
); 
GO

Next, the symmetric key test:

/* Symmetric Key Test */ 
DECLARE @StartTime DATETIME; 
DECLARE @EndTime DATETIME; 
DECLARE @KeyGUID UNIQUEIDENTIFIER; 

SET @KeyGUID = KEY_GUID('TestSymmKey'); 
SET @StartTime = GETDATE(); 

OPEN SYMMETRIC KEY TestSymmKey DECRYPTION BY PASSWORD = 'TestP4ssw0rd!'; 

INSERT INTO dbo.SymmKeyTest (EncryptedCol) 
SELECT TOP 5000 ENCRYPTBYKEY(@KeyGUID, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') 
FROM master.sys.columns c1 CROSS JOIN master.sys.columns c2; 

SELECT TOP 5000 CONVERT(VARCHAR(52), DECRYPTBYKEY(EncryptedCol)) 
FROM dbo.SymmKeyTest; 

SET @EndTime = GETDATE(); 

PRINT 'Symmetric Key Time Difference (ms): ' + CONVERT(CHAR, DATEDIFF(ms, @StartTime, @EndTime)); 
GO

Then, the asymmetric key test:

/* Asymmetric Key Test */ 
DECLARE @StartTime DATETIME; 
DECLARE @EndTime DATETIME; 
DECLARE @AsymID INT; 

SET @AsymID = ASYMKEY_ID('TestAsymmKey'); 
SET @StartTime = GETDATE(); 

INSERT INTO dbo.AsymmKeyTest (EncryptedCol) 
SELECT TOP 5000 ENCRYPTBYASYMKEY(@AsymID, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') 
FROM master.sys.columns c1 CROSS JOIN master.sys.columns c2; 

SELECT TOP 5000 CONVERT(CHAR(52), DECRYPTBYASYMKEY(@AsymID, EncryptedCol, N'TestP4ssw0rd!')) 
FROM dbo.AsymmKeyTest; 

SET @EndTime = GETDATE(); 

PRINT 'Asymmetric Key Time Difference (ms): ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)); 
GO

If you've kept the time of each, even though we did a relatively simple example with a small number of rows (5,000), you should see a noticeable difference in times. This gets worse the more data we're encrypting and decrypting.

Figures 5 and 6 show the difference in times on one of my systems.

Figure 5: - Symmetric Test

symmetric key time difference

Figure 6: - Asymmetric Test

messages

In my testing, I saw times averaging around 550 milliseconds for the symmetric key pass. And I saw times around 7500 milliseconds for the asymmetric key pass. So even in this simple example of 5000 rows of 52 characters, we can already see a sizeable difference of about 7 full seconds. Your results will vary based on your equipment and existing load at the time. But you should see a comparable difference where the symmetric key pass is better than 10x faster than the asymmetric key one. And that's why symmetric keys are preferred to encrypt data over asymmetric keys. It's more about performance than anything else.

Next Steps


Last Updated: 2009-11-23


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 21, 2018 - 3:13:27 PM - rao Back To Top

 i would like to know what will be the impact if i create table partition on existing table and sql server always on is there, such as can i create a partition without impacting the always on, or when i create a partition with partition key as non cluster and keeping the culster key ie pk as it is.

The table consists of 1.5 billion records currently and its in prod, there is no way to get the copy and shift the servers. Data mirrioning and always on is in place.

i would like to know what are the pros and cons of partitioning, it will a great help.

 

thanks

 

 


Wednesday, February 21, 2018 - 3:03:28 PM - rao Back To Top

 

 how to create a asymmetric key encrypted by master key


Wednesday, December 03, 2014 - 9:06:58 AM - armand Back To Top

Very helpful thank you


Wednesday, December 02, 2009 - 8:13:07 AM - [email protected] Back To Top

I would like to comment on the following

 

"With respect to SQL Server, it can do the key handling for us. So if we let it, SQL Server's built-in encryption functionality keeps track of all these details and for practical purposes, there is not any difference between symmetric and asymmetric keys. You can encrypt data and you can decrypt data and both seem to work fine"

In Asymmetric key the user who does the encryption of data may not be able to do decryption. that's not the case when using a symmetric key where the same level of permission is required to encrypt and decrypt the data.

To use asymmetric key decryption the user should be either the owner of the key or CONTROL permission on the key 

 

 


Learn more about SQL Server tools