ERP Software Logo1

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

FMT Consultants

Security Considerations for Microsoft Dynamics GP 2010 SQL Reporting Service Reports


Email | Print

While 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 about 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

 

FMT Consultants, LLC is a leading Microsoft Dynamics GP partner located in Southern California. FMT Consultants has completed more than 550 successful Dynamics GP implementations and is serving hundreds of clients in the US and Canada, with a significant presence in San Diego County, Orange County and Los Angeles County. Learn more at www.fmtconsultants.com

Comments are closed.