Home > SQL Server 2012, SQL Server Encryption > Encryption in SQL Server 2012–How to Create Symmetric Key and use EncryptByKey/DecryptByKey Functions

Encryption in SQL Server 2012–How to Create Symmetric Key and use EncryptByKey/DecryptByKey Functions


In this Episode of Encryption in SQL Server 2012, We will see How to create  Symmetric Key and use Symmetric Key based functions

Before creating the Key, Please make sure to create the certificate used for encrypting the key, for more information, Please refer to the previous article

First Step is to create Symmetric Key

USE AdventureWorks2012

--Create Symmetric Key protected by Certificate

CREATE SYMMETRIC KEY SymKey1_AdventureWorks2012
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert1_AdventureWorks2012;

 

Once the key is created successfully, you can verify the list of Symmetric Keys available using the below query

--View available Symmetric Keys
SELECT symmetric_key_id, name, key_algorithm, algorithm_desc, 
    key_length, key_guid FROM Sys.symmetric_keys

 

image

While creating a Symmetric Key you can choose the following list of available Encryption Algorithms

1. DES

2. TRIPLE_DES

3. TRIPLE_DES_3KEY

4. RC2

5. RC4

6. RC4_128

7. DESX

8. AES_128

9. AES_192

10. AES_256

Similarly for encrypting the Key, in the above snippet we have use Certificate, instead we can use the following other options as well

1. Password

2. Asymmetric Key

3. Another Symmetric Key

 

After we successfully created the Symmetric key, we can use open and encrypt/decrypt the data as shown follows

 

To open the Symmetric Key

OPEN SYMMETRIC KEY SymKey1_AdventureWorks2012
DECRYPTION BY CERTIFICATE Cert1_AdventureWorks2012;
GO

 

Now call the EncryptByKey and DecryptByKey functions to encrypt and decrypt the data

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

-- Encrypt the data with the Certificate Registered
SET @encryptedtext = EncryptByKey(Key_Guid(N'SymKey1_AdventureWorks2012'), @plaintext)

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

 

The results are as follows

image

Authenticator

We can also use Authenticator to add more protection to the encrypted data

 

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

-- Encrypt the data with Authenticator
SET @encryptedtext = EncryptByKey
            (
                Key_Guid(N'SymKey1_AdventureWorks2012'), 
                @plaintext, 
                1, -- To Enable Authenticator Specify 1
                CONVERT(Varbinary, 'AuthenticatorText') -- Authenticator Text should be converted to VARBINARY
            );

-- Decrypt the data with Authenticator
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText], 
    CAST(
        DECRYPTBYKEY(
                @encryptedtext, 
                1, 
                CONVERT(Varbinary, 'AuthenticatorText')
        ) AS nvarchar(100)) As [DecryptedText]

 

Once we complete the encryption and decryption operations, Please make sure to close the key

 

CLOSE SYMMETRIC KEY SymKey1_AdventureWorks2012;
GO

Hope this helps !!!

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: