Category: Blog

SQL Server has asymmetric keys available for encryption. I wouldn’t recommend them for direct encryption of data since they are slow, i.e. they require lots of resources, but they are good for encrypting the (faster) symmetric keys.

How can you create one? It’s actually easy, using the standard CREATE ASYMMETRIC KEY DDL.

create asymmetric key HRProtection
 with algorithm = RSA_1024
 ENCRYPTION BY PASSWORD = 'Use4SomeStr0ngP@ssword%^'
;
GO

This create a key with the name and using the RSA algorithm at 1024 bits and protects the key with a password. You could chose other key lengths (512, 1024, 2048), but 1024 is the minimum I would use. It has been shown that 512 bit keys can be cracked relatively cheaply and easily on AWS for less than US$100.

These keys can be created from key files (a key pair set of files if you used something like SN from the Windows SDK. This would require a path to the files and permissions to read them. Just like protecting the password with this file, you should have limited permissions set on any key pair files that exist on your OS. The ideal way to create these keys is with a hardware EKM device, but those are expensive, rarely used, and not foolproof.

This requires the CREATE ASYMMETRIC KEY permission, or equivalent. Most of the DDL type permissions include this permission.

There are no GRANT/DENY/REVOKE permission statements with keys. Make sure you use a strong password and protect it. Do not disclose this unnecessarily.

That’s the basic of the key creation. I’ll look at some other options with these keys in another post.