Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2008 Audit doesn't show parameters Expand / Collapse
Author
Message
Posted Saturday, September 5, 2009 10:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 1:45 PM
Points: 7, Visits: 13
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
Post #783364
Posted Saturday, November 28, 2009 8:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 1, 2013 4:47 AM
Points: 35, Visits: 45
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.
Post #825861
Posted Friday, July 15, 2011 2:34 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:05 PM
Points: 252, Visits: 1,720
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.
Post #1142802
Posted Monday, July 18, 2011 10:47 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:05 PM
Points: 252, Visits: 1,720
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
Post #1143542
Posted Sunday, April 7, 2013 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:13 AM
Points: 8, Visits: 105
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.
Post #1439664
Posted Thursday, May 30, 2013 4:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 18, 2013 6:51 AM
Points: 1, Visits: 30
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.
Post #1458483
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse