We had planned to abstract the use of our encryption logic inside a user-defined function in SQL Server 2008 R2. The caller would send the plain text and we could just send back the encrypted content without having the user to worry about with keys, certificates, encryption algorithms and the daily grind.
But, it appears its not so straightforward, since one would get this error if one attempted to create such a UDF:
"Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function."
The workaround is to write this logic in a stored procedure and have that executed by the caller. Of course, it isnt as nice as using the UDF in an INSERT statement directly. But, I guess we can live with that for now.
Reference:
http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/b56f3ef4-10d4-46c1-ade1-dedeee36ac33
But, it appears its not so straightforward, since one would get this error if one attempted to create such a UDF:
"Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function."
The workaround is to write this logic in a stored procedure and have that executed by the caller. Of course, it isnt as nice as using the UDF in an INSERT statement directly. But, I guess we can live with that for now.
Reference:
http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/b56f3ef4-10d4-46c1-ade1-dedeee36ac33
No comments:
Post a Comment