Security Considerations for Microsoft Dynamics GP 2010 SQL Reporting Service Reports
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 rolesCOMPANY 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:
- 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. - 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.
Written by:
Martin Jahn, Senior Consultant
FMT Consultants
Posted by:Jakob Bechgaard