"Invalid use of a side-effecting operator 'OPEN SYMMETRIC KEY' within a function." error while opening a symmetric key

Question

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?

score 7 · Answer 1 · answered Mar 20 '18 at 12:09

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;

This only answers half of the question. It doesn't address how to create the function that encrypts the data, and as far as I can tell, `ENCRYPTBYKEYAUTOCERT` doesn't exist. — Jargs, Apr 01 '19 at 20:48
@Jargs It does not exist. To `encrypt` a function you need to open the key in the parent routine (SQL procedure, trigger) or in the application. — gotqn, Apr 02 '19 at 05:02

score 5 · Accepted Answer · answered Jun 07 '15 at 18:37

5

There are several things you can do inside a procedure but can't do inside a function. Based on Ben Cull's blog, you can get around this limitation by creating a procedure that handles opening the keys and call that before using the function.

The procedure:

CREATE PROCEDURE OpenKeys
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        OPEN SYMMETRIC KEY MyKEY
        DECRYPTION BY CERTIFICATE MyCERT
    END TRY
    BEGIN CATCH
        -- Handle non-existant key here
    END CATCH
END

Then just call this before calling the functions.

answered Jun 07 '15 at 18:37

James Z

12,209
10
24
44

Should we also write a `CloseKeys` function to call afterwards? – Jargs Apr 01 '19 at 20:49
1

I'd say it depends what you're doing and how long your session is going to live, since "An open key will continue to be available until it is either explicitly closed or the session is terminated." – James Z Apr 02 '19 at 15:46

"Invalid use of a side-effecting operator 'OPEN SYMMETRIC KEY' within a function." error while opening a symmetric key

2 Answers2