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.
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
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.
https://blogs.msdn.microsoft.com/sqlserverfaq/2010/09/07/open-symmetric-key-command-prevents-query-plan-caching/
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.