-- Create SYMMETRIC KEY & CERTIFICATE
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'newPasswordForEncryption';
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'MyCryptographyCertificate';
CREATE SYMMETRIC KEY MySymetricKey
WITH IDENTITY_VALUE = 'MyCryptoIdentityValue' ,
ALGORITHM = AES_256 ,
KEY_SOURCE = '$dggfg@9e&'
ENCRYPTION BY CERTIFICATE OHCertificate;
SELECT * FROM sys.symmetric_keys; -- returns created key
-- Create SYMMETRIC KEY & CERTIFICATE
-- Create Sp to Open CLose SYMMETRIC KEY Or You can create & use Scalar Function
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
-- =============================================
-- Author: Yawahang Rai
-- Create date: 12/20/2016
-- Description: Function To Open Close Symetric Key
-- =============================================
CREATE PROCEDURE dbo.SfOpenCloseKeys
(
@Task CHAR (5))
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
IF ( @Task = 'o' ) -- Open
BEGIN
OPEN SYMMETRIC KEY MySymetricKey
DECRYPTION BY CERTIFICATE MyCertificate;
END;
ELSE -- c
BEGIN
CLOSE SYMMETRIC KEY MySymetricKey;
END;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR (4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SET @ErrorMessage = ERROR_MESSAGE ();
SET @ErrorSeverity = ERROR_SEVERITY ();
SET @ErrorState = ERROR_STATE ();
RAISERROR (@ErrorMessage , -- Message text.
@ErrorSeverity , -- Severity.
@ErrorState -- State.
);
END CATCH;
END;
GO
-- Create Sp to Open CLose SYMMETRIC KEY Or You can create & use Scalar Function
-- Create Sp to To Encrypt Decrypt Or You can create & use Scalar Function
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
-- =============================================
-- Author: Yawahang Rai
-- Create date: 12/20/2016
-- Description: Stored Procedure To Encrypt Decrypt
-- =============================================
CREATE PROCEDURE dbo.SfEncryptDecryptTsk
(
@ValueToEncrypt VARCHAR (MAX) ,
@ValueToDecrypt VARBINARY (256) ,
@Type CHAR (5) ,
@OutE VARBINARY (256) OUT ,
@OutD VARCHAR (MAX) OUT )
AS
BEGIN
EXEC dbo.SfOpenCloseKeys @Task = 'o';
IF @Type = 'e' -- Encryption
BEGIN
-- Return the result of the function
DECLARE @RetrnE VARBINARY (256);
SET @RetrnE = ENCRYPTBYKEY (KEY_GUID ('MySymetricKey'), @ValueToEncrypt);
SELECT @OutE = @RetrnE;
SELECT @OutD = NULL;
END;
ELSE -- Decryption
BEGIN
-- Return the result of the function
DECLARE @RetrnD VARCHAR (MAX);
SET @RetrnD = DECRYPTBYKEY (@ValueToDecrypt);
SELECT @OutE = NULL;
SELECT @OutD = @RetrnD;
END;
EXEC dbo.SfOpenCloseKeys @Task = 'c';
END;
GO
-- Create Sp to To Encrypt Decrypt Or You can create & use Scalar Function
-- Implementation
-- Encrypt password
DECLARE @EncPassword VARBINARY (256);
DECLARE @DecPassword VARCHAR (MAX);
EXEC dbo.SfEncryptDecryptTsk @ValueToEncrypt = 'newPassword' ,
@ValueToDecrypt = NULL ,
@Type = 'e' ,
@OutE = @EncPassword OUT ,
@OutD = @DecPassword OUT;
-- Decrypt password
DECLARE @EncPassword VARBINARY (256);
DECLARE @DecPassword VARCHAR (MAX);
EXEC dbo.SfEncryptDecryptTsk @ValueToEncrypt = NULL ,
@ValueToDecrypt = 'Incrypted VARBINARY password' ,
@Type = 'd' ,
@OutE = @EncPassword OUT ,
@OutD = @DecPassword OUT;
Comments
Post a Comment