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

 

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

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