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

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

www.code-sample.com/. Powered by Blogger.
^