MORE ABOUT ME

Viewing SQL Server 2008 R2 Audit Logs Using SSMS 2012

quit_sql

22 Oct Viewing SQL Server 2008 R2 Audit Logs Using SSMS 2012

Short version of this post: you can’t.

I’m fairly certain Microsoft won’t be able to return the hours I spent trying to view the SQL Server 2008 R2 audit logs using SSMS 2012. But they don’t have too, really, because they know I can’t quit them at this point.

This all came about recently as I was testing some SQL Server Audit functionality against a SQL 2008 R2 instance. Since I can connect to an instance of SQL 2008 R2 using SSMS 2012 I did not think it an issue to create and configure some audits. And it wasn’t really, as I was able to connect and configure the audit specifications without any problems.

The problem, of course, was that every time I went to view the audit logs I was greeted with this:

I did what most anyone else would have done, I continued to bang away at my keyboard trying to generate the events to be captured. I kept trying and trying to get something to appear there but nothing ever would. I was fairly certain I was doing something wrong. I have been using SQL Server Audit for years, have written articles about it, and even given presentations on its use. Clearly I was making a mistake somewhere.

After a few hours of going back and forth with SQL Server I suddenly had an idea: Why not go and look at the log files directly using Notepad? When I went to the directory where I was storing the files I was shocked (!) to see that they were not empty! Clearly I was able to generate the events, just not view them!

Luckily I still have the SQL2008 R2 client tools on my laptop. I opened up SSMS 2008 R2 and went to view the audit log and was greeted with this:

Much nicer, don’t you think?

The lesson here: Sometimes it isn’t you, it’s them. Also? Keep the old client tools readily available, you never know when they will come in handy.

I don’t know how or why SSMS 2012 is failing me here. It would seem that this is something that should have been caught during a regression test back in Redmond, but it wasn’t. I wasn’t able to find anyone else having this issue so maybe it is something local to my laptop, I don’t know. I figured I would post this, not to complain, but just to help anyone else that may come across a similar error when using one version of SSMS in order to connect to and administer other versions.

It is also worth mentioning that I was able to view the audit log using the sys.fn_get_audit_file() system function. The only error that I found was with the SSMS 2012 GUI and viewing the audit logs. Everything else seemed to work just fine.

Hope this helps!

  • http://twitter.com/venzann Dave Dustin

    SSMS 2012 has a number of brain-farts when connecting to older systems.
    The default date sort order for events in ErrorLog view is a good example.

  • Pieter Vanhove

    Hi Tom,

    Just discovered the same issue today… Could not see any Audit log from the SSMS of SQL 2012. Took me only 30 minutes to figure out what my problem was thanks to your blogpost ;)! Found also this connect that describes the issue.
    https://connect.microsoft.com/SQLServer/feedback/details/790910/2012-sp1-ssms-cannot-view-audit-logs-for-2008-r2-instances

    Looks like there are 2 extra columns in SSMS 2012 that are not returned by the fn_get_audit_file funtion
    Still need to test if this is fixed in SSMS SQL 2014 and if you can read SQL 2012 audit logs with SSMS 2014

    Regards
    Pieter

    • ThomasLaRock

      Pieter,

      Yeah, after I saw your comment I started thinking about SSMS 2014 and any issues with viewing an older instance. Glad this post was able to help you!

      Tom