Encrypt and Decrypt in SQL Server: Part 5

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

Introduction

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

Create Symmetric Key

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



Open Symmetric Key

Once we create the symmetric key, we need to 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.