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.
(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
