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
- 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.
- 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.
- Execution account. Windows user account with read only access to all reports. The Execution account is specified in the Reporting Services Configuration Manager
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
- Right click the report and select New Subscription
- Type the E-mail information and click Next. Notice that you can provide a link to the report or include the report.
- Select the subscription schedule and click Next. In my example the mail will be send every second Monday of the week.
- Click Next
- Click Close
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.
- Check the status of the report on the report server. Open SRSServer/Reports. Open the report and select the subscriptions tab.
- The daily log file (.\Reporting Services\LogFiles\ReportServerService_date) generated on the SRS server.
Thanks for this great post, it was very useful for me…
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..
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