Monday, April 4, 2011

Configuring SQL Reporting Services user permission for Configuration Manager 2007

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
  • 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.