Configuring SQL Reportings Services mail subscriptions with Configuration Manager

In this post I’ll describe how you configure mail subscriptions with Configuration Manager 2007 R2. I assume that SQL Reporting Services is installed and configured on a remote database. I also assume that the Reporting Services Site system is configured in the Configuration Manager Administrator console.

Prerequisites

  • SQL Reporting Services requirements
    • Execution account. Windows user account with read only access to all reports. The Execution account is specified in the Reporting Services Configuration Manager
      image
    • SMTP configured in the Reporting Services Configuration Manager.
    • You also need to make sure that the SMTP port is enabled for relaying and anonymous access. If not you might end up with this error: “Failure sending mail: The server rejected the sender address. The server response was: 554 5.1.0 Sender denied Mail will not be resent” To modify the SMTP port open “%Program Files%\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config” Search for SMTPserverPort and add the port. In my lab the port is 8025.

      image 

    • Configure the option Credentials stored securely in the report server on the Data Source Authentication tab of the Report Server Properties dialog box. For testing purposes you can also configure credentials are not required.

      image 

Configuring the mail subscription in Configuration Manager

  • Open the Config Mgr. console and navigate to Computer Management, Reporting, Reporting Services, Reporting Services Server, All Reports

    image

  • Right click the report and select New Subscription

    image

  • Type the E-mail information and click Next. Notice that you can provide a link to the report or include the report.

    image

  • Select the subscription schedule and click Next. In my example the mail will be send every second Monday of the week.

    image

  • Click Next

    image 

  • Click Close

    image

Monitor that the subscribtion works

  • Check your mail that would be the easiest way to check it 🙂
  • If it doesn’t work I normally check:
    • The daily log file (.\Reporting Services\LogFiles\ReportServerService_date) generated on the SRS server.

      image

    • Check the status of the report on the report server. Open SRSServer/Reports. Open the report and select the subscriptions tab.

      image

About the Author:

Kent Agerlund
Microsoft Regional Director, Enterprise Mobility MVP. Microsoft Certified Trainer and Principal consultant. I have been working with Enterprise client management since 1992. Co-founder of System Center User Group Denmark in 2009. Certified MCITP: Enterprise Administrator, MCSA+Messaing, and much more. Member of: Microsoft Denmark System Center Partner Expert Team The Danish Technet Influencers program System Center Influencers Program.

3 Comments

  1. Amber March 31, 2011 at 11:46 - Reply

    Thanks for this great post, it was very useful for me…

  2. ucrajee June 4, 2012 at 10:54 - Reply

    Hi Kent,

    Is there a way to find all SCCM SSRS scheduled reports details such as (Report Name, schedule time, mail IDs configured, etc.)..
    Pls help as we have many reports scheduled and manual work will take very long time..

    • Benedict September 12, 2017 at 22:18 - Reply

      I Use this query and modify it to your needs on the ReportServer DB :

      SELECT USR.UserName AS SubscriptionOwner, SUB.ModifiedDate, SUB.Description, SUB.EventType, SUB.DeliveryExtension, SUB.LastStatus, SUB.LastRunTime, SCH.NextRunTime, SCH.Name AS ScheduleName,
      CAT.Path AS ReportPath, CAT.Description AS ReportDescription, SUB.LastStatus AS Expr1
      FROM Subscriptions AS SUB INNER JOIN
      Users AS USR ON SUB.OwnerID = USR.UserID INNER JOIN
      Catalog AS CAT ON SUB.Report_OID = CAT.ItemID INNER JOIN
      ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID AND SUB.SubscriptionID = RS.SubscriptionID INNER JOIN
      Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID
      ORDER BY SubscriptionOwner, ReportPath

Leave A Comment