October 18, 2011 at 10:31 am
Hello, I have a question about something I can't seem to find any information on.
Basically we have sensitive data stored encrypted in varbinary columns using stored procedures that call EncryptByKey. There are also stored procedures that use DecryptByKey for viewing the data from an application.
I have been asked to audit whenever the data in those columns are decrypted to be read, whether it be from a DBA/admin via Management Studio or an application.
I looked at SQL Audit (we're running 2008 Enterprise) but it just didnt seem like the right tool for this, same with CDC.
I guess a SQL Trace would come close, but to satisfy the audit specification I would need to look for:
1. Save any proc/function with DecryptByKey in it to a table
2. Audit any procedure/function being Created or Altered that had DecryptByKey in it, also save to a table
3. Trace for direct text data with DecryptByKey
4. Another trace to watch for executions of items identified in #1-#2?
Am I missing something? I would think this question would have been asked more, or perhaps I just have entirely too strict auditors 😉
MCSA SQL 2014
October 18, 2011 at 12:07 pm
The only method that comes to my mind is a Server side trace that watches for the objectID. But that can also be manipulated by a SysAdmin.
-Roy
February 19, 2016 at 12:48 pm
Did you ever develop a solution for this? We need to do the same thing...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply