Encrypt and Decrypt in SQL Server

Its mandatory to create Master Key

Now we are creating 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.

USE prasi
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Pr@S!C00l'

CREATE ASYMMETRIC KEY AsymKey   
WITH ALGORITHM = RSA_1024
go
DECLARE @EncryptedText VARBINARY(128)
DECLARE @Text VARCHAR(128)
DECLARE @DecryptedText VARCHAR(MAX)
SET @Text='I am Super Human'
SET @EncryptedText=ENCRYPTBYASYMKEY(ASYMKEY_ID(N'AsymKey'),@Text)
SET @DecryptedText=DECRYPTBYASYMKEY (ASYMKEY_ID(N'AsymKey'),@EncryptedText)
SELECT @Text AS 'TextToEncrypt',@EncryptedText AS 'EncryptedText',@DecryptedText AS 'DecryptedText'

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

DECLARE @PWD varchar(30) = 'Pr@S!C00l'

CREATE CERTIFICATE TESTCERT
ENCRYPTION BY PASSWORD = 'Pr@S!C00l'
WITH SUBJECT ='TEST CERTIFICATE',
START_DATE='12/10/2017', 
EXPIRY_DATE='11/01/2018'

DROP CERTIFICATE TESTCERT

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

OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY CERTIFICATE TestCert 
WITH PASSWORD ='Pr@S!C00l'
GO


DECLARE @Text VARCHAR(MAX)
SET @Text = 'I am Super Human'

DECLARE @EncryptedText VARBINARY(128)
SET @EncryptedText = (SELECT ENCRYPTBYKEY(KEY_GUID(N'TestSymKey'),@Text))

DECLARE @DecryptedText VARCHAR(MAX)

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


SELECT @Text AS 'TextToEncrypt',@EncryptedText AS 'EncryptedText',@DecryptedText AS 'DecryptedText'

DROP SYMMETRIC KEY TestSymKey

GO