"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?

Answer 1

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.