none
Unable to create a function by using symmetric encryption

    Question

  • Msg:

    Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.

    "open symmetric keys" is not allowed in a function?

    if I want to encrypt a string in a function by sql2005's internal functions ,how can I do ?

     

    Monday, January 29, 2007 9:28 AM

Answers

  •   As the error message indicates, OPEN SYMMETRIC KEY is an operation with a side effect (i.e. it affects the current session’s key ring) and user defined functions (UDFs) cannot have such operations in the body.  You can create a UDF using EncryptByKey in it and call OPEN SYMMETRIC KEY before calling the UDF.

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, January 29, 2007 6:10 PM

All replies

  •   As the error message indicates, OPEN SYMMETRIC KEY is an operation with a side effect (i.e. it affects the current session’s key ring) and user defined functions (UDFs) cannot have such operations in the body.  You can create a UDF using EncryptByKey in it and call OPEN SYMMETRIC KEY before calling the UDF.

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, January 29, 2007 6:10 PM
  • Instead of using a function, you can create a procedure with an output parameter for passing out  the result.

    Thanks
    Laurentiu

    Tuesday, January 30, 2007 12:42 AM