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 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..
|