SQL 2008 Audit doesn't show parameters

Question

SQL 2008 Audit doesn't show parameters

jgnewberry

Old Hand

Points: 369
More actions
September 5, 2009 at 10:54 am

#210298

I need to audit all accesses, updates, inserts, deletes, executes, etc. done to specific tables in our database. I upgraded my SQL Server to 2008 so I could use the new Audit functionality. This works great and shows me everything I need and who accessed it. The problem is I don't know what the query contained. Let me give some examples:
If I run a query from the Management studio it gives me the following:
Query that was run: Select * From Customer Where ID = '123'
Audit shows this: Select * From Customer Where ID = '123'
This is what I would expect all the time. But if I add in a parameter it gives the following:
Query that was run: Declare @id varchar(255)
Set @id = '123'
Select * From Customer Where ID = @id
Audit show this: Select * From Customer Where ID = @id
I would like to be able to see what the parameter was in case I need to investigate something.
Does anyone have any suggestions? Our application sends in thousands of stored procedures with parameters and how it currently works won't help.
Thanks in advance!
JN

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

estanito SSC Enthusiast Points: 173 More actions · Answer 1

I just starting using SQL Server 2008 and with it the auditing feature. I come across the same problem you are having but possible it is different after all.

I noticed that if you audit a select on a table and you execute a stored procedure that performs a select on that particular table, the statement is the query with @parameterName instead of the parameter value. Now if you remove that audit and add execute on that stored procedure, the statement is correctly. However goes well when you perform the execute in SQL Server Management Studio, I can't seem to have the same result if I execute a stored procedure from a .NET application where I am using System.Data.SqlClient with System.Data.CommandType.StoredProcedure.

So if someone can help me (us) out, is glad to do so.

Thanks.

Dave23 Hall of Fame Points: 3903 More actions · Answer 2

JN, did you ever find a satisfactory solution to this issue? I'm facing the same problem. The process of simple parametrization by the query optimizer is confounding my ability to audit ad hoc select queries in SSMS. I'm using Change Data Capture to track Inserts, Updates, and Deletes, but I've still not come up with a solution for auditing selects.

Dave23 Hall of Fame Points: 3903 More actions · Answer 3

I've found a solution for this issue today, so I thought I would pass it along. Cumulative update package 3 for SQL Server 2008 Service Pack 1 addresses SQL Bug #316148 (KB 967552): FIX: SQL Server 2008 Database Auditing shows query criteria as a parameterized value instead of the actual record.

I installed the hotfix on our dev server, and found that the actual search predicate is now being included in the audit statement capture.

The fix link is http://support.microsoft.com/kb/971491/LN

arbcoll Old Hand Points: 317 More actions · Answer 4

Can anyone confirm this is working on SQL Server 2008 R2 please?

Tested in SP2, CU #5, and the results are negative, the parameter values are not shown in SQL Server Audit.

robert_nickel Valued Member Points: 69 More actions · Answer 5

From the KB article http://support.microsoft.com/kb/967552:

The fix applies to the queries executing in the database with Simple Parameterization enabled.
The fix does not apply to the queries executing in the database with Forced Parameterization option enabled.
The fix does not apply to the queries that are explicitly parameterized using sp_executesql stored procedure.

So our security posture of using explicit parameterization (which I'm pretty sure is the industry standard to prevent SQL injection... doesn't Microsoft even specifically recommend this??) means there is no fix for us.