In this post I will show how to setup SQL logon auditing. You will have to complete a number of steps before you have a complete auditing. Beware that logging all logon events can fill up your database. This post includes the following steps:

1. Configure SQL to audit logon events
2. Configure Ops Mgr to collect logon events
3. Create a report to show the collected data

Configure SQL to audit logon events

Login auditing can be configured to write to the error log on the following events.

• Failed logins
• Successful logins
• Both failed and successful logins

To configure login auditing

1. In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine with Object Explorer.
2. In Object Explorer, right-click the server name, and then click Properties.
3. On the Security page, under Login auditing, click the desired option.
4. After you have applied this setting you might need to restart the SQL service before it take effect. After that you should see logon events in the local application log, in event viewer.

Configure Ops Mgr to collect logon events

You will need some suitable class to target your rule to. If you import the SQL MP you will get a number of SQL classes.

1. In the Ops Mgr Console click Authoring
2. Right-click Rules and choose to create a new rule
3. Create Rule Wizard – Rule Type: Choose to create a Collection Rules/Event Based/NT Event log. Choose a suitable management pack and click next
4. Create Rule Wizard – General: Input a rule name for example “SQL Auditing – Success Logon”. Choose a target, for example “SQL 2005 DB Engine”. Click Next
5. Create Rule Wizard – Event Log Type: Log Name should be Application then click Next
6. Create Rule Wizard – Build Event Expression: input
   Event ID equals 18453
   Event Source equals MSSQLSERVER
   Click Create

To collect also failed logon you need to create a rule to collect event ID 18456.

You can use the Effective Configuration Viewer from the Ops Mgr 2007 resource kit to verify if your new rule affect a SQL machine. You can also create a new event view, to show these events, after a couple of minutes you should see them.

Create a report to show the collected data

There is no report model in Ops Mgr by default, so if you want to create a brand new report, you must start with create a report model. You can do that with SQL Server Business Intelligence Development Studio. Take a look at Jonathan Hamb step by step guide how to create a report model here.

What you can do, with default reports, is a linked report. A linked report is like a shortcut to a program, it is a link that provide settings that inputs in a existing report. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.

To create a new report, for example a report showing all success logon events,

1. Start the Ops Mgr console and click Reporting
2. Click Microsoft Generic Report Library
3. Click Event Analysis and then Open
4. In the report select suitable FROM and TO for example
5. Click Add Group and select SQL 2005 DB Engine Group
6. Select MSSQLSERVER as SOURCE, 4 as Type, 18453 as Event ID and Success Audit as Event Type
7. Click Run

Now you can see a report with all Sucess Audit. You can now click the File menu and choose Publish. This report will now be stored as a linked report under Authored Reports. The next time you want to see success logons to SQL you can click this report direct in the console, and all the parameters will be there.

If you select MSSQLSERVER as source and 4 as event type, you will get both Failure and Success audit events, but you must first make sure you have rules to collect them both.