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

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

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group. View all posts by Arunraj