Add Function EncryptByKeyAutoCert
There is a function to automatically handle opening symmetric keys for decrypting (DecryptByKeyAutoCert), but there is no encryption equivalent. It would be extremely useful as a means to put a custom UDF around encryption. As it stands, OPEN SYMMETRIC KEY cannot be called from a function, thus there is no workaround. A stored proc is not the answer in this situation either, not to mention a srpoc cannot be called from a UDF.
Upvotes: 2<=-=Aug 4 2015 12:28PM=-=>
While the logic of proposal is understood and good and clear, nevertheless, can you please share scenrio in which you need to encrypt data and cannot invoke OPEN SYMMETRIC KEY.<=-=Oct 25 2017 1:52PM=-=>
It is possible to invoke OPEN SYMMETRIC KEY but this causes the sproc not to be in the plan cache and so forces a recompile for every execution.
The point of a corresponding EncryptByKeyAutoCert is to preserve the same functionality currently available via OPEN SYMMETRIC KEY and also to enable a SP: Cache Insert for the sproc.