Archive

Archive for April 5, 2013

Encryption in SQL Server 2012–How to encrypt data using Asymmetric Key functions

April 5, 2013 Leave a comment

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

image

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:

image

Hope you find this helpful !!!