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 importance of key length with the SQL Server asymmetric encryption algorithms

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

I'm trying to use SQL Server's built-in 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?

Solution

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:

  • 512 (RSA_512)
  • 1024 (RSA_1024)
  • 2048 (RSA_2048)

So the question arises, "Which key length do I use?"

Avoid RSA with a 512 bit key length

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 768-bit 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 512-bit key length option.

Consider RSA with a 1024 bit key length okay... for now

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.

Make the move to RSA with a 2048 bit key length

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:

  • DecryptByKeyAutoAsymKey()
  • DecryptByKeyAutoCert()

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).

Next Steps


Last Update: 7/23/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
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



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/sql-server-encryption-symmetric-vs-asymmetric-keys/

 


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/en-us/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?




 
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.