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
    WITH ALGORITHM = RSA_2048 
        /* 
            Other Algorithms Supported as follows

            RSA_512
            RSA_1024
            AES_256
        */
    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

 

SELECT 
    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 !!!