Archive

Archive for April 3, 2013

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

April 3, 2013 2 comments

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…

Advertisements
%d bloggers like this: