Sifiso Ndlovu

How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell

January 23, 2017 by

In the article Report Subscription Changes in SQL Server Reporting Services 2016, I covered several changes to standard and data-driven subscriptions that were introduced in the release of SQL Server 2016. However all of those changes related to administering report subscriptions using a GUI (i.e. Report Manager Portal, SSRS Configuration Manager). The release of SQL Server Core, SQL Server on Linux and enhancements on Windows PowerShell, reinforces the growing shift by Microsoft to have SQL Server professionals adopt scripting as one of the ways to carry out their daily tasks. In this article we continue to explore SQL Server Reporting Services (SSRS) subscription feature by discussing some of the Windows PowerShell commands that can be utilised to administer report subscriptions.

Basic PowerShell Commands

Although latest releases of SQL Server usually introduce new T-SQL functions, they still support basic built-in functions that can be used across all versions of SQL Server i.e. SUBSTRING, REPLACE. Not surprisingly, there are PowerShell commands that can be executed against most versions of Reporting Services. Examples of such basic reporting services functionalities that can be administered using PowerShell includes the following:

  • Providing a list of subscriptions
  • Change report subscription ownership
  • Delete a report subscription

The ability to administer report subscriptions using PowerShell is made possible by the Report Server Web service. The Report Server Web service has several endpoints for administering a report server instance such as ReportService2005, ReportService2006 and ReportService2010. For the purposes of this discussion, we will focus on the ReportService2010. Figure 1 shows a preview of the XML file returned when you access the endpoint from a browser.


Figure 1: ReportService2010 Endpoint

The ReportService2010 endpoint contains multiple members such as, properties, events and methods that can be queried using PowerShell. Script 1 shows how we can get a list of member details using the ReportService2010. The script is formatted and executed using Windows PowerShell Integrated Scripting Environment (ISE).

Script 1: Listing of members belonging to ReportService2010 Endpoint

1. List My Subscriptions in Windows PowerShell

One of the PowerShell commands that you can run against almost any version of SSRS is to list subscriptions that your account owns. This is similar to clicking My Subscriptions link from the Report Manager page. The PowerShell script used to return such a list is shown in Script 2 and its output (shown in Figure 2) is very similar to My Subscriptions list returned using the GUI – shown in Figure 3.

Script 2: List My Subscriptions


Figure 2


Figure 3

2. List All Subscriptions in Windows PowerShell

One of the security flows in reporting services is that subscriptions can continue to be owned by a domain account that has been disabled. Therefore as a Report Server administrator, you don’t want to be limited to just a list of subscriptions that your account own instead you may want to retrieve a subscription list for all accounts. Although, the GUI is unable to readily retrieve such a complete list, ReportService2010 endpoint has a method (ListSubscriptions) which can be used in PowerShell to retrieve all subscriptions as shown in Script 3.

Script 3: List All Subscriptions

Whilst Figure 2 showed an output of subscriptions owned by the Administrator account, the output of Script 3 (shown in Figure 4), further retrieved subscriptions owned by account name WIN-9P7O5S87UBG\Sifiso.


Figure 4

Depending on the number of subscriptions within a given report server instance, the output of Script 3 can potentially be a long list. You can reduce the returned output by retrieving subscriptions of a given path. Up to this point, the script examples demonstrated have been looking at the entire root folder (denoted as “/”), Script 4 shows the changes that you have to make in order to retrieve subscriptions for reports located in Folder1.

Script 4: List All Subscriptions on Folder1

Figure 5 shows a reduced list of subscriptions belonging to reports stored on within Folder1.


Figure 5

3. Change Subscription Owner in Windows PowerShell

One of the things we may want to do as a result of going through an output of an All Subscriptions list would be to change ownership for subscriptions that belong to disabled domain accounts. This can be done using the method ChangeSubscriptionOwner which accepts two parameters, namely, the report subscription ID as well as the name of the account the subscription ownership is being changed to.

Figure 6 identifies a report subscription whose ownership we would like to change from WIN-9P7O5S87UBG\Administrator to WIN-9P7O5S87UBG\Sifiso


Figure 6

The PowerShell script for changing report subscription ownership is shown in Script 5 and its output (shown in Figure 7) indicates that the subscription ownership was successfully changed.

Script 5: Change subscription ownership


Figure 7

4. Delete Subscriptions in Windows PowerShell

In addition to switching account ownership, you also have an option to delete a report subscription without having to launch and navigate through Report Manager links. This can be done in PowerShell using the DeleteSubscription method. The method simply requires a report subscription ID as shown in Script 6.

Script 6: Delete report subscription

New PowerShell Commands in SSRS 2016

The release of SSRS 2016 introduced new methods into the ReportService2010 endpoint. One of the checks you may want to conduct as a report server administrator could be to use PowerShell to identify report subscriptions that are using the new File Share Account feature in your environment. Another feature introduced in SSRS 2016 – disabling and enabling of report subscriptions – could also be administered in PowerShell.

1. File Share Account

Unlike previous sections, the ReportService2010 endpoint doesn’t contain a dedicated method for identifying subscriptions configured to use a File Share Account instead we make use of the ListSubscriptions method. File Share Account can only be configured against Windows File Share delivery method, thus, the first part of writing a PowerShell script that will list subscriptions using File Share Account is to firstly identify those subscriptions that are using Windows File Share delivery method. Script 7 identifies such subscriptions by filtering DeveliverySettings.Extension on “Report Server FileShare”.

Script 7

For the purposes of this discussion, I setup sample subscriptions with keyword FS to indicate that they are using Windows File Share delivery method. Subsequently, the output of Script 7 (shown in Figure 8) shows such list of report subscriptions.


Figure 8: Subscriptions using Windows Delivery method

Now that we have identified all subscriptions configured to use Windows File Share delivery method, we can apply the second part of the script which uses the filter method to iterate through subscriptions properties to identify those subscriptions configured to use File Share Account. The complete script is shown in Script 8.

Script 8

The output of Script 8 is shown in Figure 9 which contains a further reduced list from the one displayed in Figure 8.


Figure 9

2. Enable and disable subscriptions using Windows PowerShell

The enabling and disabling of subscriptions in Windows PowerShell is similar to doing a delete in that a single report subscription ID parameter is passed into a method. The scripts for enabling and disabling subscriptions is shown in Script 9 and 10, respectively.

Script 9

Script 10

References

Sifiso Ndlovu
Latest posts by Sifiso Ndlovu (see all)

Related posts:

  1. Report Subscription Changes in SQL Server Reporting Services 2016
  2. SSRS Report Builder introduction and tutorial
  3. SSRS Failed Subscription Alerting
  4. Migrating SSRS content with PowerShell
  5. Enhancing Customer Experiences with Subscriptions in SSRS
168 Views