RSMUS.comPart of setting up and deploying Microsoft Dynamics GP SSRS reports is making sure the security is configured. There are really two steps in this process, setting up the security through SQL server and setting security in the SSRS website itself. If you are looking for how to set the permissions on the SQL side of things, check out my previous blog post, Solving SQL Server Reporting Services “cannot create connection to data source” error. If you are looking for information on how to configure security within the SSRS website, or the report manager, then you have come to the right place!
If you are getting this error when you attempt to open an SSRS report or the SSRS home page, then you have an issue with your report manager permissions.
The first thing you will need to do is find an account which is an administrator on the server where SSRS is installed. This will usually be the SQL server, though not always in larger deployments. The initial security is set so that built-in administrators have access to the site. If you are on as an admin but still running into permissions issues, make sure you launch internet explorer (or your browser of choice) as an administrator.
Next, navigate to your report manager site. This will usually be http://servername/reports where servername is the name of the server running SSRS in your environment. The home screen for the report manager should look something like this after you have deployed the Dynamics GP reports:
From this screen, we have a few options. You can set security on the company folders to secure SSRS reports on a company by company basis, or we could set security at the root level, which will flow down to every company. The best way to move forward depends a bit on your individual security needs of your company, though I generally recommend setting security at the root level and locking down anything that needs to be further secured from there.
To set the security at the root, click on Folder Settings near the top of the home page. This will set security for all of the folder which may exist. This will be the default security settings for the report manager:
In order to add additional users or groups to the security, we will select New Role Assignment to take us to this screen:
We will type the user name or group in the box and then select the roles that they need. This is based on windows authentication, so the format will be DOMAINUsername or group. Unfortunately, there is no lookup to the active directory here, so we need to know the name of the user or group which we are granting security to. The roles available give a pretty good idea of what they do when you grant them in the security window, but most users should really just need Browser, and maybe My Reports.
Any security that is set at this level will flow down to all of the folders under it, but that does not mean that it can’t be changed on individual folders after the fact. For example, you may want to grant security to all company folders to all of your employees, but you don’t want everyone to have access to the Payroll reports. What you can do is drill into the company folder, find the payroll reports, hover over the arrow and choose the security option:
This will bring up a screen that should look identical to the security screen we used to set security at the root level. The only option on this screen will be to Edit Item Security. When you click this option, you will be presented with this message:
This is telling you that your security settings are coming from the higher level parent folder. In order to change the security for the payroll folder, we will need to click OK. We can now edit and change security, either by adding additional users/groups, or removing our current selections. If you accidently get to this step without intending to break from the parent security, you can always revert back to the parent security:
Once you have security set the way you want it, you should be able to let your users access the SSRS site without error. As mentioned above, in order to run reports, users will need to have SQL permissions set on their user. Now, get to generating your SSRS reports!
If you are looking to crate SSRS reports for your Dynamics GP solution, contact our professionals to learn how we can help. RSM has 30 years of experience helping clients maximize their Dynamics investment. Our professionals can be reached at erp@rsmus.com or 855.437.7202.
By: Paul Newsom – Colorado Microsoft Dynamics GP partner
