Encryption in SQL Server 2012–How to encrypt a Symmetric Key using Asymmetric Keys

In the last episode, we saw How to create Asymmetric Keys, and in this episode of Encryption in SQL Server 2012, We will see how to encrypt a Symmetric Key using an Asymmetric Key

First Step is to create the Symmetric Key based using Asymmetric Key

USE AdventureWorks2012

-- Creating a Symmetric Key using Asymmetric Key
CREATE SYMMETRIC KEY SymKey2_AdventureWorks2012
WITH ALGORITHM = AES_256 
    /* Other Supported Algorithms 
        
        DES
        TRIPLE_DES
        TRIPLE_DES_3KEY
        RC2
        RC4
        RC4_128
        DESX
        AES_128
        AES_192
        
    */
ENCRYPTION BY ASYMMETRIC KEY AsymKey1_AdventureWorks2012;

Next Open the Symmetric Key using Asymmetric Key to Encrypt/Decrypt the data

--Opening Symmetric using Asymmetric Key

OPEN SYMMETRIC KEY SymKey2_AdventureWorks2012
DECRYPTION BY ASYMMETRIC KEY AsymKey1_AdventureWorks2012
WITH PASSWORD = 'UseAReallyStrongPasswordHere';
GO

Once the Symmetric Key is successfully opened, You can Encrypt or Decrypt the data using EncryptByKey / DecryptByKey functions

--Encrypting the data 

DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption'

SET @encryptedtext = EncryptByKey(Key_Guid(N'SymKey2_AdventureWorks2012'), @plaintext)

--Decrypting the data
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText],
    CAST(DECRYPTBYKEY(@encryptedtext) AS nvarchar(100)) [DecryptedText]

The output of the above query is as follows:

Once the Encryption / Decryption process is complete, Please make sure to close the Symmetric Key

--Close the Symmetric Key
CLOSE SYMMETRIC KEY SymKey2_AdventureWorks2012;
GO

Hope this helps !!!

In the next Episode of Encryption in SQL Server 2012, We will see how to encrypt data directly using Asymmetric Key and its functions…

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. View all posts by Arunraj