I am trying to open symmetric key inside two functions. Like this:
CREATE FUNCTION DECRYPTDATA
(
@CipherText NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(MAX)
OPEN SYMMETRIC KEY MyKEY DECRYPTION BY CERTIFICATE MyCERT
SELECT @Result = CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@CipherText))
RETURN @Result
END
GO
CREATE FUNCTION ENCRYPTDATA
(
@Text NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(MAX)
OPEN SYMMETRIC KEY MyKEY DECRYPTION BY CERTIFICATE MyCERT
SELECT @Result = ENCRYPTBYKEY(Key_GUID('MyKEY'),@Text)
RETURN @Result
END
GO
But I am getting this error:
Invalid use of a side-effecting operator 'OPEN SYMMETRIC KEY' within a function.
Why this is happening?
Actually, you can do this in function without opening the symmetric key using the DECRYPTBYKEYAUTOCERT function:
Decrypts by using a symmetric key that is automatically decrypted with a certificate.
The following example demonstrates this:
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'sm_long_password@'
GO
CREATE CERTIFICATE CERT_01
WITH SUBJECT = 'CERT_01'
GO
CREATE SYMMETRIC KEY SK_01
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE CERT_01
GO
CREATE FUNCTION [dbo].[TEST] (@encryptedValue VARBINARY(256))
RETURNS NVARCHAR(128)
AS
BEGIN;
RETURN CONVERT(NVARCHAR(128),DECRYPTBYKEYAUTOCERT(CERT_ID('CERT_01'), NULL, @encryptedValue));
END
GO
DECLARE @encryptedValue VARBINARY(256);
OPEN SYMMETRIC KEY SK_01 DECRYPTION
BY CERTIFICATE CERT_01
SET @encryptedValue = ENCRYPTBYKEY(KEY_GUID('SK_01'), N'Stack Overflow')
CLOSE SYMMETRIC KEY SK_01;
SELECT [dbo].[TEST] (@encryptedValue);
DROP FUNCTION [dbo].[TEST];
DROP SYMMETRIC KEY SK_01;
DROP CERTIFICATE CERT_01;
DROP MASTER KEY;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With