CREATE ASYMMETRIC KEY – How to Generate an Asymmetric Key

Introduction

Certificates and asymmetric keys are very similar in SQL Server. Both can be used to protect symmetric keys in the database. Whilst this not being a good practice, they can also both be used to encrypt data directly. One of the bigger differences is that the certificate can have additional information attached to it, like a signature that proves the identity of the certificate owner.

If you are just using the certificate to protect keys in a single database, you do not need all that additional information. In that case, you can use an asymmetric key instead.

The CREATE ASYMMETRIC KEY statement

To generate a new asymmetric key you can use the CREATE ASYMMETRIC KEY statement. In its simplest form, the statement looks like this:

The WITH ALGORITHM clause determines the algorithm that the key should be created for. SQL Server knows three algorithms for asymmetric key encryption:

All three algorithms are all based on the RSA cryptosystem. The difference between them is the key length: 512, 1024 or 2048 bits. The longer the key (the more bits it has) the more secure the encrypted data is. However, more bits also mean that the encryption process uses more CPU resources.

The algorithm has to be specified when generating an asymmetric key. This is another big difference to certificates and maybe the biggest reason to use asymmetric keys at all. Certificates that are generated in SQL Server always use 1024 bit as key length. Asymmetric keys allow you to generate a longer and hence more secure key. (Certificates that where generated outside of SQL Server with a longer key can however be imported.)

Finding your Asymmetric Key

To see if an asymmetric key exists you can use the sys.asymmetric_keys catalog view:

That query returns a result like this:

The last column tells us how the private key is protected.

Protecting the Private Key

Like certificates, asymmetric keys can be protected either with the database master key or with a password. The CREATE ASYMMETRIC KEY statement above does not specify a protection mechanism. That results in the key being protected by the database master key. If you instead want to protect the private key with a password, you can use the following syntax:

The specified password needs to comply with the windows password policies. The asymmetric key created by this statement is indeed password protected:

Summary

Asymmetric keys and certificates in SQL Server are very similar. However, asymmetric keys make it easier to use a longer and hence more secure key length. Asymmetric keys in SQL Server are created with the CREATE ASYMMETRIC KEY statement. The private key can be protected either with the database master key or with a password.