Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Understanding the SQL Server Symmetric Encryption Algorithms

MSSQLTips author K. Brian Kelley By:   |   Read Comments (3)   |   Related Tips: More > Security
Problem

I'm trying to use SQL Server's built-in encryption and I see there is an assortment of algorithms available. What is the difference between each one?

Solution

Since we're talking symmetric encryption, these algorithms either take unencrypted data and return encrypted data, or they take encrypted data and return unencrypted data. In order to do so, they use a key, and the key is the same regardless of which direction you go. When it comes to encryption algorithms, symmetric encryption algorithms are substantially faster than asymmetric algorithms. So they represent the best choice with which to encrypt data in SQL Server.

SQL Server 2005 provides us with the following symmetric encryption algorithms (how you specify them with CREATE SYMMETRIC KEY is in parentheses):

  • DES (DES)
  • Triple DES with 128 bit key (TRIPLE_DES)
  • Triple DES with 192 bit key (DESX)
  • RC2 (RC2)
  • RC4 (RC4)
  • RC4 with 128 bit key (RC4_128)
  • AES with 128 bit key (AES_128)
  • AES with 192 bit key (AES_192)
  • AES with 256 bit key (AES_256)

I mentioned Triple DES with 192 bit key but said it was DESX. Is this a mistake? It is not. Updated versions of SQL Server Books Online clarify that SQL Server does not support the DESX encryption algorithm. TRIPLE_DES should probably have been TRIPLE_DES_128 and DESX should probably have been TRIPLE_DES_192 to stay consistent with the naming convention Microsoft chose.

Note: Microsoft has indicated that since DESX is in there incorrectly, that its use should be phased out. It will be removed in a later version of SQL Server.

SQL Server 2008 introduced:

  • Triple DES with 192 bit key (TRIPLE_DES_3KEY)

Obviously, since DESX shouldn't be used, if you want Triple DES with a 192 bit key, use TRIPLE_DES_3KEY.

SQL Server 2008R2 and SQL Server 2012 did not introduce any new symmetric encryption algorithms.

With these options, which should you choose? Unlike with hashing algorithms, which can be used for more than security, all of these are encryption algorithms. Therefore, your choice is key. You want an algorithms that's going to be secure for the foreseeable future. In that case:

Symmetric Encryption Algorithms You Should Probably Avoid

We'll specify two types of algorithms here. The first are the symmetric encryption algorithms considered broken either because computing power has caught up with them or there's a flaw that can be exploited. The second are algorithms which SQL Server implements in a weakened or incorrect way. As of the writing of this tip, the following algorithms fall into that list:

  • DES - a 56 bit key encryption algorithm that was the "go to" algorithms for many years. Now, the key can be broken too quickly (less than 1 day), making this an outdated encryption algorithm.
  • "DESX" - As per the earlier discussion, "DESX" isn't really DES-X, and Microsoft is phasing this out of SQL Server.
  • RC2 - a block cipher, RC2 isn't heavily used in relation to other symmetric algorithms. There's not been a lot of research done on it and there are a couple of known attacks against it. However, given that it isn't so commonly used, it's probably best to skip this one.
  • RC4 - a stream cipher that can be implemented with a variable length key from 40-128 bits. This option in SQL Server is less than 128 bits. SQL Server implements this algorithm in a weakened form (no salt). As a result, Microsoft has updated Books Online to indicate not to use this algorithm for future development work as it'll be phased out.
  • RC4 with 128 bit key - Just as with the RC4 protocol, SQL Server implements this in a weakened form. It also is being phased out.

Symmetric Encryption Algorithms Good for the Foreseeable Future

There are several options available here:

  • Triple DES with 128 bit key - Uses DES, but in a series of three steps. This uses keying option 2, meaning that the 128 bits is actually made up of 2 distinct keys.
  • Triple DES with 192 bit key - Same as Triple DES with 128 bit key, but with a 192 bit key. This uses keying option 1 meaning that the 192 bits are actually made up of 3 distinct keys. This particular version NIST feels will be okay until around 2030.
  • AES with 128 bit key - This the Advanced Encryption Standard algorithm, formerly known as Rijndael. It uses a 128 bit key. While there is an attack out there that's faster than brute force, it's still unfeasibly from a time perspective.
  • AES with 192 bit key
  • AES with 256 bit key

Both Triple DES and AES are considered secure for the foreseeable future. However, we have multiple options with both algorithms revolving around his large the key is. So which version of each algorithm do we choose? Generally speaking, when comparing how secure an algorithm is, the larger the key the better with respect to comparisons of the same algorithm (so AES 128 bit vs. AES 256 bit, not Triple DES with 192 bit vs. AES with 256 bit). However, this comes at a computational cost. If you want to make sure it's secure for longer, choose the large key.

Next Steps


Last Update: 7/19/2013


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
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, July 19, 2013 - 8:40:11 AM - Jeremy Kadlec Read The Tip

Brian,

Great tip as always!  Thank you for sharing your knowledge with the community.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, July 19, 2013 - 2:57:20 PM - Prasad Read The Tip

Good one!!


Tuesday, October 29, 2013 - 5:31:26 PM - NIST SP800-131A Read The Tip

Only Three-key Triple DES is considered secure for the forseeable future.  Two-key Triple DES is considered weak.

Anyone subject to NIST special publications, or who wants to meet or exceed NIST best practices should aso avoid Triple DES with 128 bit key.  Per NIST SP800-131A, http://csrc.nist.gov/publications/nistpubs/800-131A/sp800-131A.pdf      Two-key Triple DES Decryption is listed as Legacy Use after 2010, and Two-key Triple DES Encryption is listed as Restricted Use until 2015, and Disallowed after.  As the article says, Three-key Triple DES and all three AES variants are listed as Acceptable (which is the best rating used in SP800-131A) with no end date known at the time SP800-131A was written.

 

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.