check view exists sql server

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
ANIL SINGH

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Google Amazon and Flipkart Book Store!

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.
ASK Questions
SQL Server NodeJs TypeScript JavaScript Angular SQL Server My Book