Asymmetric Key (Encryption – Decryption in SQL Server 2008 Part 5)

Asymmetric Key is a small brother of Certificate which I have explained in my previous article. Asymmetric Key is a combination of public key and private key which uses some different algorithms to encrypt and decrypt key. Algorithms are RSA_512, RSA_1024, RSA_2048. Details of these algorithms are beyond the scope of this article, I will write detailed article on these kind of algorithm very soon.

Now let us look small T-SQL practice for this.

--create database master key before creating Asymmetric key

--if it is not already exist

CREATE MASTER KEY ENCRYPTION BY PASSWORD='$qlhub'

--creaet asymmetric key with

--RSA_512 algorithm

CREATE ASYMMETRIC KEY AsymADV

WITH ALGORITHM = RSA_512

go

--let us encrypt and decrypt data with Asymmetric key

DECLARE @Text nvarchar(max)

DECLARE @TextEnrypt varbinary(128)

DECLARE @TextDecrypt nvarchar(max)

SET @Text=N'hi'--, this is first Assymetric test, created by Ritesh Shah'

SET @TextEnrypt=ENCRYPTBYASYMKEY(AsymKey_ID(N'AsymADV'),@Text)

SET @TextDecrypt=DECRYPTBYASYMKEY (AsymKey_ID(N'AsymADV'),@TextEnrypt)

SELECT @Text AS 'ORIGINAL TEXT',@TextEnrypt AS 'Encrypted Text',@TextDecrypt as 'Decrypted Text'

GO

--drop Asymmetric key

DROP ASYMMETRIC KEY AsymADV

GO

--drop master key

drop master key

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com