Run SSRS Report in MS Dynamics CRM on-premise that connects to a different server and database

(0) Share

Report

Posted on by Community Member

Hi,

I am facing an issue with the SSRS report we created for MS Dynamics CRM. The SSRS report runs fine on BIDS but when it is deployed on CRM and run from there we get the following error:

Cannot create a connection to data source 'DS_CustomerRegistrationsperLocation'. ---> System.Data.SqlClient.SqlException: Login failed for user '{bacbf62b-ab12-e611-80b6-00155d0a0605}'.
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at Microsoft.ReportingServices.DataExtensions.ConnectionExtension.Open()
   at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapperBase.Open()
   at Microsoft.ReportingServices.Diagnostics.DataExtensionConnectionBase.OpenConnection(IProcessingDataSource dataSourceObj, DataSourceInfo dataSourceInfo, IDbConnection conn)
this is the error that i found from the reporting logs in SQL server
machine

The SSRS report is actually connecting to the database of a different application in another SQL Server Instance running on another machine. I need to know whether this is possible to do or is there any way I can show the reports from database other than MS CRM?

Regards,

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Andreas Cieslik 9,267 on at

Like (0)

Report

Follow the rules mentioned here to prevent login failures for service accounts, databases and tables, etc.:

crmtipoftheday.com/.../including-external-data-in-internal-reports

Was this reply helpful? Yes No
Suggested answer

tw0sh3ds 5,600 on at

Like (0)

Report

Hi,

It works in BIDS, because for sure you are using BIDS on your account and also your account has access to external DB. When you deployed report to CRM, the report is run by CRM service account. How did you configure connection string for your external DB? There can be multiple solutions based on how your environment configuration looks like. The easiest would be to include user and password in your connection string to external DB or just give access to this DB for the user that runs CRM service.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

Thanks all for the tips. I have included the user name and the password in my connection string but still it is not working. Also, how will I know what service account is running the Report?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks. Can you advise how to check this :

"Make sure that the service account that your MSCRM services runs as has permission to read the other database. "

There are multiple service accounts being used in the deployment. How to check under which service account the database access is requried?

Was this reply helpful? Yes No
Suggested answer

tw0sh3ds 5,600 on at

Like (0)

Report

Hi,

You need to check IIS Application Pool user

Was this reply helpful? Yes No
Suggested answer

Andreas Cieslik 9,267 on at

Like (0)

Report

Checking this article:

technet.microsoft.com/.../hh699754.aspx

The identity account running the instance of Microsoft SQL Server Reporting Services where the Microsoft Dynamics 365 Reporting Extensions are running can’t be the local system or a virtual account. This is required for Microsoft Dynamics 365 reporting to work because the identity account must be added to the PrivReportingGroup Active Directory security group that is used by Microsoft Dynamics 365.

The same identity account needs to able to access your other database as well.

You can find the configured account in the Reporting Services Configuration Manager tool.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have MS Dynamics 2016 on-premise.

In the IIS, the CRMAppPool in IIS is running under NetworkService, and CrmDeploymentServiceAppPool is running under a service account.

And the reporting service is running under an Admin account "Acc_admin"which is also the member of PriReportingGroup and is also admin. I guess I have to give the Acc_admin rights over the remote database. Please advise if I am heading in the right direction.

Was this reply helpful? Yes No
Suggested answer

tw0sh3ds 5,600 on at

Like (0)

Report

Sorry, I made a mistake, Andreas is right, you should be looking at the user which runs Reporting Services. So you should give Acc_admin access to the remote DB

Was this reply helpful? Yes No