Encrypt and Decrypt in SQL Server: Part 4

Introduction

This is Part 4 of the article series. If you have not read the previous articles in this series then please go through the following articles.

1. Encrypt & Decrypt in SQL Server Part-1
2. Encrypt & Decrypt in SQL Server Part-2
3. Encrypt & Decrypt in SQL Server Part-3

In this article, we will encrypt plain text and decrypt encrypted text using an asymmetric key in SQL Server.

Asymmetric key in SQL 

An asymmetric key is a combination of a public key and a private key. A public key is used to encrypt the data and a private key to decrypt the data.

How to Create Asymmetric Key in SQL Server?

CREATE ASYMMETRIC KEY AsymKey       
WITH ALGORITHM = RSA_1024  
go 

It is mandatory to create or open a database key to execute the statement above successfully otherwise you will get the following error message:

Msg 15581, Level 16, State 6, Line 1

Please create a master key in the database or open the master key in the session before performing this operation.

Encrypt

DECLARE @EncryptedText VARBINARY(128)  
SET @EncryptedText=ENCRYPTBYASYMKEY(ASYMKEY_ID(N'AsymKey'),@Text)

=

Decrypt

DECLARE @DecryptedText VARCHAR(MAX)  
SET @DecryptedText=DECRYPTBYASYMKEY (ASYMKEY_ID(N'AsymKey'),@TextEnrypt) 

SELECT @Text AS 'TextToEncrypt',@TextEnrypt AS 'EncryptedText',@TextDecrypt AS 'DecryptedText'  
GO

Drop Asymmetric Key

DROP ASYMMETRIC KEY AsymKey  
GO  

Conclusion

In the next article, we will generate a symmetric key and encrypt and decrypt the string using the same key.

Next >> Encrypt and Decrypt in SQL Server: Part 5