Catch-22: Securing ACS Reports AND scheduling them. Part I: How Catch-22 was born…

--------------------------------------------------------------------------------- 
Postings in the same series:
Part  II: What do we need? 
Part III: Setting Security
Part IV: Setting the subscriptions on the ACS Reports
Part  V: Auditing Security
---------------------------------------------------------------------------------

In the field I bumped into a situation which seemed like Catch-22: Audit Collection Services (ACS) had to be implemented. On itself no problem.

One of the requirements started the move towards Catch-22: the database had to keep its data for 60+ days. From the community I got the advise not to keep the data that long in the database since ACS Reports – maintaining older data – would take ages to be rendered. Also would the size of the ACS database grow to an enormous size. And huge databases cost a lot of money and effort to maintain. As stated in this blog posting of Kevin Holman:

Again, no problem. With an archiving solution this issue could be addressed: the ACS database would run with the default grooming settings (14 days) and older data would be archived and accessible for reports. But in this case budget didn’t allow for it.

Hmm. How to solve this? So I started to think and think and finally...

I thought to have found the solution: I would keep the default grooming settings on 14 days on the ACS Database AND would schedule all the available preset ACS Reports to run on a weekly basis and have the results uploaded to a secured file share!

This way I had both of two worlds: a well performing ACS environment with an acceptable ACS database size AND much of the most important collected data would be available in the format of the uploaded reports. Of course, no new queries could be run for data older than 14 days since it was groomed out of the ACS database, but under these circumstances it was the best to achieve.

So far so good. But another requirement introduced Catch-22 full blown: the ACS Reports had to be accessible by the auditors only.

Why? In order for SQL Server Reporting Services (SSRS aka SRS) being able to run scheduled reports, it needs to have the related credentials stored locally on the SSRS server. Otherwise this message will be shown:

But when these credentials are stored locally, every one with local administrator permissions on that server, won’t be challenged for his/her credentials when running ACS Reports, thus every one who is local administrator can run the ACS Reports whether or not they are an auditor!

Look here at SQL Server Central for more on that topic.

And the security where only ACS Auditors are allowed to run the ACS Reports is true requirement. No shortcuts there.

With the help of a much appreciated SQL guru/colleague (thanks so much Mark D. !) the Catch-22 got solved. Not easily but it can be done. So this series will be about how to go about it when ACS has these requirements:

1. ACS Reports much be secured so only a certain group of people can run these reports  and certainly not the Domain Admins.
2. Data must be kept no too long in the ACS database since it introduces issues: bad performance and a huge database.
3. ACS Reports must be scheduled in order to keep some data older than the grooming settings set on the ACS Database.
4. An archiving solution can not be used.

Remark:
In this series I will outline certain needed steps. Reason why I do this is because these steps are described in detail in the Big Orange Book: SCOM Unleashed. Since I am not a copy cat I will refrain my self from describing those details. All I can say here is that when you are really into SCOM/OpsMgr, this book is worth every euro-/dollar cent since it contains very valuable information for any one who runs a SCOM environment.