Using SQL Reporting Services is really a “blast”. If you want to use the reporting feature with Configuration Manager 2007 R2 (and you do!), you need to configure a few accounts, groups and configure permissions. In this post I assume that SQL Reporting Services is installed and configured.
Accounts to be created
- Execution Account (Configured in the SQL Reporting Services manager)
- The EA is used by SRS to execute reports used for subscribtions. The account should be a low privilige account that has:
- Read access to all reports
- Open a browser and type SQLReportingSername/Reports
- Click Properties
- Click New Role Assignment
- Fill in the domain\account name and select the Browser role
- Click OK to finish the configuration
- The EA is used by SRS to execute reports used for subscribtions. The account should be a low privilige account that has:
- Dataset Authentication Account (configured in the Config Mgr. console)
- A SQL login and db_datareader user mapping to the Configuration Manager database.
- To assign the correct permission to the account open SQL Studio Management.
- Select Security, Logins
- Right click Login and select New User
- Click Search and add the domain account. Select the SMS_Sitecode as the default database and click OK
- Right click the new Login name and select Properties.
- Select User mapping. Select the SMS_sitecode database and map the user to the DB_datareader.
- Click OK and close SQL
- Open the Configuration Manager console.
- Navigate to Computer Management, Reporting, Reporting Services, Servername.
- Right click the reporting services server and select Properties.
- In Data source settings type the database servername (and instance if needed) and the name of the SMS_sitecode database.
- Select Data Source Authentication and select Credentials stored securely in the report server. Fill in the data source authentication account like domain\account. Select Use as windows credentials when connecting to the data source.
- Click OK
The same account can be used as both Execution and Data source authentication. The permissions in this example also enables the account to be used for subscription purposes.
Groups to be created
- Reporting Viewers
- Members who have read access to the reports. Add the group to the SQL Reporting Services Browser role as described earlier in the post..
- Reporting Administrators
- Members who can publish reports, create subscriptions etc. Select all the roles in the New role assignment.
A great SQL tutorial (http://www.sql-reports.net/2011/03/sql-select-tutorials.html)can be found at the site SQL Reports( http://www.sql-reports.net). It is most helpful among people who are just starting on SQL selects.
Kent,
Long time listener, first time caller. 😉 Couple of questions:
First, you state the ‘execution’ account should be a low privilege account. Can I use a domain account that is simply a member of domain users and nothing else?
Second, the landing page for http///reports appears to be the ‘home’ folder, which contains the ConfigMgr_ folder, but I don’t see a ‘properties’ option. I see a ‘folder settings’ button and a ‘site settings’ link in the top right-hand corner of the page. Perhaps this is a newer version of SRS than you used for this post?
@ Mike, Yes it can be a domain user account. What version of SQL are you running?
Yes, it can be a domain user account. What SQL version are you running?
Does your site have a contact page? I’m having problems locating it but, I’d like to
send you an e-mail. I’ve got some ideas for your blog you might be interested in hearing. Either way, great site and I look forward to seeing it expand over time.
hi Kent,
I was not able to get the option for :
Open the Configuration Manager console.
Navigate to Computer Management, Reporting, Reporting Services, Servername.
I am using sql server 2008 and am trying to configure SSRS in my company domain. For some reason I am not able to as it is asking me enter the SSL.