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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah