Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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.

2 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

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.

0 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base