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!