Understanding the importance of key length with the SQL Server asymmetric encryption algorithms

By:   |   Comments (4)   |   Related: > Security


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?


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley 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

Comments For This Article

Monday, August 12, 2013 - 10:40:38 AM - K. Brian Kelley Back To Top (26251)

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:




Monday, August 12, 2013 - 10:23:30 AM - Chris Hoffmann Back To Top (26250)

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 Back To Top (25986)

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 Back To Top (25978)

You have a great article, but I have some questions. 

What does factor mean?

How do you do that?

get free sql tips
agree to terms