How do I use encrypt/decrypt functionality within a user defined function?

For example, I want the function to return a decrypted value similar to this:

SELECT @OutString=convert(varchar(60), convert(varchar,
decryptbykey(@deText)))

But I need to open a symmetric key to decrypt and a a user defined functions
will not support an OPEN SYMMETRIC KEY statement  (error: Invalid use of
side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a
function.)

All the examples I can find on the web use stored procs for encrypt/decrypt
functionality.  

Can anyone confirm this limitation with functions?  Is there a work around?

You wouldn't be able to. Because the encryption functions are
non-deterministic (uses Symmetric_GUID) and you cannot call them inside a
function (though 2005 allows getdate() alone). why don't you use a stored
procedure with an output parameter? if you want to call this function for a
column in your select query, I suggest you move this to the query.

Thanks Omnibuzz

Yes, I thought as much but I was hoping someone had a clever work around.

We are encrypting creditcard numbers and need to modifying dozens of stored
procs that access the encrypted credit card number column.  The easiest way
(least amount of coding) to implement this would be to make a function call
to the credit card column within the SELECT statement that does the
decryption.  But then I have the limitation of not being able to open the key
in a function.   If  I open the key within the proc that calls the function,
it may stay open too long and compromise security.

However, I think I may be able to get this to work using decryptbypassphrase
and passing in a password to the function rather than relying on a key.

I will let you know how it goes.

Thanks again
Dave






Show quoteHide quote

Trying to match lowercase
too long...
datetime between problem
SQL Join question - getting most recent value in a history table
Select problem Msg. 156