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.

Ops Mgr SP1 RC0 is here. You can download it from MS Connect, here. This version will support upgrade to the final version of SP1. The final version will be public around mid febuari 2008.

Some of the news in SP1 RC0

• Improved performance when working in the console
• Improved advanced search in the console
• Support for both SNMP v1 and SNMP v2 network devices
• Support for exporting diagrams to Visio VDX format
• Support for copy/paste from the alert details pane (Ctrl+C and Ctrl+V)
• RMS encryption key backup wizard
• Support to copy views between management packs
• Both the repeatcount and override summary function are improved
• Scripts can noe be used for diagnostic tasks
• Enable to publish a report to for example sharepoint services web sites
• ACS is now supported on the Management and Gateway server roles
• ACS forwarding can be enable with command shell script

Read more about What’s New in Operations Manager 2007 Service Pack 1, here

If you want to discuss the service pack please join the SP1 news group at microsoft.public.opsmgr.sp1 at news.microsoft.com. I recommend JetBrains Omea Readers as news group reader, you can download it here. More information about Microsoft news groups here.

If you have tried edit Company Knowledge you have most probable recived a popup telling you “Visual Studio Tools For Office runtime is not installed“. To cure this you can download Microsoft Visual Studio 2005 Tools for Office Second Edition Runtime here. 

After that you will recive a popup telling you “Failed to launch Microsoft Word. Please make sure Microsoft Word is installed. Here is the error message: {0}“. To cure that, install Microsoft Word. Then it should be possible for you to edit company knowledge. I needed a reboot both after Visual Studio tools setup and after Word setup.

As a template in your company knowledge articles I suggest that you follow a common structure, for example summary, configuration, cause, resolutions and additional information. Make sure you press SAVE in Word first and then switch over to the Company Knowledge box and press SAVE there too before you close Word.

In the “Windows Client Operating System Management Pack Guide for Operations Manager 2007” you can read about monitoring Business Critical Clients. But even if you import this management pack, and add your business critical workstation to the “All Business Critical Windows Critical” group you will not get an alert when the workstation are unreachable for example disconnected from the network.

If you look at the “Computer Not Reachable” monitor you will see a default override that disable it for Computer Clients. The same thing for the “Health Service Heartbeat Failure” monitor. If you then add your workstation to the for example All Business Critical Windows Client/All Business Critical Windows XP Clients” group and then create a override that enable “Generates Alert” for the two monitors for this group, you will get an alert if they are unreachable.

Today I saw a question about reponse on a logfile monitor, as there is no “Diagnostic and recovery” tab on a log file monitor. A solution to this is to use a rule instead. If you create a Alert Generating Rules/Event Based/Generic Text Log(Alert) rule, then right-click it and choose properties, you can add a response on the Configuration tab. You can for example add to run a command or a script.

Boris Yanushpolsky a member of the System Center Operations team at Microsoft has upload a new version of MPViewer to his blog. This tool can help you show the following contents of a management pack: Rules, Monitors, Views, Tasks, Console Tasks, and Reports. Download this tool here.

I couple of days ago I posted about ACS and failover. As I wrote there is no failover back to the first ACS collector. If your ACS forwarder failover to a second ACS collector your will have your ACS forwarder data in two databases, as there is a one-to-one relationship between ACS databases and ACS collectors. That will lead to a lot of extra work for you.

I have wrote a simple script to show you one way to get the ACS forwarder to return to the first ACS collector in the list. This script gets the ACS collector settings from the registry, then it test if it can ping the first ACS collector, and if it can, it will restart the ACS forwarder service. That will make the ACS forwarder return to the first ACS collector. If it cant ping the server, it will wait some time and then try again.

Even if your ACS forwarder will start send data to the second ACS collector as soon as your first ACS collector is back online your ACS forwarder will return and you will hopefully not have that much data in the second database.

You can setup a rule to trigger on event ID 4368 from Source AdtAgent in the Application log. The description should also include the name of your second ACS collector. Make the rule run the script as a response.

You can download the script here

Tonight I have been thinker with ACS forwarder failover. You can read more about Audit Collection Services (ACS) in prior posts, but the fundamental is that the agent can be a ACS Forwarder which forward security events to a ACS collector (management server). My thought what is happening if a ACS collector goes down? What will the ACS forwarder do?

When you enable Audit Collection on a machine, in the “Run Task – Enable Audit Collection” box, there is a Override button (if you have multiple ACS collectors). If you click that one you can manually input collector servers. If you input “opsmgracs01.contoso.local, opsmgracs02.contoso.local” bot of these machines will be written in your ACS forwarder registry as AdtServers. You can verify that in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Policies\
Microsoft\AdtAgent\Parameters\AdtServers

If you disconnect the first machine, opsmgracs01, from the network you will get a event (ID 4369) in the event viewer at the ACS forwarder. This event tells you that the agent can not connect to any ACS collector, but a couple of seconds later you will see a event (ID 4368) telling you that the ACS forwarder is now connected to the other ACS collector, opsmgracs02.

I have been waiting around 30 minutes, after I reconnected the first ACS collector, and I have not seen a event telling me that the ACS forwarder has return back to the first ACS collect. You could control this with a extra script.

Summary: If you use the override button during enable audit collection you can setup multiple ACS collectors for your ACS forwarder. Remeber that if the agent failover, you will have ACS data in two different databases.