You’re offline. This is a read only version of the page.
I have deployed SSRS report using SQL 2019 and SSRS 2019. I added domain users to the folder containing these reports. The users are getting this message "Cannot create connection to the datasource". What roles and/or database mappings need to be assigned? I did not have to do this in older versions.
I don't have a SQL 2019/SSRS 2019 environment in front of me right now, but normally, as you know, there are three areas where permissions need to be given for users to access SSRS reports deployed from Dynamics GP:
1. In the SQL SRS Report Manager site, click on Site Settings > Security. Click on the 'Security' tab, and then click 'New Role Assignment' in order to assign the users/groups as a System Administrator or a System User, depending on what they need permissions to do. (If at all possible, set it up per user, not per user group, as we've seen various results when using user groups.)
2. Also in Report Manager, click on Home > 'Folder Settings', on the Security tab, click 'New Role Assignment' and assign users/groups one or more of the roles as needed: Browser, Content Manager, My Reports, Publisher, Report Builder.
3. Lastly, in SQL Server Management Studio, expand the main Security folder, then expand Logins. Find the Windows user account that you're giving access to the SQL SRS reports and right-click on it, and choose Properties.
In the Login Properties window for that Windows user, click on the 'User Mapping' page.
Here, select the DYNAMICS/system database for Dynamics GP and in the list of database roles, you'll see about a few RPT_ roles. Mark the 'rpt_all user' role, which is required for all users, and then assign the user any of the other rpt roles needed. If you want the user to have access to all SQL SRS reports and/or Excel Report data, you can assign them the 'rpt_power user' role, which is similar to the PowerUser security role in Dynamics GP.
In any of the company databases that the user needs to access SQL SRS reports from, highlight those companies as well in this Login Properties window and under the list of database roles, you'll again see a series of rpt roles for different areas, similar to the security roles in Dynamics GP. Assign the user to the rpt roles the user needs to access the SRS reports, or give the user the 'rpt_power user' role if you want them to have access to all SQL SRS report data.
Click OK and close the window to save changes.
I know in SQL 2017 and 2019, some of the paths for the above are a bit different, but the same settings are still there and needed.
Based on the message, I would check the datasource for the SSRS reports to see if they're set to use 'Windows Integrated' security, to connect to the SQL Server, which is the default and means it is looking at the permissions of the actual Windows account you're logged onto the machine as, or setup to use something different. There's also a connection test in the Datasource window as well on the SSRS sites.
Thank you,
Share
Report
75,850
All responses (
Answers (
