October 9, 2015 at 10:29 am
Hi All,
I'm interested in using SQL Audit for auditing since I've read it's considered a compliant tool for capturing/auditing for things like HIPAA/SOX.
However it's my understanding that the tool does not capture information when they are "parameterized" queries.
example:
>>select firstname, lastname, SSN, address, birthday from Employee where ID = '234324'
Also if we change the database to forced
-- I know I'm so out of date and we're already heading towards 2016 🙁
ALTER DATABASE AdventureWorks2012 SET PARAMETERIZATION FORCED
How do people here address that?
It's hard to perform and audit and user: to flag a user "JimBob" to specifically identify:
runs: >>select firstname, lastname, SSN, address, birthday from Employee where ID = @P1
Thanks for any thoughts and feedback.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
October 9, 2015 at 11:07 am
To my knowledge SQL Audit will capture ad hoc queries, parameterized queries and stored procedures. It just doesn't keep the values that are passed to parameters. This is the same for stored procedures, not just parameterized queries. If you want to capture every single value passed, you can just use Extended Events to capture the queries made to the system. You'll have to be prepared to manage quite a lot of data, but it's easy enough to capture all everything if that's what you want. You could also look to enable Change Data Capture in addition to SQL Audit if you really want to see each and every change.
The audits I've had done in the past didn't require us to capture all values passed to the database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2015 at 1:02 pm
Grant Fritchey (10/9/2015)
To my knowledge SQL Audit will capture ad hoc queries, parameterized queries and stored procedures. It just doesn't keep the values that are passed to parameters. This is the same for stored procedures, not just parameterized queries. If you want to capture every single value passed, you can just use Extended Events to capture the queries made to the system. You'll have to be prepared to manage quite a lot of data, but it's easy enough to capture all everything if that's what you want. You could also look to enable Change Data Capture in addition to SQL Audit if you really want to see each and every change.The audits I've had done in the past didn't require us to capture all values passed to the database.
Hi Grant thank you for the reply! (sorry I sending a late thank you) I had to work on implementing SSL and fix some non-SQL stuff and didnt get to check SQL Server central. Thanks again for the feedback and comments much appreciated.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply