SQL Server 2008 Database Audit on INSERT UPDATE and DELETE actual SQL and not parameter values
If a database Audit Specification is created for a table object on SELECT, INSERT UPDATE and DELETE and the Transact SQL to perform the SELECT, INSERT UPDATE or DELETE contains a parameter, then the parameter is shown in the Audit Log instead of the parameter value.
For example, executing the following TSQL statement:
INSERT INTO [config] VALUES ('test','1234')
creates an audit entry:
statement:INSERT INTO [config] VALUES ('test','1234')
However, executing the TSQL:
DECLARE @stat1 varchar(10)='test'
DECLARE @stat2 varchar(10)='1234'
INSERT INTO [config] VALUES (@stat1,@stat2)
Creates the audit entry:
statement:INSERT INTO [config] VALUES (@stat1,@stat2)
In order for the Audit to be of any use, I would like to know the values that are inserted into the database table and not the parameter names.
This would improve the capability of the Audit to record actual values involved in the INSERT, UPDATE OR DELETE statements.
I am aware of CDC, but incorporating this functionality into Database Audit would make it more valuable for audit purposes.
Upvotes: 54<=-=Nov 29 2010 8:49AM=-=>
First of all, thank you for your feedback!
This is a great suggestion to improve the SQL Server Audit experience and we will try to consider this in our future release planning.
Thank you and regards,
SQL Server Engine
I can’t believe there aren’t more comments on this subject. I assume it’s simply because not many are currently using the auditing system, likely one reason is due to the limitation described here.
It seems like a logical step to include parameters within the additional_information field (which appears completely unused with regard to table level events). Perhaps this was overlooked due to performance issues?
While I’m on the topic, and understanding that CDC is NOT necessarily to be used for auditing, it seems odd to me that the information regarding WHO did something is not included with the data tracked.
You’d think that since there is no mechanism to determine who did something in CDC there would be a common way to stitch auditing information together with CDC to create a more complete picture.
Now, I’ve worked around that issue by creating a signed procedure, a SQL Server Agent job and a trigger (much simpler than previous manual change capture needs but still onerous) and a couple of sprocs.
I mean, this is CRAZY :)
We can see DML executed against a table, but not the parameters that indicate some kind of clue as to WHAT might have been “seen”. So the Audit system answers who did it, and when it was done, but not what was done.
CDC answers the question (not related to SELECT!) of what was done, and when it was done but not WHO did it.
Neither gives you a way to easily combine the results to get a good idea of what is happening.
And change tracking….really? The only way to get WHO did something there is to create a maintenance nightmare of INSTEAD OF triggers.
My take is this:
Audit: To be truly useful it MUST have the parameters used in the query somewhere and if it’s a performance hit then maybe parameter tracking is something that can be disabled.
CDC: To be truly useful it needs to pull the who from the logs during it’s log parsing (or give you a way to marry the data with the Audit)
NICE TO HAVE: For all three data specific event systems it would be of great use (I think) to have some way of (more or less) definitively stitching the data from the three systems together with some kind of common identifier.<=-=Aug 3 2011 3:33AM=-=>
For SQL Server 2008 Microsoft released a bugfix:
DOES Microsoft think about releasing a bugfix for SQL SERVER 2008 R2 RTM too?<=-=Aug 3 2011 3:35AM=-=>
SQL Bug #316148 (KB 967552): FIX: SQL Server 2008 Database Auditing shows query criteria as a parameterized value instead of the actual record.<=-=Sep 16 2011 10:38PM=-=>
I second the request for a fix to this in SQL 2008 R2. This will be an extremely helpful feature, but only if it includes the actual data along with the queries.<=-=Oct 26 2011 1:27AM=-=>
This is a serious problem for tracability of actions. We need to know what happened and who did it. To work around this we now use SQL Audit and CDC in unison, exporting to a separate audit database. It would be preferable to have just one source however.<=-=Feb 10 2012 2:58AM=-=>
Team, any update here?<=-=Feb 10 2012 2:59AM=-=>
This would be for SQL Server 2008 R2.<=-=Feb 29 2012 1:39PM=-=>
Ditto what Atif said.
Any news about this? Are there any service packs to resolve this in SQL2008 R2?
We are using R2 and would like/need the audit to capture the parameter values.<=-=Mar 14 2012 11:00AM=-=>
We are also using Microsoft SQL Server 2008 (SP2) – 10.0.4266.0 (X64)
Any news on this?
We would also like to get this fixed ASAP or is there already a fix which we can apply?<=-=Apr 24 2012 11:51AM=-=>
Yes, is there any news on this?
I’m working on a project for Cook County, Illinois and this bug in the auditing is a major issue. We’re running SQL Server 2008 R2 v10.50.1600.1 (X64) Enterprise Edition (64-bit).
I have to say, Microsoft is catching some bad press in the office on this one. I mean it was fixed for SSE Ed 2008 back in 2009. Why is it still an issue in SSE Ed 2008 R2?
I have been looking for others having the same problem with the audit in R2 for months and finally found this. I keep getting told on other forums this isn’t a problem with R2 and yet there it is.<=-=May 4 2012 12:39AM=-=>
I have heard from some reliable sources that this problem has been extended to “SQL 2012” too.
Still waiting for any response from microsoft team, Yes or No, anything so that we can plan accordingly.
(please provide any other link if I am posting on wrong thread.)
Atif.<=-=Jun 10 2012 11:00AM=-=>
We’re on SQL 2008 R2 SP1 + CU5 and suffering this same issue. SQL Audit should extract the query’s param values – wether from autoparametrized text or even from user binds (ODBC/OLEDB, whatever). Our customer must pass a formal security certification review, and frankly this is really annoying, since this makes auditing on DML unusable, ihmo.
This is really critical for anyone trying to reach standards compliance.
For updates, we tried linking CDC with database_transaction_end extended events via transaction_id, but whoa.. that “tran_id” inside that lsn_time_mappings cdc table is not the transacion_id recorded in xevents or traces. So, this is a big stop.<=-=Feb 9 2013 3:19PM=-=>
Any news about this? I need it in MSSQL 2008R2! It is crazy that it is fixed in 2008 and not in 2008 R2. 2012 will works?<=-=Feb 11 2013 3:00PM=-=>
Thank you for your feedback. The auto-parameterized text defect should be resolved in all 2008+ releases with the latest SPs – please contact CSS if you have continue to have issues. For explicitly parameterized queries, it’s an item we are looking at when planning future releases of SQL Server. Thanks, Jack Richins<=-=Dec 6 2013 7:55AM=-=>
I’ve the same error on SQL Server Enterprise with Service Pack 2 and i don’t have any alternative solution for this case.<=-=Jul 29 2014 11:42AM=-=>
I’m having the same issue with SQL Server 2012 (specifically 11.0.3128). We need this for HIPAA compliance before we can move databases from SQL2005.<=-=Sep 29 2014 2:10PM=-=>
We are running SQL Server 2008 (not R2) v10.0.5520 with Service Pack 3 and yet this issue is not corrected. Since SP1 documents a fix for this issue, is there a possible stand-alone hotfix that will work with 2008?<=-=Jan 6 2015 11:21AM=-=>
We are running SQL Server 2008 R2 SP3 10.50.6000. This fix would be much appreciated as the Audit is not useful when it could be omitting critical information. Most of your customers have HIPAA/SOX auditing requirements that need this detail. The alternatives are much less efficient and troublesome.<=-=Jan 8 2015 5:34AM=-=>
It’s absolutely useless for an audit functionality to not audit correctly what values are queried/modified… When will this be solved? How come there’s a hotfix for SQL Server 2008 but not latest versions? Is it ever going to be fixed?<=-=May 4 2017 1:57PM=-=>
Auditing should report the parameter values even for explicitly parameterized queries. Otherwise it’s an incomplete solution.<=-=Jan 4 2018 10:51AM=-=>
Dear MS, please fix 8-yr old bugs instead of releasing new features I will never use such as Operations Studio