How do you handle audit logging with SSRS?

Question

I have some reports in SQL Server Reporting Services 2005 that I need to keep audit logs for. The audit log should include who ran what report with what parameters. I can't use Windows authentication.

What is the best way to log this information?

Answer 1

From memory SSRS has built in logging for this exact situation

Answer 2

Have a look at the ExecutionLog table in the ReportServer database. This contains information on who ran what report and with what parameters.

I'm not sure how this is going to work without Windows authentication though, as it'll have no way of knowing who's running what report.

Answer 3

Can you share some info on your authentication method?

MS provides some report samples that include everything you need to get started.

For SSRS 2005 http://www.codeplex.com/MSFTRSProdSamples/Wiki/View.aspx?title=SS2005!Server%20Management%20Sample%20Reports&referringTitle=Home

Many more report Samples. http://www.codeplex.com/MSFTRSProdSamples/

Answer 4

If you are using a custom security extention, you will still be able to get all the info you need from the ExecutionLog table. Unless off-course if all your users uses a shared login, in which case you probably need to reconsider your architecture, depending on the importance of the audit log.

Answer 5

The previous comments were dead on accurate that you can mine the data from the ReportServer ExecutionLog table in SQL Server 2000/2005 or the ExecutionLogStorage table in SQL Server 2008. If you are using form-based authentication to access the reports instead of windows authentication, then you are probably passing some unique UserID, CompanyID, CustomerID, or other value as a parameter in your reports. If this is the case, then the built-in table captures the parameters already. If you aren't passing the unique user identifier as a parameter, then you will probably need to rely on logging report executions in your application itself.