Encrypt and Decrypt in SQL Server: Part 4

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

Introduction

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

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

Create Asymmetric Key

              

CREATE ASYMMETRIC KEY AsymKey     
WITH ALGORITHM = RSA_1024
go

It is mandatory to create a 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.