Our Reporting Team wanted to disable all of the actual subscriptions because our production has to be stopped in case of the Coronavirus Pandemie and reports don’t show realistic data. But they want to be able to activate the same reports again later – and not all reports!

Our first „solution“ to disable all of the reports was to send the reports by email
to „/dev/nul“ 😉
Therefore we changed the SMTP Mailserver in the Reporting Services Configuration. Below is a link to a mini PowerShell SMTP Server „Blackhole“ which we tested on the SSRS. Later I’ve found that there doesn’t need to be a SMTP Server running. Changing the entry works without any problem.

The success lasted only one day.
Then the Reporting Team needed to activate some reports again, so our solution doesn’t work anymore.

A Google Search shows up several PowerShell snippets to disable or enable simple reports via the subscription id or do this for all of the reports (Link in the notes at the end of this article). But there is no solution for a huge number of reports (~600 Reports).
BTW: Enabling and disabling Report Subscriptions only works from version 2016 and beyond!

With a litte help from my friend Claudio @ClaudioESSilva I implemented the idea of storing the subscription IDs in a sql table and using them for disabling and enabling all IDs in a loop.

<# 
Name: SSRS enable disable Reports.ps1
Author: Volker Bachmann
Date: 11.4.2020

:
Disable and Enable Reports or their subscription with a list of SubscriptionIDs

#>

Function LogWrite
{
    # simple function for logging to a file.

    Param ([string]$logstring)
   $Logfile=".\enable_disable_reports.log"

   $logstring += " ["
   $logstring += (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
   $logstring += "]"

   Add-content $Logfile -value $logstring
   
}


# $URI="http://<SSRS Server>/ReportServer/ReportService2010.asmx" 
[int]$counter=0


# ############################################################################
# Disable specific Subscriptions, defined in a referenced SQL Table

<# SQL Table Definition and Load

    CREATE TABLE [dbo].[Subscriptions](
    [id] [INT] IDENTITY(1,1) NOT NULL,
    [subscription_id] [UNIQUEIDENTIFIER] NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO Subscriptions (subscription_id)
    VALUES

    ('D0DD9A1C-2393-4529-9F93-85DEC5FCD42F'),
    ('A023E6B9-943E-4A53-806D-1A05A8FBE0A8'),
    ('F28A93D5-FB56-46BE-A057-5B16945728B6');

#>

[string]$serverName = '<SSRS Server>' # ServerName for the list of subscriptionIDs

# connection to the table of subscription ids
$SubscriptionList = (Invoke-SQLCmd -query "select * from [Subscriptions]" -Server $serverName )

# connection to the Reporting Server with actual credentials
$rs2010 = New-WebServiceProxy -Uri $URI -Namespace SSRS.ReportingService2010 -UseDefaultCredential;  

# create new chapter in the logfile
LogWrite  "############# Start disable ###################"

# loop through all subscription ids of the list and disable them
ForEach ($subscription in $SubscriptionList)  
{  
    # Error-handling with a try/catch 
 
try {
    $rs2010.DisableSubscription($subscription.ItemArray[1]);  
        
        write-host "disabled :"  $subscription.ItemArray[1]

        $output = "disabled : "+  $subscription.ItemArray[1]
        LogWrite  $output
    
        $counter ++ # counting the amount of successful disabled subscriptions

    }
catch {
    write-host "Error during disabling of id :"  $subscription.ItemArray[1]

    $output =  "Error during disabling of id : "+  $subscription.ItemArray[1]
    LogWrite  $output
    }    
}# End ForEach

$output =  "=> Amount of disabled Subscription IDs : " 
$output += $counter 
LogWrite  $output

# Chapter End in Log
LogWrite  "############# End disable  ###################"

This was for disabling the subscription IDs – enabling is nearly the same:

# ###################################################################
# Enable specific Subscriptions, defined in a referenced SQL Table
 
# Definition and load of the table see above.

# ServerName for the list of subscription ids
[string]$serverName = '<SSRS Server>' 

# connection to the table of subscription ids
$SubscriptionList = (Invoke-SQLCmd -query "select * from Subscriptions" -Server $serverName )

# connection to the Reporting Server with actual credentials
$rs2010 = New-WebServiceProxy -Uri $URI -Namespace SSRS.ReportingService2010 -UseDefaultCredential;  

# create new chapter in the logfile
Log-Write  "############# Start enable ###################"

# loop through all subscription ids of the list an enable them
ForEach ($subscription in $SubscriptionList)  
{  
    #try/Catch see above
try {
    $rs2010.EnableSubscription($subscription.ItemArray[1]);  

        write-host "enabled :"  $subscription.ItemArray[1]

        $output = "enabled : "+  $subscription.ItemArray[1]
        LogWrite  $output
    
        $counter ++ # count the amount of successful subscriptions
    }
    catch
    {
    write-host "Error enabling id :"  $subscription.ItemArray[1]

    $output = "Error enabling id : "+  $subscription.ItemArray[1]
    LogWrite  $output
    }
} # End ForEach

$output =  "=> Amount of enabled Subscription IDs : "
$output += $counter 
LogWrite $output

# End in Log
LogWrite  "############# End enable ###################"

These two snippets work for me.
Disabling Subscriptions brings up some Errors for me when:

• Owner of the Subscription is not valid any more -> change Owner
• Subscription is not a report – it was a Power BI or other subscription
• In the subscription was an End date set – than the disable didn’t work for me.

Any comments, additions?

Thanks for Reading,
Volker

Notes: Links to documentation from Microsoft:

• https://docs.microsoft.com/en-US/sql/reporting-services/subscriptions/disable-or-pause-report-and-subscription-processing?view=sql-server-ver15 
• https://docs.microsoft.com/en-US/sql/reporting-services/subscriptions/manage-subscription-owners-and-run-subscription-powershell?view=sql-server-2017#bkmk_change_all

SMTP Blackhole (German only): https://www.msxfaq.de/tools/sonstige/smtp_blackhole.htm