>Invalid use of a side-effecting operator ‘OPEN MASTER KEY’ within a function


>Recently i have tried to create a simple function which should take a single nvarchar parameter as input and should return me its varbinary version, after encryption.When i executed my script, it failed with an error:

Msg 443, Level 16, State 14, Procedure fnc_EncryptNVarcharData, Line 7
Invalid use of a side-effecting operator ‘OPEN MASTER KEY’ within a function.
Msg 443, Level 16, State 14, Procedure fnc_EncryptNVarcharData, Line 8
Invalid use of a side-effecting operator ‘OPEN SYMMETRIC KEY’ within a function.
Following is the script, which i had used.
CREATE FUNCTION [dbo].[fnc_EncryptNVarcharData] ( @value nvarchar(2000) )
RETURNS VARBINARY(2000)
AS BEGIN
    DECLARE @EncryptedValue VARBINARY(2000)
  
    OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘iwillwin’   
    OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert   
    DECLARE @KeyGuid AS UNIQUEIDENTIFIER   
    SET @KeyGuid = key_guid(‘UserKey’)   
   
    SELECT  @EncryptedValue = encryptByKey(Key_GUID(‘UserKey’), @value)
   
    RETURN ( @EncryptedValue )
   
   END

 Problem occurs when you use OPEN MASTER KEY or OPEN SYMMETRIC KEY inside your function.
Solution is very simple.Remove these problematic lines from your function and write before using this function.

New script for this function would be like this

CREATE FUNCTION [dbo].[fnc_EncryptNVarcharData] ( @value nvarchar(2000) )
RETURNS VARBINARY(2000)
AS BEGIN
    DECLARE @EncryptedValue VARBINARY(2000)
  
    –OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘iwillwin’   
    –OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert   
    DECLARE @KeyGuid AS UNIQUEIDENTIFIER   
    SET @KeyGuid = key_guid(‘UserKey’)   
   
    SELECT  @EncryptedValue = encryptByKey(Key_GUID(‘UserKey’), @value)
   
    RETURN ( @EncryptedValue )
   
   END

And now when i like to use this function, i will mention OPEN MASTER KEY and OPEN SYMMETRIC KEY statements, first.

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘iwillwin’   
OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert
   
SELECT  CONVERT(nvarchar(200), DECRYPTBYKEYAUTOCERT(CERT_ID(‘Usercert’), NULL,                                               [dbo].[fnc_encryptNVarchardata](N’ConnectSql’)))
Advertisements

Posted on November 24, 2010, in TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: