When you navigate to the pages in Business Portal for Microsoft Dynamics GP 2010 that have web parts to display Microsoft SQL Server Reporting Services (SSRS) reports, charts or KPI's, you may receive one or more error messages:
Error 1:
The request failed with HTTP status 401: Unauthorized.
Error 2:
The item '/%dbname%/%series%/Charts and KPIs/%report%' cannot be found. (rsItemNotFound)
Error 3:
The permissions granted to user 'domain\user' are insufficient for performing this operation. (raAccessDenied)
Error 4:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSourceGPCompany'. (rsErrorOpeningConnection)
Login failed for user 'domain\user'.
Error 5:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSourceGPCompany'. (rsErrorOpeningConnection)
Cannot open database '%dbname%' requested by the login. Login failed for user 'domain/user'.
Error 6:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSourceGPCompany'. (rsErrorOpeningConnection)
The EXECUTE permission was denied on object '%objectname%', database '%dbname%', schema 'dbo'.
Symptoms
Cause 1: There are several potential causes for this error.
Cause 2: The SSRS reports for Microsoft Dynamics GP 2010 have not been deployed.
Cause 3: The domain user attempting to run the report does not have access to the report in SSRS.
Cause 4: The SQL Server login has not been created for the domain user attempting to run the report.
Cause 5: The domain user running the report does not have access to the Microsoft Dynamics GP 2010 company database(s).
Cause 6: The domain user has not been mapped to the appropriate database roles in the Microsoft Dynamics GP system and company databases.
NOTE: If you are just receiving a generic error message it is likely because Remote Errors are disabled in SSRS by default. To enable this feature perform the following:
1. Log on to the server where SSRS is installed.
2. Click Start, click All Programs, point to Microsoft SQL Server 2008 (or 2008 R2) and then click SQL Server Management Studio. Connect to your Reporting Services instance.
3. Right-click on the instance name in the Object Explorer on the left and select Properties.
4. Select the Advanced tab on the left.
5. Under the Security section set EnableRemoteErrors to True.
6. Click OK to save the change. It is not required to restart the service to complete this change.
Cause
Resolution 1:
Refer to the following article for the resolution:
2608328 When you click on the Executive Center in Business Portal, the error “The Request Failed with HTTP Status 401: Unauthorized” appears in the SQL Server Reporting Services web parts.
Resolution 2:
Refer to the SQL Server Reporting Services Guide for information regarding the deployment of the Microsoft SQL Server Reporting Services reports for Microsoft Dynamics GP. You can download the SQL Server Reporting Services Guide at the link below:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=18981
Resolution 3:
If you are using SSRS in Native Mode, use the Report Manager site to add or verify the user's access to the reports. If you've deployed the SSRS reports in SharePoint Integrated Mode, use SharePoint security to verify that the user has access to the report library site. For more information on SSRS permissions review Chapter 7 of the SQL Server Reportings Services Guide at the link below:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=18981
Resolution 4:
Refer to the steps below to create a SQL Server login for a domain user:
1. Log on to the server where SQL Server is installed.
2. Click Start, click All Programs, point to Microsoft SQL Server 2008 (or 2008 R2) and then click SQL Server Management Studio.
3. Connect to the instance of SQL Server where the Microsoft Dynamics GP databases are located.
4. Click to expand Security, click to expand Logins in the Object Explorer.
5. Right-click on the Logins folder and click New Login...
6. In the Login Name field specify the domain user from the error message in the format of domainname\username
7. Click the User Mapping tab on the left to assign the user access to the appropriate Microsoft Dynamics GP databases. Refer to Chapter 7 in the above SQL Server Reporting Service Guide for details on company and role assignment.
Resolution 5:
To verify the database assignment of the SQL Server login given in the error you can use the following steps:
1. Log on to the server where SQL Server is installed.
2. Click Start, click All Programs, point to Microsoft SQL Server 2008 (or 2008 R2) and then click SQL Server Management Studio.
3. Connect to the instance of SQL Server where the Microsoft Dynamics GP databases are located.
4. Click to expand Security, click to expand Logins in the Object Explorer.
5. Right-click the SQL Server login for the domain user given in the error message and click Properties.
6. Click the User Mapping tab on the left.
7. All logins that need to run the Microsoft Dynamics GP SSRS reports must be mapped to the system (DYNAMICS) database as well as any company database they intend to report against. Refer to Chapter 7 of the SQL Server Reporting Services Guide below for more information on user security.
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=18981
8. Click OK and have the user run the report again.
Resolution 6:
To change the database role assignment for your SQL Server logins you can review the following:
1. Log on to the server where SQL Server is installed.
2. Click Start, click All Programs, point to Microsoft SQL Server 2008 (or 2008 R2) and then click SQL Server Management Studio.
3. Connect to the instance of SQL Server where the Microsoft Dynamics GP databases are located.
4. Click to expand Security, click to expand Logins in the Object Explorer.
5. Right-click the SQL Server login for the domain user given in the error message and click Properties.
6. Click the User Mapping tab on the left.
7. Click one of the Microsoft Dynamics GP databases the login has access to (e.g. DYNAMICS or TWO).
8. Review the selection in the 'Database role membership for 'xxxxx' section on the bottom of this database for each database. A list of what database roles give access to what SSRS reports can be found in the MDGP11R2_SRS_Report_Roles.pdf file on page 50 of the of the SQL Server Reporting Services Guide. Click the link below to download the SQL Server Reporting Services Guide:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=18981
9. Click OK and run the report again.
