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

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

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: