Easy setup of SRS with Config Manager R2
I just completed a screen cast video for my customers on configuring SRS with Config Manager. While I can’t share the video with you I can post the document that contains the steps I used. This is all information that is on the Microsoft TechNet site but is kind of hard to find and understand. I took it and compiled it into a more readable format and included it in a complete R2 document that I wrote last fall. Let me know if this helps anyone out there:
SQL Reporting Services Reporting
The SQL Server Reporting services product is a reporting platform that provides advanced reporting functionality currently not available in the existing Config Manager reporting solution. Building the Config Manager reporting solution on this platform enables advanced reporting scenarios for the Config Manager administrator and enables future extensions and improvements to further enhance the Config Manager product from a reporting perspective.
Config Manager 2007 R2 with SQL Reporting Services Integration enables rich reporting and advanced information gathering and delivery for current and future configuration management releases, by providing rich reports highlighting the strengths of the product to executive level audiences and enable the Config Manager administrator to perform their job more effectively.
SQL Reporting Services Integration is being added as a reporting solution for Configuration Manager 2007 R2 to accomplish the following goals:
- Provide best in class reporting capability by integrating SQL Reporting Services, with the leading change and configuration management product: SCCM 2007
- Enable Ad-hoc reporting - Make it easy for both SCCM administrators, and non administrators to find the information they need to make the right decisions for their business
- Integrate core SRS functionality without requiring significant changes to the Configuration Manager product
- Assist customers by migrating both existing reports and customer generated custom reports to the SRS solution
- Integrate Reporting Services side by side with the existing Configuration Manager reporting solution, so customers can slowly migrate to the SRS solution
- Implement a new Reporting Services Point that can be added as a site role to an existing or new SCCM Site System that has SQL Reporting Services already installed
- Support alternative databases as the reporting database, such as a replicated or backup database
- Provide ability to convert out of the box classic SCCM reports and to convert customer- generated reports to SQL Server reports.
- Allow custom report creation via a report authoring wizard.
- Enable report browsing and viewing via the SRS Report Viewer.
- Take advantage of rendering in all supported SRS formats, report caching, and subscriptions.
Planning for SQL Reporting Services
The following considerations should be taken into account when configuring the Configuration Manager 2007 R2 SQL Reporting Services feature.
Performance
Location of SQL Reporting Services Installation - Configuration Manager 2007 R2 supports locating the SQL reporting server on the site server or a remote computer. However, for performance reasons, you should locate the SQL reporting server and the reporting services point on a remote site system.
Security
Unattended Report Processing Account - If you intend to allow report subscriptions, you must configure an execution account to allow these to run unattended. This account must be a Windows user account. If you configure this account with a password expiry date, or if you change its information in Active Directory, you must also update this information in Reporting Services. You should set read-only permissions to reports for this account.
Credentials Options when Configuring the Connection to the Configuration Manager Database - Use the option, Credentials are not required in the Report Server Properties: Data Source Authentication Tab for testing purposes only. This setting is not recommended for a production environment.
Best Practices for SQL Reporting Services
Optimize SQL Reporting Services Queries - Optimize your report queries. Usually, the bulk of report execution time is spent executing queries and retrieving results. If you are using SQL Server, tools such as Query Analyzer and Profiler can help you optimize queries.
Report Subscription Scheduling - Whenever possible, schedule report subscription processing to run outside of normal office hours. This will reduce load on the reporting server and improve availability for ad-hoc report requests.
Prerequisites for SQL Reporting Services
SQL Reporting Services in Configuration Manager 2007 R2 has external dependencies and dependencies within the product.
The following table describes the external dependencies for SQL Reporting Services running in Configuration Manager 2007.
Dependency |
More Information |
|
A SQL Reporting Services reporting server must be installed and configured in the site hierarchy. |
Before you can use SQL Reporting Services integration in Configuration Manager 2007 R2, you must configure reporting services on a SQL Server installation in your network. For more information about installing and configuring SQL Reporting Services, visit the SQL Reporting Services Web site at https://go.microsoft.com/fwlink/?LinkId=111840. |
|
SQL Reporting Services requires Internet Information Services (IIS) 6.0 or later for the Reporting Services Web Site. |
For more information about installing Internet Information Services, see your Windows Server documentation.
|
The following table describes the dependencies within Configuration Manager 2007 for running SQL Reporting Services.
Dependency |
More Information |
The site server must be running Configuration Manager 2007 R2. |
SQL Reporting Services requires Configuration Manager 2007 R2. |
Reporting services point site system role. |
The reporting services point site system role must be configured before you can use SQL Reporting Services. |
Configuring Microsoft SQL Server for SQL Reporting Services
Before you can use SQL Reporting Services integration in Configuration Manager 2007 R2, you must configure reporting services on a SQL Server installation in your network. The following steps will allow you to install the SRS Component of SQL:
1. From the installation media you used to install SQL Server 2005, run setup.exe to launch the SQL Server Setup Wizard.
2. Accept the end user license agreement, then click Next.
3. Review the Installing Prerequisites page, and click Next.
4. After SQL Server setup performs a configuration check, the Welcome page of the SQL Server Installation Wizard is displayed. Click Next.
5. Review the results of the system configuration check to identify any problems that may prevent the configuration of SQL Server from completing successfully. Click Next.
Note: |
If you receive an Edition Change Check warning message, you must reapply any previously applied SQL Server Service Packs after you have finished configuring the software. |
6. On the Registration Information page, enter your name, company name and product key, then click Next.
7. On the Components to Install page, make sure that Reporting Services is selected, then click Next.
8. On the Instance Name page, specify a name for the instance of SQL Server you are upgrading. If you are upgrading the default instance of SQL Server, select the Default instance check box, then click Next.
9. On the Service Account page, specify the account details that the reporting services service will use to log in. Check the Reporting Services check box to start the reporting services service after setup completes. Click Next to continue.
10. On the Report Server Installation Options page, verify that the option Install but do not configure the server is selected, then click Next.
11. On the Error and Usage Report Settings page, select whether you want to inform Microsoft of any error and usage data for SQL Server, then click Next.
12. On the Ready to Install page, review the information shown, then click Install.
13. After installation is complete, click Next.
14. On the Completing Microsoft SQL Server 2005 Setup page, review any information shown, and click Finish to close the wizard.
15. If you need to reapply the Service Pack, please do so at this time before moving on to the next section.
Configuring SQL Reporting Services in SQL Server 2005
1. From the Windows Start menu, navigate to Microsoft SQL Server 2005 / Configuration Tools, then click Reporting Services Configuration.
2. In the Report Server Installation Instance Selection dialog box, specify the name of the server hosting SQL Reporting Services, select the database instance from the drop-down menu, and then click Connect.
3. In Reporting Services Configuration Manager, click Server Status. Verify that Service Status is set to Running. If it is not, click Start, and then click Apply.
4. Click Report Server Virtual Directory, and then click New to specify a new virtual directory.
5. In the Create a New Virtual Directory dialog box, select Default Web Site from the Website drop-down list, and specify the name you want to call the virtual directory created by Reporting Services (or use the default ReportServer), then click OK.
6. Ensure that Apply default settings is selected, then click Apply.
7. Click Report Manager Virtual Directory, and then click New to specify a new virtual directory.
8. In the Create a New Virtual Directory dialog box, select Default Web Site from the Website drop-down list, and specify the name you want to call the Report Manager virtual directory created by Reporting Services (or use the default Reports), and then click OK.
9. Ensure that Apply default settings is selected, then click Apply.
10. Click Web Service Identity. If you do not want to change the defaults in this section, click Apply.
11. Click Database Setup, select a server from the Server name drop-down list, and then click Connect.
12. In the SQL Server Connection Dialog dialog box, specify the SQL Server name and connection details for the Reporting Services server, then click OK.
13. On the Database Connection page of Configure Report Server, click New.
14. In the SQL Server Connection Dialog dialog box, specify a name for the new Reporting Services database (or use the default, ReportServer), then click OK.
15. Click Apply to complete configuration of the new database.
16. Click Email Settings. Specify the following information:
Note: |
If you do not want to use report subscriptions, you do not need to complete this step. |
Sender Address: Specifies the email address that will appear as the sender address of any email sent from SQL Reporting Services. The account specified must have sufficient permissions to send email from the specified SMTP server.
Current Delivery Method: Specifies that the delivery method used to send emails will be via. SMTP server. This is the only delivery method you can configure through reporting services configuration. For details about configuring other delivery methods, see the SQL Reporting Services documentation.
SMTP Server: Specify the name of the SMTP server to use when sending reports. This can be a local or remote server.
17. Click Apply.
18. Click Execution Account. Specify the domain\username and password of a Windows user account that will be used to run unattended reports, for example those used by subscriptions. Click Apply.
Note: |
If you do not want to use report subscriptions, you do not need to complete this step. |
19. Click Apply, and then click OK.
20. Click Exit to close Reporting Services Configuration Manager.
Creating a Reporting Services Point for SQL Reporting Services
Before you can use SQL Reporting Services in Configuration Manager 2007, you must configure a reporting services point. The reporting services point is a site system role that must be configured on a server running Microsoft SQL Server with the Reporting Services component installed.
Use the following procedure to create a reporting services point on a Configuration Manager 2007 site system computer.
1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Site Management / <site code> - <site name> / Site Settings / Site Systems.
2. Determine whether to create a new site system or add the reporting services point site role to an existing site system, and then follow the associated step.
To create a new site system and add the reporting services point role:
Right-click Site Systems, click New, and then click Server.
On the General page of the New Site Role Wizard, specify the name of the server that will be used to host the reporting services point.
To add the reporting services point role to an existing site system:
? Right-click the site system name, and click New Roles.
3. On the General page of the New Site Role Wizard, configure the general settings for this site system, then click Next. Click Next.
4. On the System Role Selection page of the wizard, select Reporting Services point, then click Next.
5. On the Reporting Services Point page, specify the folder that will be created on the report server to contain the SQL Reporting Services reports used in Configuration Manager and then click Next.
Note: |
The folder will not be created on the report server until you create reports or copy reports into SQL Reporting Services. |
6. Review the information shown on the Summary page, then click Next.
7. Click Close to exit the wizard.
Configuring Properties for the Reporting Services Point
Before you can begin to generate reports in the Configuration Manager 2007 R2 console using SQL Reporting Services, you must configure properties for the reporting services point.
Use the following procedure to configure the reporting services point.
1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services, and expand the node.
2. Right-click the reporting services point server you want to configure, then click Properties.
3. On the General tab of the Report Server Properties dialog box, review information about the currently selected report server.
Note: |
Information about the report folder will only be shown after you have created a new report or copied existing reports to this report server. |
4. On the Data Source Settings tab of the Report Server Properties dialog box, specify the Configuration Manager 2007 database server and database name to be used as the data source for SQL Reporting Services reports. Click the test button to verify that you have correct data entered.
5. On the Data Source Authentication tab of the Report Server Properties dialog box, specify the credentials used to access reports on the report server.
6. On the Data Source Security tab of the Report Server Properties dialog box, specify permissions for the users who have access to the data source specified in the Data Source Settings tab.
7. On the Security tab of the Report Server Properties dialog box, specify the users who have access to the selected report server.
8. Click OK to close the Report Server Properties dialog box.
Copying standard Configuration Manager reports to Reporting Services
In Configuration Manager 2007 R2, standard reports can be copied to a folder on a Reporting Services server by using the Copy Reports to Reporting Services Wizard. Use the following procedure to copy existing Configuration Manager reports to Reporting Services.
Use the following steps to copy standard Configuration Manager reports to Reporting Services.
1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services Reporting.
2. Right-click the Reporting Services server that the standard reports will be copied to, then click Copy Reports to Reporting Services.
3. On the Data Source Settings page of the Copy Reports Wizard, specify the Configuration Manager 2007 database server and database name from which to copy the reports.
4. Click Next.
5. On the Data Source Authentication page of the Copy Reports Wizard, choose the authentication method required to run the copied reports on the reporting point server.
6. Click Next.
7. On the Select Reports page of the Copy Reports Wizard, select the reports you want to copy to the reporting services point. Select Overwrite existing reports if you want to overwrite any reports that already exist on the reporting services point.
8. Click Next.
9. On the Security page of the Copy Reports Wizard, specify which users can access the copied reports and the roles they must belong to. For more information about the options on this page, see Report Server Properties: Security Tab.
10. Click Next.
11. On the Summary page of the Copy Reports Wizard, review the reports that will be created, and then click Next.
12. After the reports are copied, click Next.
13. On the Confirmation page of the Copy Reports Wizard, review the information, and then click Close to exit the wizard.
Note: |
If a report fails to import, it will be indicated by a red cross. Double-click the entry to open the Details dialog box, which lists details of the error in copying the report. |
Comments
Anonymous
January 01, 2003
So I started down the journey of upgrading to R2. I have no clue what we are going to use in R2 yet butAnonymous
February 06, 2010
Thanks for posting this information!!! Very useful and helped a lot!!! Was having a heck of a time trying to figure out how to configure this using Technet and other web sites!!! Did mine with SQL 2008 but still was pretty darn close to perfect! Kris TurnerAnonymous
February 06, 2010
Do you have any documents on how to configure the WSUS intergration? Also on how to deploy PXE service points to more then one site server?Anonymous
June 02, 2010
Do anyone know if SCCM 2007 SP2 R2 supports Reporting services point installed on a SLQ 2008 cluster database ? Seems that it doesn't work. After I finished the wizard the data source settings are grayed out . BTW I running srsserver.dll version 4.0.6487.2000Anonymous
June 28, 2012
I am working as an intern for a company that uses this report authoring wizard. My manager would like to have description of the options that are under the 'Security Options'. Could somebody direct me to the right place, please?