Accessing SQL Server Reporting Services by Non-Admin Accounts

Question

When accessing SQL Reporting Services Report Manager through Great Plains 2010, we're getting the following:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Cannot create a connection to data source 'DataSourceGPCompany'. (rsErrorOpeningConnection) Get Online Help

It's not a GP issue, but rather a permission issue with SQL Reporting Services. We don't have IIS installed on that server. If we hit it using our domain admin, it works. All other users fail.

The strange thing is that the Reports vdir works find. It's just the webservice that is failing.

Answer 1

Have you captured a profiler trace and checked if there is a "Login Failed" reported on the SQL Server GP database or an access denied on certain tables on the SQL database? Or is there a login failed reported in the Reporting Services logs?

Also, do these users have access to browse these reports? Users need to be in the browser role to generate these reports and should have the necessary permissions on the database objects to get the data for these reports if you are using Windows Authentication and the same user credentials to connect to the SQL Server instance. If the same account is being used to connect to the backend SQL instance irrespective of the user accessing the report, then you need to check if the users are part of the RS Browser role for the RS folder which contains these reports.

Reference: 954242 Frequently asked questions about the integration of SQL Server Reporting Services (SSRS) with Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010 http://support.microsoft.com/default.aspx?scid=kb;EN-US;954242

Answer 2

Apparently the user needed access to one of the other databases and a particular role. Weird though.