
Latest from MSSQLTips
10 Skills Development Strategies for Retaining Knowledge
Capture SQL Server Schema Changes Using the Default Trace
Including NULL Values in JSON Output in SQL Server 2016
SQL Server 2016 Query Store SSMS Panes
Create a Simple SQL Server Trigger to Build an Audit Trail
OnDemand Webcast  Key Insights for Meeting your SLAs for SQL Server in VMware Environments
By: K. Brian Kelley  Read Comments (4)  Related Tips: More > Security 
I'm trying to use SQL Server's builtin encryption and I see there are three different options available for an asymmetric key, corresponding to key length. What's the impact of the key length?
Since we're talking asymmetric encryption in SQL Server, we have available the RSA public key encryption algorithm. The way this algorithm works is dependent on two very large prime numbers. These numbers are used as part of a formula to generate the public key, and those prime numbers are kept secret. If you take the product of those prime numbers (a x b) and determine its length, that length expressed in bits is the size of the key. Therefore, the larger the key length, the larger the numbers involved. The larger the numbers involved, the harder it is to factor back to the original two prime numbers and break the key.
SQL Server supports three particular key lengths with respect to the RSA algorithm:
So the question arises, "Which key length do I use?"
If your data is sensitive at all (why else would you be encrypting it?), you want to avoid this option. 512 bit length numbers can be factored with reasonable resources. It has been around since 1999. Also, in the last couple of years we've seen attacks where certificates with 512 bit length keys may have been factored. While not an option within SQL Server, 768 bit length numbers can be factored, too. That's why 768bit RSA is considered broken. To go a step further, with respect to the operating system, Microsoft has put out an update which blocks certificates in which the key has been encrypted by anything less than 1024 bits with the RSA public key encryption algorithm. Therefore, steer clear of the 512bit key length option.
NIST originally predicted that a 1024 bit key length would be good until about 2010. 2010 has passed and we've not seen any means of factoring a 1024 bit length number made public. There was a theoretical hardware proposal from 2003 that suggested that a 1024 bit key length number could be factored, however there hasn't been any announced research which indicates that 1024 bit keys are broken.
So should you use 1024 bit keys? Given that we're past 2010 and that it's entirely possible that a nation state actor (think NSA or a foreign nation's equivalent) may have the resources to crack a 1024 bit key, we should probably consider this a short term option at best. Some cryptography experts believe that 1024 bit key lengths will be broken soon (the next few years), albeit it will probably require some major computing resources. Therefore, 1024 should be okay for the near future.
Given that SQL Server supports a 2048 key length, this is probably the best option to choose. NIST estimates that it'll be 2030 before 2048 bit key lengths will be in danger. Also, if you're encrypting a large amount of data, the recommendation is to encrypt using a symmetric key algorithm and to protect that algorithm's key using an asymmetric encryption algorithm. That's why in SQL Server there are the following functions:
If you're wondering about that last function, when it comes to encryption in SQL Server, there is functionally no difference between asymmetric keys and certificates. One option certificates give you is the ability to have key lengths ranging from 384 to 4096 bits (except SQL Server 2005, which is limited to a maximum key length of 3,456 bits). Therefore, if you want to go beyond 2048, certificates (albeit an imported one) are the only option currently with respect to SQL Server (through SQL Server 2012).


Monday, August 12, 2013  10:40:38 AM  K. Brian Kelley  Read The Tip 
True, you may not put a strain on the SQL Server and the OS, however, even with very simple data sets and small number of rows, so you can clearly see the difference in performance. If you want to recreate that simple test for yourself, I detail it here:
http://www.mssqltips.com/sqlservertip/1886/sqlserverencryptionsymmetricvsasymmetrickeys/

Monday, August 12, 2013  10:23:30 AM  Chris Hoffmann  Read The Tip 
Agreed, good article. One thing to note, however, is the resource overheard associated with asymmetric key decryption; this of course increases with the length of the key as well. Native SQL Server asymmetric key decryption can use a great deal of CPU and memory, depending upon the number of rows being decrypted. Depending upon the other activities taking place on the server, and whether or not the database is OLTP, an asymmetric key may not be a great option. EKM (http://technet.microsoft.com/enus/library/bb895340.aspx) may provide some relief, albeit at higher cost. 
Wednesday, July 24, 2013  4:55:30 PM  K. Brian Kelley  Read The Tip 
Factor is the same term as in mathematics. So if I wanted to factor 12, I'd look at the combinations that multiply together to get 12:
 1 and 12  2 and 6  3 and 4
With small numbers like this, determining the factors is easy. However, when you talk about very, very, very large numbers, it is not humanly possible. In order to be able to crack the RSA algorithm, you'd want to be able to take that huge number and factor it into the two prime numbers. 
Wednesday, July 24, 2013  8:49:14 AM  JustCurious  Read The Tip 
You have a great article, but I have some questions. What does factor mean? How do you do that? 