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.

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, 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
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base