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

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))

Decrypt

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

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

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