Catch-22: Securing ACS Reports AND scheduling them. Part IV: Setting the subscriptions on the ACS Reports

--------------------------------------------------------------------------------- 
Postings in the same series:
Part   I: How Catch-22 was born…
Part  II: What do we need?
Part III: Setting Security
Part  V: Auditing Security
---------------------------------------------------------------------------------

This posting is a continuation of the previous posting in this series, where the security was set on the SQL Server hosting the ACS Database which is also the SQL Server Reporting Services (SSRS aka SRS) for the ACS Reports.

With the security model in place the subscriptions can be set on the ACS Reports. These tasks must be completed:

1. Creating a secure file share where the scheduled reports can be uploaded to.
2. Changing the security settings on the DataSource used by the ACS Reports so subscriptions can be created.
3. Setting the subscriptions on the ACS Reports.
4. Testing the subscriptions set on the ACS Reports.

#1: Creating a secure file share
I won’t go in detail about creating a secure file share since that is basic knowledge & experience. Just be sure this share can be accessed by the Auditors (local group ACS Auditors) and the service account used by the backup software in order to perform its daily/weekly duties. Also enable some auditing on that share in order to keep a watchful eye on it.

But even that can be circumvented. However, there comes a certain point that only hiring loads of north Korean soldiers will lock everything down. The question is: ‘How Far Do You Want To Go?’. Not too far I hope…

#2: Changing the security settings on the data source of the ACS Reports

1. Create a local account. Give this account a logical name like Planned_Reports_ACS
   
   
2. Add this account to the local group ACS Auditors
   
   
3. Open  Report Manager by opening this address in IE: http://localhost/Reports.
   Be sure to start IE with elevated permissions otherwise you bump into this issue.
   
   
4. Click on Audit Reports > Show Details. Click on DB Audit DataSource:
   
   
   
5. Select the option Credentials stored securely in the report server. Use the account and password created in Step 1 (Planned_Reports_ACS) and select Use as Windows credentials when connection to the data source. Click Apply.
   
   
6. Close IE.

#3: Setting the subscriptions on the ACS Reports

1. Open  Report Manager by opening this address in IE: http://localhost/Reports.
   Be sure to start IE with elevated permissions otherwise you bump into this issue.
   
   
2. Click on Audit Reports and select the first report on top (Access Violation – Account Locked). The report will be rendered and presented. This is good since it shows the credentials are in place and functional.
   
   
3. Go to the fourth Tab, Subscriptions > New Subscriptions. A form will be shown.
   
   
   
4. Fill the form out like this:
   - Delivered by: Windows File Share (email will only be available when SSRS has been configured to use it, like posted here, Step 5)
   - File Name: Already provisioned. Select the option Add a file extension when the file is created.
   - Path: unc path referring to the file share created at #1
   - Render Format: PDF will do
   - User Name and Password: Account and Password created in #2
   - Overwrite options: Increment file names as newer versions are added
    
   
5. Under the section Subscription Processing Options hit the button Select Schedule.
   - Select Day > Sunday > 06:00 AM (for instance). DO NOT SELECT 02:00 AM SINCE THAT IS THE DEFAULT MAINTENANCE TIME!
   
   
   Select a start date and hit OK.
   
   
   
6. Now you are back in the screen/form as described in Step 4. Under the header Report Parameter Values needs some special attention. For the Start Date and End Date deselect the option Use Default first and than select NULL.
      
   
   This needs some explanation. In SQL NULL is NOT zero. NULL means unknown data. SQL 2008 Server Books Online for SSRS writes this about using NULL in publishing reports (found here):
   
   
   What happens is that all available data will be used in the scheduled ACS Report. So when the default retention setting is being used in ACS (14 days) and this report runs every 12 days, every report will have an overlap of 2 days. Of course you could set the days in Step 5 at 13 or even 14 but I rather have some overlap then a (slight) possibility missing out on some data.
   
   
7. Click OK.

#4: Testing the subscriptions set on the ACS Reports

How do you know whether the subscription works? By testing it! Duh! So go back to Step 5 of #3 and change the setting in such a manner that the Report is scheduled to be rendered within a few minutes. Check out the file share in order to see the report is created AND uploaded. Also open it in a PDF viewer to see whether it contains valid AND readable data.

Also SSRS itself for the scheduled report will show information in the tab Subscription for the Scheduled Report.

When all is in place and functional the other ACS Reports can be scheduled as well. Be aware that some ACS Reports need some additional parameters in order to run. So some of those reports can not be scheduled or need some more attention before being scheduled.