Encrypt and Decrypt in SQL Server: Part 5

Introduction

This is Part 5 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
  4. Encrypt & Decrypt in SQL Server Part-4

In this article, we will create a symmetric key and encrypt and decrypt a string using this key.

How to Create Symmetric Key in SQL Server?

CREATE SYMMETRIC KEY TestSymKey                 
WITH ALGORITHM =AES_256  
ENCRYPTION BY CERTIFICATE TestCert  
GO

Create-Symmetric-Key.jpg

Open Symmetric Key in SQL Server

Once we create the symmetric key, we must open it before use.

OPEN SYMMETRIC KEY TestSymKey   
DECRYPTION BY CERTIFICATE TestCert    
WITH PASSWORD  = '@k$h@yPatel'    
GO  

Encrypt

DECLARE @Text VARCHAR(MAX)  
SET @Text = 'I am Akshay Patel'  
   
DECLARE @EncryptedText VARBINARY(128)  
SET @EncryptedText = (SELECT ENCRYPTBYKEY(KEY_GUID(N'TestSymKey'),@Text))

Encrypt.jpg

Decrypt

DECLARE @DecryptedText VARCHAR(MAX)  
  
SET @DecryptedText = (SELECT CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@EncryptedText)))   

Decrypt.jpg

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

Decrypt1.jpg

Drop Asymmetric Key

DROP SYMMETRIC KEY TestSymKey  
GO  

Conclusion

In this five-article series, we have seen Service Master Key, Database Master Key, and Encrypt & Decrypt using Certificate, Asymmetric Key, and Symmetric Key in SQL Server

Find the whole series here.

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


Similar Articles