blog-01While most Microsoft Dynamics GP system administrators are accustomed to securing access to the ERP system data using both built in and custom security roles and tasks, a completely different security model must be employed when using the SQL Reporting Services Reports.

Access to reports within SQL Reporting Services (SSRS) is controlled using Active Directory (AD) authentication of AD users or groups. Additionally, by default, the data behind the standard GP reports is secured by SQL database roles that need to be mapped to AD users and/or groups. The Microsoft Dynamics® GP 2010 SQL Server Reporting Services Guide provides a decent step by step guide of how to setup security for the reports based on predefined database roles that are created during the deployment process. Below is the listing of database roles:

DYNAMICS database roles COMPANY database roles
rpt_all user rpt_all user
rpt_executive rpt_executive
rpt_human resource
administrator
rpt_human resource
administrator
rpt_payroll rpt_payroll
rpt_power user rpt_accounting manager
rpt_accounts payable
coordinator
rpt_accounts receivable
coordinator
rpt_bookkeeper
rpt_certified accountant
rpt_collections manager
rpt_customer service rep
rpt_dispatcher
rpt_executive
rpt_human resource
administrator
rpt_materials manager
rpt_operations manager
rpt_order processor
rpt_payroll
rpt_power user
rpt_production manager
rpt_production planner
rpt_project manager
rpt_purchasing agent
rpt_purchasing manager
rpt_sales manager
rpt_shipping and receiving
rpt_shop supervisor
rpt_warehouse manager

To review what database roles allow access to which reports, download MDGP11R2_SRS_Report_Roles.pdf (CustomerSource login required.)

While this is an effective way to secure the data behind the reports, the predefined database roles may not match the needs of your organization as in many cases it makes more sense to secure the reports by report group rather than the user’s functional role. So before going through all the analysis to see if your business needs to match up the predefined roles, now is a good time to evaluate your security requirements.

Provided you actually do want to secure the GP reports, two key questions to ask are:

  1. Will any users be given rights to create custom reports using the Report Builder?
    If you allow users to create reports using the Report Builder, you will need to be sure that credentials are not stored in the GP data source (the default setting), or that the user creating the report uses an alternate method to secure the data such as report or folder permissions, or using an alternate data source that requires credentials.
  2. Will your organization use report subscriptions?
    Unfortunately for subscriptions to execute properly, the credentials for the data source must be stored with the data source. This means that if you want to allow users to subscribe to GP reports, you must enter and save appropriate credentials which have access to all the SQL data related to that data source. This means that the database role mapping to AD user/group is effectively bypassed. You would need to secure the reports individually or by folder.

As you can see, the decision how to handle report credentials is not easy as there are tradeoffs either way. The most effective security deployment will likely involve a combination of securing the SQL data, securing the SRS site and securing the individual reports and report folders.

I highly recommend reviewing the SSRS documentation in addition to the GP SSRS installation manual before installing SSRS or beginning the report deployment process so as to better plan your reports security.

Side note on SSRS installation:

One of the easily overlooked installation choices for SSRS is the service account. By choosing the ‘Use built-in account’ option, as recommended in the Microsoft Dynamics® GP 2010 SQL Server Reporting Services Guide, you will have difficulty with subscriptions. We recommend the use of a domain user account created specifically as the SSR service account.

If you find yourself needing to change the service account for SSRS, be sure to follow the step by step instructions to export the encryption key and reimport it so as to not require redeployment of the reports. Also, should you find you are unable to reach the SSRS site remotely after updating the service account to a domain user account, you will likely need to use the setspn utility on the domain controller to set the Service Principal Name. For example:

setspn –s http/sqlserver.domain.com svc_sqlreports

setspn –s http/sqlserver svc_sqlreports

Where sqlserver.domain.com is the fully qualified domain name and svc_sqlreports is the domain user account for the SSRS service.

For more information about setting up security for your Dynamics GP SSRS Reports, please do not hesitate to contact FMT Consultants, LLC.

Written by:
Martin Jahn, Senior Consultant
FMT Consultants, LLC

Posted by: