How to use SQL Server Encryption with Symmetric Keys?

Before create a symmetric key, you first setup your database’s master key and certificate and this master key and certificate help us to protect of your symmetric key store using the ALGORITHM AES_256.

In the below examples, I am using a default master key password is System!123 and the certificate name is Certificate_demo.

The following steps as given below,

--======================================
--MASTER KEY ENCRYPTION WITH PASSWORD
--=======================================
CREATE MASTER KEY 
    ENCRYPTION BY 
      PASSWORD = 'System!123' 
GO 

--===============================
--OPEN MASTER KEY
--==============================
OPEN MASTER KEY 
    DECRYPTION BY 
        PASSWORD = 'System!123'
GO

--=============================
--CREATE CERTIFICATE
--==================================
CREATE CERTIFICATE Certificate_demo 
  WITH SUBJECT = 'Certificate_demo' 
GO 

--====================================================================
--CREATE SYMMETRIC KEY WITH AES_256 SECURE ALGO USING ENCRYPTION CERTIFICATE
--===========================================
CREATE SYMMETRIC KEY SymmetricKey_demo WITH 
IDENTITY_VALUE = 'SymmetricKey_demo', 
    ALGORITHM = AES_256, 
        KEY_SOURCE = 'A very secure and strong password' 
    ENCRYPTION BY CERTIFICATE Certificate_demo; 
GO 

--=========================================================
--CREATE PROCEDURE FOR OPEN SYMMETRIC KEY WITH CERTIFICATE
--=======================================
CREATE PROCEDURE [dbo].[sp_OpenKeys] 
AS 
BEGIN 
BEGIN TRY 
    OPEN SYMMETRIC KEY SymmetricKey_demo    
    DECRYPTION BY CERTIFICATE Certificate_demo 
END TRY 
BEGIN CATCH 
    -- HANDLE NON EXISTANT KEY HERE 
END CATCH 
END 
GO

--=============================
--CREATE ENCRIPTION FUNCTION
--===============================
CREATE FUNCTION [dbo].[FNC_ENCRIPTION_TOKEN](@Token NVARCHAR(255)) 
RETURNS VARBINARY(8000) 
AS 
BEGIN 
    DECLARE @encriptToken VARBINARY(8000); 
    SET @encriptToken = EncryptByKey(Key_GUID('SymmetricKey_demo'), @Token) 
    RETURN (@encriptToken) 
END 

GO

--============================
--CREATE DECRIPTION FUNCTION
--============================
CREATE FUNCTION [dbo].[FNC_DECRIPTION_TOKEN](@encrypToken VARBINARY(8000)) 
RETURNS NVARCHAR(255) 
AS 
BEGIN 
    DECLARE @decriptToken VARBINARY(4000) 
    SET @decriptToken = DecryptByKey(@encrypToken) 
    RETURN (@decriptToken) 
END 
GO 

--===========================================
-- USE OF ENCRIPTION AND DECRIPTION FUNCTION
--===============================
DECLARE @GUID NVARCHAR(55);
DECLARE @EncryptedToken NVARCHAR(max);

SET @GUID = 'ADC10CFC-EC8A-424B-AF5E-1F1B177D304B'

-- OPEN SECURITY KEYS USING PROC [dbo].[sp_OpenKeys] FOR ENCRIPTION AND DECRIPTION
EXEC [dbo].[sp_OpenKeys]

-- THE FUNCTION [dbo].[FNC_ENCRIPTION_TOKEN] IS USE FOR GUID ENCRIPTION.
SET @EncryptedToken = [dbo].[FNC_ENCRIPTION_TOKEN](@GUID);

-- THE FUNCTION [dbo].[FNC_DECRIPTION_TOKEN], USE FOR DECRIPT TO A ENCRIPTED GUID.
SET @GUID = [dbo].[FNC_DECRIPTION_TOKEN](@EncryptedToken);

SELECT @EncryptedToken, @GUID

GO

--==============================================
--IF NEEDED, DROP MASTER KEY, CERTIFICATE AND SYMMETRIC KEYS
--==========================================================
DROP SYMMETRIC KEY SymmetricKey_demo
GO 

DROP CERTIFICATE Certificate_demo
GO 

DROP MASTER KEY
GO