Tag: SQL Server Encryption

In the previous episodes, we saw How to create Asymmetric key and How to use Asymmetric key to encrypt a Symmetric key, in this Episode of Encryption in SQL Server 2012, We will see How to encrypt data using Asymmetric Key functions

To encrypt data using Asymmetric Key, we need to use EncryptByAsymKey function and to decrypt data using Asymmetric Key, we need to use DecryptByAsymKey function

Snippet demonstrating Encrypting/Decrypting of Data using Asymmetric Key created using Password

USE AdventureWorks2012

--Encrypting the data 

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

SET @encryptedtext = EncryptByAsymKey
                    (
                        AsymKey_ID(N'AsymKey1_AdventureWorks2012'), 
                        @plaintext
                    )

--Decrypting the data
SELECT 
    @plaintext AS [PlainText], 
    @encryptedtext AS [EncryptedText],
    CAST
    (
        DecryptByAsymKey
        (
            AsymKey_ID(N'AsymKey1_AdventureWorks2012'), 
            @encryptedtext,
            N'UseAReallyStrongPasswordHere' –Specify Asym Key Password here
        ) 
        AS nvarchar(100)
    ) As [DecryptedText]

 

c

Snippet demonstrating Encrypting/Decrypting of Data using Asymmetric Key created using Strong Name Key Pair file

USE AdventureWorks2012

--Encrypting the data 

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

SET @encryptedtext = EncryptByAsymKey
                    (
                        AsymKey_ID(N'AsymKey2_AdventureWorks2012'), 
                        @plaintext
                    )

--Decrypting the data
SELECT 
    @plaintext AS [PlainText], 
    @encryptedtext AS [EncryptedText],
    CAST
    (
        DecryptByAsymKey
        (
            AsymKey_ID(N'AsymKey2_AdventureWorks2012'), 
            @encryptedtext
        ) 
        AS nvarchar(100)
    ) As [DecryptedText]

 

The results are as follows:

Hope you find this helpful !!!

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…

In this episode of Encryption in SQL Server 2012, We will see how to create Asymmetric Keys in databases using Password and Strong Name Key file

We can create Asymmetric Keys using the following options

1. Creating using Password

USE AdventureWorks2012

--Creating Asymmetric Key using Password
CREATE ASYMMETRIC KEY AsymKey1_AdventureWorks2012
    WITH ALGORITHM = RSA_2048 
        /* 
            Other Algorithms Supported as follows

            RSA_512
            RSA_1024
            AES_256
        */
    ENCRYPTION BY PASSWORD = 'UseAReallyStrongPasswordHere'

 

2. Creating using Strong Name Key Pair file

To generate a Strong Name Key Pair, we have to use sn.exe (For more information, Please refer http://msdn.microsoft.com/en-us/library/k5b5tt23(v=vs.110).aspx)

Command for Generating a Strong Name Key Pair file

sn –k C:\Workspace\AsymKey_AdventureWorks2012.snk

Once the key file is generated successfully, we can use that to create a Asymmetric key

--Creating Asymmetric Key using Strong Name Key file
CREATE ASYMMETRIC KEY AsymKey2_AdventureWorks2012
FROM FILE = N'C:\Workspace\AsymKey_AdventureWorks2012.snk';

 

The third option to generate an Asymmetric Key is to use EKM Device, for more information, Please refer http://technet.microsoft.com/en-us/library/bb895340.aspx

 

To View the list of Asymmetric Key available in the database, you can use the below query

 

SELECT 
    asymmetric_key_id, name, 
    pvt_key_encryption_type, pvt_key_encryption_type_desc, 
    algorithm, algorithm_desc, key_length
FROM sys.asymmetric_keys

 

In the next episode, we will see how we can use Asymmetric Keys

1. To Encrypt a Symmetric Key

2. To Encrypt and Decrypt Data

Hope this helps !!!

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

In the previous article, we saw how to create X 509 Certificate and register it in SQL Server,

Now we will see how we can use the registered certificates

SQL Server 2012 provides the following two functions to encrypt and decrypt data directly using X 509 Certificates

1. EncryptByCert

2. DecryptByCert

Please refer to the below sample on how we can use these functions

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

-- Get the Certificate ID
SET @CertID = Cert_ID(N'Cert1_AdventureWorks2012')

-- Encrypt the data with the Certificate Registered
SET @encryptedtext = EncryptByCert(@CertID, @plaintext) 

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

 

The result of the above are as follows

Please make sure to use the correct Certificate ID to decrypt the data and also make sure to document which certificate is used for Encryption, if you are planning to use multiple certificates for different tables.

Hope this helps !!!

In the upcoming articles, we will see How we can use Certificates to

1. Encrypt Symmetric Keys

2. To Enable TDE based Encryption for Database

3. To digitally sign data using SignByCert() function