A recent customer wanted an automated SSRS Report Email Subscription anytime a new deployment was created. I’d been pondering how do accomplish that for some time, but finally got a catalyst and a bit of time.
I found a PowerShell script by George Walkey to get started with which is hosted on his GitHub repo at https://github.com/gwalkey/SSRS_Subscriptions/blob/master/New-SSRS_Subscription.ps1. The script needs to be tweaked a bit to accept all of the required parameters for the various deployments (Application, Package/Program, Task Sequence, Software Update Group, Compliance Baseline), but the core functionality is there.
After tweaking the script I created a ConfigMgr Status Filter Rule to run the script with the right parameters. The script runs; however, it throws an error
Exception: Exception calling “CreateSubscription” with “6” argument(s): “System.Web.Services.Protocols.SoapException: The DefaultValue expression for the report parameter ‘UserTokenSIDs’ contains an error: Server names cannot contain a space character.
Attempting to hard code the UserTokenSIDs to “disable” or “disabled” only produced another error:
Exception: Exception calling “CreateSubscription” with “6” argument(s): “System.Web.Services.Protocols.SoapException: The report parameter ‘UserTokenSIDs’ is read-only and cannot be modified. —> Microsoft.ReportingServices.Diagnostics.Utilities.ReadOnlyReportParameterException: The report parameter ‘UserTokenSIDs’ is read-only and cannot be modified.
So the issue is that the script is executed as the local SYSTEM account by the Status Filter Rule and SYSTEM doesn’t have any RBAC credentials. I considered trying to grant it some rights, but decided this wouldn’t be a good idea.
I could only find one reference on BinGoogle related to the situation and comments are closed. https://www.reddit.com/r/SCCM/comments/4cvb7j/status_filter_rule_run_as_account/
So, how to get the SSRS subscription created with a user that has proper RBAC? RunAs…
There are a few ways to accomplish this by having the primary script run the SSRS CreateSubscription function with alternate credentials.
- Have the Status Filter Rule call a bootstrap script. The bootstrap script saves the parameters to an XML then calls a Scheduled Task that runs a valid user which consumes the XML to create the subscription.
- Using a Scheduled Task with saved credentials, trigger the PowerShell script on the proper Application / Source / Event IDs. The script will needed to query ConfigMgr to find the associated Audit Status Message then parse out all of the required parameters.
- Use Lee Holmes method to save PowerShell credentials to disk and have the bootstrap script utilize the credentials to call the CreateSubscription script/function. The credentials will need to be created while running PowerShell as the SYSTEM account. PSEXEC -S -D -I PowerShell.exe will get that part going.
- Dig into the CreateSubscription function in SSRS to see if it will accept alternate credentials. If so, then no bootstrap script will be required when using Lee Holmes’ method.
I chose option 1 for the moment and my rough code is working for Packages.
I hope to post the full solution soon including any script(s) for each deployment type.