By: K. Brian Kelley | Comments (3) | Related: > 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
- Understand the different between symmetric and asymmetric encryption algorithms.
- Learn how to use certificates to encrypt sensitive data in SQL Server.
- Read up on authenticators and how they work within SQL Server.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
