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

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:

image

 

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…

About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 111 other followers

%d bloggers like this: