In How to create SSRS Subscriptions for ConfigMgr - Part 1, we discussed background information regarding SSRS subscriptions and necessary system prerequisites.
For this article, we'll cover permissions required for report subscriptions, how to properly configure reports for subscriptions to be delivered via e-mail, or to a file share.
As mentioned in the previous article, SQL Server subscriptions require the SQL Server Agent to be running, in order to schedule the subscriptions. Schedules can be created as shared or individual schedules. Shared schedules can be re-used for multiple reports, or created per report as an individual schedule.
Report Subscription Permissions
Schedules are created based on membership in specific SQL Server roles. The most common roles used will be the System Administrator and Content Manager roles, in that, either role can manage all available subscriptions. By using the other predefined roles defined below, it is possible to limit specific report subscriptions to specific users.
If you are using predefined roles, users who are Content Managers and System Administrators can create and manage any schedule. If you use custom role assignments, the role assignment must include tasks that support scheduled operations. (From Books online)
| To do this | Include this task | Predefined roles |
| Create, modify, or delete shared schedules | Manage shared schedules | System Administrator |
| Select shared schedules | View shared schedules | System User |
| Create, modify, or delete report-specific schedules in a user-defined subscription | Manage individual subscriptions | Browser, Report Builder, My Reports, Content Manager |
| Create, modify, or delete report-specific schedules for all other scheduled operations | Manage report history, manage all subscriptions, manage reports | Content Manager |
For more information on SQL Server role based security, please see Role Based Access Security in SQL Server.
Creating the report subscription
To begin, you need the following prerequisites:
- You must have view access to the report
- You are a member of one or more of the appropriate RBAC roles listed above
- The report must have stored credentials, or require no credentials at time of execution. Reports that use impersonated or delegated credentials to connect to an external data source cannot be subscribed.
How to: Subscribe to a Report (Management Studio) (adapted From Books online)
This topic provides instructions for creating an e-mail subscription and a file share delivery subscription.
To subscribe to a report, the report data source (DSN) must be configured to use stored credentials or no credentials.
[Note] My recommendation is the creation of a new data source that is used for subscriptions, that will use a stored account to execute the subscribed report. Use an account that has the minimum rights/ability to invoke reports, do not use an administrator account for this operation!
Your defined DSN should appear as the screen shot below. The connection string will vary depending on your database name, etc. The important part is the section that includes 'Credentials stored securely on the report server' and include checkbox 'Use as windows credentials when connecting to the data source'
When you create your report subscription, simply point your report DSN selection to your DSN with stored credentials.
For completeness in creating this article, it is possible to define and store an account/password combination for each report.
[Update] myITForum member Edward Woo provided the following detail, regarding using a dedicated application account for report subscriptions.
I had to set the domain user account with Allow log on locally and Log on as a Batch Job rights and then assigned it db_datareader rights to the SCCM database. Then I changed the SCCM SQL reporting services configuration from Windows Integrated Security to Credentials stored securely in the report server along with Use as Windows credentials when connecting to the data source.
By changing the Credentials stored… option, this changed the security settings for the long guid [the default ConfigMgr DSN] that you referred to.
Ran a quick e-mail subscription test and it worked!
Subscription Options
For e-mail delivery, the report server must be configured for an SMTP gateway connection before you create the subscription. To use file share delivery, you must have target folder already defined.
To create an e-mail subscription
-
In Object Explorer, navigate to the report you want to subscribe to. Expand the report item.
-
Right-click the Subscriptions folder, and click New Subscription. The Properties page of the Report Subscription dialog box displays.
-
For Notify by, click Report Server E-Mail.
-
In the To text box, type the e-mail address to deliver the report to. To specify multiple e-mail addresses, separate them with a semicolon (;). You can type additional e-mail addresses in Cc, Bcc, and Reply-To text boxes.
.gif)
Note:
This requires that you have permission to manage all subscriptions.
-
Choose report delivery options as follows:
- Select Include Report to embed or attach a copy of the report. The rendering format you select in a later step determines how the report is included.
- Select Include Link to include a URL link to the report in the body of the e-mail message.
Note:
If you clear these check boxes, only the text in the subject line is sent.
-
Choose a rendering format from the Render Format list box. This option is available if you include the report with the e-mail.
- To embed the report in the body of the e-mail, select Web archive. Do not choose this option if you think the report size will exceed the maximum limit defined for your e-mail system.
- All other rendering formats send the report as an attachment.
-
Select an importance level for the e-mail message from the Priority list box. These settings are obtained from your e-mail server.
-
Optionally type comments to include in the e-mail message in the Comment text box.
-
Click Scheduling to specify when you want the report delivered.
-
Specify the schedule or event used to trigger report processing.
- Choose Deliver when the report is updated on the report server if you want to deliver a report snapshot each time it is refreshed. This option is available only for snapshots that retrieve data at scheduled intervals.
- Choose On a shared schedule to select a predefined schedule. This option is only available if shared schedules are already defined.
- Choose On a custom schedule to pick the dates and times when the subscription is processed. Click the Set Schedule button to create the schedule.
-
For parameterized reports, specify parameters to use for the report for this subscription. The parameters that you specify can be different from those used to run the report on demand or in other scheduled operations.
-
Click OK.
To create a file share subscription
-
In Object Explorer, navigate to the report you want to subscribe to. Expand the report item.
-
Right-click the Subscriptions folder and click New Subscription. The Properties page of the Report Subscription dialog box displays.
-
Select Report Server File Share from the Notify by list box.
-
Click in the text box after File name, and type a file name for the report.
-
If you want the file extension automatically appended to the report file name, select True from the File Extension list box. Otherwise, select False.
-
In the Path text box, type a Universal Naming Convention (UNC) path to an existing folder where you want to deliver the reports (for example, \\<servername>\<myreports>). Include double backslash characters at the beginning of the path. Do not specify a trailing backslash.
-
Select a format from the Render Format list box. Choose a format that corresponds to the desktop application that will be used to open the report. Avoid formats that do not render a report in a single stream or that introduce interactivity that cannot be supported in a static file (that is, HTML 3.2, HTML 4.0, or HTML with Office Web Components).
-
In the User name and Password text boxes, specify the credentials required to access the file share, using the format <domain>\<user name> for the user name.
-
Specify overwrite options. If you click Do not overwrite the file if a previous version exists, the delivery will not occur if an existing file is detected. If you click AutoIncrement, the report server appends a number to the file name to distinguish it from existing files of the same name.
-
Click Scheduling to specify when you want the report delivered.
-
Specify the schedule or event used to trigger report processing.
- Choose Deliver when the report is updated on the report server if you want to deliver a report snapshot each time it is refreshed. This option is available only for snapshots that retrieve data at scheduled intervals.
- Choose On a shared schedule to select a predefined schedule. This option is only available if shared schedules are already defined.
- Choose On a custom schedule to pick the dates and times when the subscription is processed. Click the Set Schedule button to create the schedule.
-
For parameterized reports, specify parameters to use for the report for this subscription. The parameters can be different from those used to run the report on demand or in other scheduled operations.
-
Click OK.
The report is delivered as a static file. If the report includes interactive features (for example, links to additional rows and columns), those features are not available.
For more information, see Configuring a Report Server for E-Mail Delivery and How to: Create a Shared Folder for Report Server File Share Delivery.