Catch-22: Securing ACS Reports AND scheduling them. Part V: Auditing Security

--------------------------------------------------------------------------------- 
Postings in the same series:
Part   I: How Catch-22 was born…
Part  II: What do we need?
Part III: Setting Security
Part IV: Setting the subscriptions on the ACS Reports
---------------------------------------------------------------------------------

This will be the last posting in this series and will be all about auditing the security on the SQL Server hosting the ACS Database which is also the SQL Server Reporting Services (SSRS aka SRS) for the ACS Reports.

In order to accomplish this certain tasks need to be completed:

1. Setting an Auditing Policy on the SQL Server
2. Creating a MP which monitors the Security Event log on the SQL Server and raises Alerts
3. Creating a Notification Model for the Alerts generated by the MP mentioned in Step 2.

#1: Setting an Auditing Policy on the SQL Server
It is to be preferred that any policy is created on AD level, not on the server level since the latter is much harder to maintain and to keep a solid overview. How ever in this example I will set locally on the server the Audit Policy.

1. Go to Start, in the run box, type gpedit.msc <enter>. The Local Group Policy Editor will be started now.
   
   
2. Go to Computer Configuration > Windows Settings > Security Settings > Local Policies > Audit Policy and select the option Audit account management. Open it, select both options (Success and Failure) and click Apply > OK.
    
   Close the Local Group Policy Editor screen.

#2: Creating a MP which monitors the Security Event log of the SQL Server and raises Alerts
Now a MP (or better, a set of rules/monitors all contained in the same MP) needs to be created which monitors the Security Event log of the SQL Server hosting the ACS Database. These Security Events will be monitored:

Monitor/Rule Name	Security EventID	Alert Description (Based on Windows 2008 R2 events!!!)
Security Log Cleared	1102	User '$Data/Context/Params/Param[2]$' from Domain '$Data/Context/Params/Param[3]$' cleared the Security Log of server  '$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/DNSName$'
User Created	4720	User '$Data/Context/Params/Param[4]$' created the new User Account '$Data/Context/Params/Param[1]$' on server '$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/DNSName$'
User Deleted	4726	User '$Data/Context/Params/Param[4]$' deleted the User Account '$Data/Context/Params/Param[1]$' on server '$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/DNSName$'
User Added To Group	4732	User '$Data/Context/Params/Param[6]$' added User '$Data/Context/Params/Param[2]$' to Group '$Data/Context/Params/Param[5]$' on server '$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/DNSName$'
User Deleted From Group	4733	User '$Data/Context/Params/Param[6]$' deleted User '$Data/Context/Params/Param[2]$' from Group '$Data/Context/Params/Param[5]$' on server '$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/DNSName$'
Group Created	4731	User '$Data/Context/Params/Param[4]$' created the group '$Data/Context/Params/Param[1]$' on server  '$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/DNSName$'
Group Deleted	4734	User '$Data/Context/Params/Param[4]$' deleted the group '$Data/Context/Params/Param[1]$' from server  '$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/DNSName$'

The Alert Description has been set up in such a manner that an Alert is raised with a clear message like this example:

All rules/monitors (I come to that discussion further up in this posting) are disabled by default and targeted at Windows Server 2008 Computer.

Of course it could be targeted at SQL 200x Computers. This is just a matter of choice. Another approach could be creating a whole new Class and target it at that new Class. As you see, many possibilities are there and it is just a matter of choice.

For the rule/monitor the Rule target/Parent Monitor is Security (of course). With an override the rules/monitors are enabled and targeted at a single server which is the server hosting the ACS Database which is also the SQL Server Reporting Services (SSRS aka SRS) for the ACS Reports.

Discussion: Do I Rule or Monitor?
Knowing what Events to target and what the Alert Description is going to look like isn’t enough. Now the choice has to be made: do I use a Monitor or a Rule?

Monitor:
When a monitor comes into play there are some issues to reckon with. Suppose two new groups are created. Only an Alert for the first new group created will be raised. Not for the second one since the monitor is in a critical health state.

Also when a monitor is being used, it needs a reset. There isn’t another event which can be used to reset the monitor back to a healthy state. So a timer reset could be used. But when no one acts upon the Alert there is a change this Alerts goes away unnoticed. So a timer reset won’t do.

The only viable choice could be a manual reset of the monitor. When that is being used, the Alert Description needs some additional information like:

Still, technique here won’t suffice. Also some organizational processes need to be in place AND functional. So when such an Alert comes in a small chains of actions takes place, like checking out the SQL Server and getting to know why the security has been changed. Perhaps it had a valid reason and nothing bad is at order.

Rule:
When a Rule comes into play it has the advantage that even when the Rule has triggered an Alert when a new group has been created (for instance), it will do so again when a second group has been created. So one gets an Alert per created Group. And a rule doesn’t need a reset either so it is less labor extensive.

However, when closing that Alert it is gone from the Console so it easier to have it unnoticed. In order to get it noticed, a Notification Model is needed which send out the Alerts as (for instance) a mail message.

#3: Creating a Notification Model
The easiest way to achieve this is to have the Alerts raised in the Console, right click on every single Alert and create a subscription for it.

Now all is in place and you are ready to go. Now you have ACS Reports secured so only a certain group of people can run them AND to have these reports scheduled as well.