Encryption in SQL Server 2012–Creating Asymmetric Keys using Password and Strong Name Key file

In this episode of Encryption in SQL Server 2012, We will see how to create Asymmetric Keys in databases using Password and Strong Name Key file

We can create Asymmetric Keys using the following options

1. Creating using Password

USE AdventureWorks2012

--Creating Asymmetric Key using Password
CREATE ASYMMETRIC KEY AsymKey1_AdventureWorks2012
            Other Algorithms Supported as follows

    ENCRYPTION BY PASSWORD = 'UseAReallyStrongPasswordHere'


2. Creating using Strong Name Key Pair file

To generate a Strong Name Key Pair, we have to use sn.exe (For more information, Please refer http://msdn.microsoft.com/en-us/library/k5b5tt23(v=vs.110).aspx)

Command for Generating a Strong Name Key Pair file

sn –k C:\Workspace\AsymKey_AdventureWorks2012.snk


Once the key file is generated successfully, we can use that to create a Asymmetric key

--Creating Asymmetric Key using Strong Name Key file
CREATE ASYMMETRIC KEY AsymKey2_AdventureWorks2012
FROM FILE = N'C:\Workspace\AsymKey_AdventureWorks2012.snk';


The third option to generate an Asymmetric Key is to use EKM Device, for more information, Please refer http://technet.microsoft.com/en-us/library/bb895340.aspx


To View the list of Asymmetric Key available in the database, you can use the below query


    asymmetric_key_id, name, 
    pvt_key_encryption_type, pvt_key_encryption_type_desc, 
    algorithm, algorithm_desc, key_length
FROM sys.asymmetric_keys



In the next episode, we will see how we can use Asymmetric Keys

1. To Encrypt a Symmetric Key

2. To Encrypt and Decrypt Data

Hope this helps !!!

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

2 thoughts on “Encryption in SQL Server 2012–Creating Asymmetric Keys using Password and Strong Name Key file”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s