RSMUS.comHave you ever received this error when trying to run a SQL Server Reporting Services report?
An error has occurred during report processing.
Cannot create connection to data source ‘XXXXXXXX’
For more information about this error navigate to the reporter server on the local machine or enable remote errors.
This is a common error in SQL Server Reporting Services reports which is caused by incorrect security setup. Let’s look at SQL Server Reporting Services reports and how we can resolve this kind of error:
SQL Server Reporting Services (SSRS) reports are one of the best ways to get and review data in Microsoft Dynamics GP. They offer the flexibility of exporting the reports to a number of formats (namely Excel), are extremely customizable and can even display information in a graphical format (Charts and KPIs) instead of purely text. They also available in Dynamics GP out of the box. So why is security such a big issue?
First, we need to understand why security is not the same as our Dynamics GP user’s security. Dynamics GP uses SQL logins for the user authentication within the application itself. In order to prevent these users from logging directly into the database itself and reviewing all of the information we have stored in our ERP system, Dynamics GP encrypts these passwords. This basically means that we can’t use these logins for anything except a Dynamics GP product (this includes Dynamics GP, Management Reporter and specific Dynamics GP third party solutions). What we need is a different way to secure these reports.
This brings us to a different form of authentication using windows credentials. This is how we will secure our SSRS reports. We can secure on individual users, though I would recommend using an Active Directory Security Group for ease of management. I like to start within SQL Server Management Studio. You will need to have elevated permissions within Management Studio in order to set the security, so I recommend an administrator account or the ‘sa’ account.
The first thing you will have to do is to create the windows login or group within SQL management studio. It is really less creating a new login as it is letting SQL know about an existing windows login or group. Within SQL management studio, we will expand the Security section, right click Logins, and select New Login.
That will bring us to this window:
Notice how we have the radio button for Windows Authentication selected and not SQL Server Authentication. From here, we will want to search for our windows logins or groups that we need to secure.
Within this window, we will type the name of the user or the group that we want to grant security for.
Make sure the location is set to the domain which the users exist in and that the object types include groups.
Once you have found the object which you want to secure, click ok on this window. We will want to go back to the New Login… window that we started on. We have selected our user, but we have not really given them any security yet. From the left hand menu, we will select User Mapping so we can grant permissions for our reports.
This will show a list of all of our databases which exist on this SQL server. The first thing we will need to do is check the box which is marked Map for any databases we want the users to have access to. Users will need access to the system database (usually called DYNAMICS), as well as any company databases on which they want to run reports. Once we have mapped the user to the database, we will need to grant them permissions on the mapped databases as well.
On the DYNAMICS (or system) database, all users will need public, as well as the rpt_all_user role. This is a role which is created when Dynamics GP is installed and the database is created. It will grant the required security on database objects, but nothing more. It does not give unfettered access to the database the way granting the DYNGRP role would.
The roles on the company databases look a bit different, although they should look familiar:
We will again need the public role, though from there, what is required will depend on your unique security needs. The rpt_* roles you see above are similar to the role selections you are offered when you first login to Dynamics GP as a new user. Some of them will be obvious as to their purpose; “rpt_power user” will grant access to everything that is available from a reporting perspective. Some will be less obvious though, and you may need some help. This link will download a Microsoft Dynamics GP SRS Report Roles document which can help with identifying which roles will grant permissions to which reports. It does require a customer source login to download, so if you are having trouble accessing the content, reach out to your Dynamics GP partner. Be careful when granting these permissions, anything you grant to the windows users can be used in SSRS, Excel, Access or any other program which can connect using an ODBC connection. The good news though is that you will not need to duplicate your efforts in order to grant security to Dynamics GP Excel reports.
The final step will be to officially create the login. Click the OK button on the New Login… window and it will do everything to create the login with the security you just defined.
That is all you need to do in order to grant security on the SQL side of things for SSRS report. My next blog will discuss the considerations of granting security using the SSRS report manager website. If you have questions or comments about this tip, please leave them below.
RSM has 30 years of experience helping clients maximize their Dynamics investment. If you are looking to optimize your Dynamics GP solutions, contact our professionals to learn how we can help. Our professionals can be reached at erp@rsmus.com or 855.437.7202.
By: Paul Newsom – Colorado Microsoft Dynamics GP partner
