OPEN SYMMETRIC KEY & the Plan Cache

SQL Server natively supports column-level encryption. You can visit here to learn how to do it. But the basics are:

OPEN SYMMETRIC KEY

Do encryption and/or decryption stuff

CLOSE SYMMETRIC KEY

In order to use OPEN SYMMETRIC KEY, you have to specify either a password or a certificate which protects the symmetric key. If you use a password, it is clear text in the sproc code and so SQL Server does not put the sproc in the plan cache. No execution plan reuse and no usage & performance data in sys.dm_exec_procedure_stats DMV.

Thing is… even if, instead of a password, you use a certificate which presents no security exposure, SQL Server still does not put the sproc in the plan cache. If it sees OPEN SYMMETRIC KEY, no plan cache.

Guilt by association.  

SQL Server 2008 introduced another option for decryption only – DECRYPTBYKEYAUTOCERT. This command accepts the certificate name as a parameter and works on its own without OPEN SYMMETRIC KEY and so the sproc can go in the plan cache. Currently, there is no corresponding ENCRYPYBYKEYAUTOCERT.

Here is the big picture:

So why not a corresponding ENCRYPTBYKEYAUTOCERT?

I asked Microsoft at SQL PASS 2017. They took the question back to the mother ship and here is the email reply I received.

Hi Regan,

So the reason we support decryptByAutoCert but not the encryption part is twofold:

1.      Performance: We had received a steady flow of complaints that select operations with decryption occur frequently and should be made as performant as possible; hence we invested in multi-threading of decryption.

2.      Unlike decryption, which is deterministic and can pretty much execute in parallel without much code change, encryption is much harder to parallelize and even harder to test and ensure it works correctly.

So unfortunately, we have not funded parallelizing the encryption operation yet.

Thanks for bringing this to the forefront again. I will put this on our list for planning items.

There is a Connect UserVoice item here. Please vote!

In the meantime, there are two work-a-rounds.

(1)   To at least get the sproc in the DMVs so you can monitor usage & performance, move the actual T-SQL code to a “private” sproc and have the application-called sproc just be a wrapper to call the “private” version. This does not get the real code into the plan cache; it still has to be compiled on every execution. But at least you can get all the metrics from the sys.dm_exec_procedure_stats DMV for the wrapper sproc.

(2)   This 2010 MSDN blog post suggests using EXECUTE to fire dynamic SQL for the OPEN SYMMETRIC KEY command (whether using password or certificate). This enables the sproc to go in the plan cache. You still get a SP:CacheMiss for the OPEN SYMMETRIC KEY - but at least now it is only for that one command. 

We do what we can.

Later