asked on

Unable to run reports from CRM

Hi Guys
I've been struggling to get reports running on a crm system. Rollup 2 was applied recently but I'm not absolutely sure the reports were functioning prior to this.
I have seen the MS article that describes the tracelog error but I'm not going across a trust for domain queries and I'm not importing companies so I don't think it applies to me.

The configuration is a CRM server then behind that an sql server with reporting services, there is a provisioning server as well.

There are a few other CRM servers with reports functioning correctly with a similar setup in this environment but for the life of me I can't see where the problem lies.

I've been over and verified microsofts additional steps when SQL RS is install on a seperate server from CRM and it all fits. The crm IIS site runs as a service account and the reporting service iis runs as the network service.
From the user perspective they receive the error crm window in IE with the following

Reporting error
The report cannot be displayed

From the CRM servers event log.
Web service request SetDataSourceCredentials to Report Server http://server.domain/ReportServer failed with SoapException. Error: An error has occurred during report processing. (rsProcessingAborted)

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 1/05/2009 8:52:43 a.m.
Event time (UTC): 30/04/2009 8:52:43 p.m.
Event ID: 34f16dca62ca417a83a702819f5d7de0
Event sequence: 11895
Event occurrence: 18
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/1/ROOT-1-128855114073071492
Trust level: Full
Application Virtual Path: /
Application Path: c:\inetpub\wwwroot\
Machine name: SERVER

Process information:
Process ID: 3220
Process name: w3wp.exe
Account name: DOMAIN\SERVICEACCOUNT

Exception information:
Exception type: CrmReportingException
Exception message: An error has occurred during report processing. (rsProcessingAborted)

Request information:
Request URL: http://EXTERNALURL/CRMReports/rsviewer/reportviewer.aspx
Request path: /CRMReports/rsviewer/reportviewer.aspx
User host address: HOSTIP
User: {9693860f-c118-de11-9982-005056973caf}
Is authenticated: True
Authentication Type: CrmPostAuthentication
Thread account name: DOMAIN\SERVICEACCOUNT

Thread information:
Thread ID: 1
Thread account name: DOMAIN\SERVICEACCOUNT
Is impersonating: False
Stack trace: at Microsoft.Crm.Web.Reporting.SrsReportViewer.SetExecutionCredentials(ServerReport report)
at Microsoft.Crm.Web.Reporting.SrsReportViewer.ConfigurePage()
at Microsoft.Crm.Application.Controls.AppUIPage.OnPreRender(EventArgs e)
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Custom event details:

For more information, see Help and Support Center at

From the Reporting Services tracelog

w3wp!processing!5!4/30/2009-20:09:29:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CRM'., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CRM'. ---> System.DirectoryServices.DirectoryServicesCOMException (0x80072030): There is no such object on the server.

at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail)
at System.DirectoryServices.DirectoryEntry.Bind()
at System.DirectoryServices.DirectoryEntry.get_AdsObject()
at System.DirectoryServices.PropertyValueCollection.PopulateList()
at System.DirectoryServices.PropertyValueCollection..ctor(DirectoryEntry entry, String propertyName)
at System.DirectoryServices.PropertyCollection.get_Item(String propertyName)
at Microsoft.Crm.BusinessEntities.SecurityUtils.GetSidFromAD(Guid objectGuid)
at Microsoft.Crm.Reporting.DataConnector.SrsExtConnection.ValidateCaller(Guid systemUserId, Guid organizationId)
at Microsoft.Crm.Reporting.DataConnector.SrsExtConnection.Microsoft.ReportingServices.DataProcessing.IDbConnection.Open()
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc)
--- End of inner exception stack trace ---
w3wp!processing!5!4/30/2009-20:09:29:: e ERROR: Data source 'CRM': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CRM'. --->8:18 p.m. 30/04/2009.

w3wp!processing!5!4/30/2009-20:09:29:: e ERROR: An exception has occurred in data source 'CRM'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CRM'. ---> System.DirectoryServices.DirectoryServicesCOMException: There is no such object on the server.

w3wp!processing!5!4/30/2009-20:09:29:: i INFO: Merge abort handler called. Aborting data sources ...
w3wp!processing!5!4/30/2009-20:09:29:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CRM'. ---> System.DirectoryServices.DirectoryServicesCOMException: There is no such object on the server.

crm_info

One quick suggestion: Microsoft actually released Update Rollup 2, then Update Rollup 2 version 2 (because the first version caused so many problems) and then, more recently, they released Update Rollup 3.
It's possible that you got the first version of Rollup 2, which may have caused your errors. Try going to Rollup 3 to see if that will resolve your problems:
http://www.microsoft.com/downloads/details.aspx?familyid=05453fa4-9551-4a88-9852-634a9ad0e140&displaylang=en&tm

dj_relentless

ASKER

I had debated doing rollup 3 on these servers but I didn't want to compound the problem by possibly introducing new ones.
Though I get the feeling this is exactly what microsoft will say if I put the call through to them.

Aftab_Khateeb

I have gone through your error information
You got to check couple of things
1) when you login to your reportserver,http://<Servername>/Reports - you could navigate to the CRM <Organization_MSCRM>. you would find CRM datasource, open it and check the SQL connection string is pointing to the right server.
2) In SQLSERVER, expand the database_MSCRM and check if user or administrator who has installed the Reportserver has CRMReaderrole assigned.

dj_relentless

ASKER

Thanks for your comment.
In the Connection string it shows the correct server, here is the full string minus company info.

Data Source=server.domain.local;Initial Catalog=company_MSCRM;Integrated Security=SSPI

In the sqlserver, the administrator doesn't have the crmreaderrole currently. I've tried assigning the db rights but it doesn't seem to make any different to what is logged.

Aftab_Khateeb

Can you try adding the role CRMReaderRole to your CRM database?
Also i would like to know if CRM and SRS is installed in one server and SQL in another? If yes, then install CRM 4.0 Connector for SQL Server Reporting Services.

crm_info

I've not tried this yet, but it looks to have some tools that can aid in debugging report server.

http://blogs.msdn.com/benlec/archive/2008/03/04/crmdiagtool4-for-microsoft-crm-4-0-has-been-released.aspx

rakeshAgarwal

Yesterday I resolve this error for a client

1. You have to install data connector on sql box
2. publish the all the reports to report server <install location of crm/tools/publishreport.exe <<orgname>>
3. Go to report server , open report manager you will see orgname_mscrm folder
4. you will see folder 4.0
5. Scroll down to datasource
6. Select from datasource --> crm extensons
7. Credential will be provided by user at run time

issreset

rakeshAgarwal

You dont need to do second step just do 6th and 7th step that should help

dj_relentless

ASKER

I haven't installed the diag tools yet.
sql and reporting services is on the same box with crm on a different box.
The crmreaderrole is already added to the databases

rakesh, the datasource configuration inside the 4.0 folder is configured as you've suggested.

If I run the report from the reporting services page (from the sql server) and not via crm I receive this message.
(it does request user id and pass before the report runs)

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'CRM'. (rsErrorOpeningConnection)
Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

dj_relentless

ASKER

Though after I checked I realised that error is normal if run from the reporting services.

Aftab_Khateeb

Have you given permission for NT AUTHORITY\NETWORK SERVICE with CRMReaderrole? I think its better to reinstall CRM Connector 4.0 and check if its giving the same issue

dj_relentless

ASKER

I did test giving the network service (which is what the crm iis site runs as) crmreaderrole on the companies database but it didn't make any difference at the time.
I've reinstalled the rs connector on the sql server before.
Something I haven't mentioned yet which may make quite a difference is that the user logging in has a different upn to the domain.

ASKER CERTIFIED SOLUTION

dj_relentless

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cov IT

I am having the same issue. Can you clarify your solutions a little?

customertrax

I'm having the same issue, but don't understand what you're referring to in your solution. Could you please clarify, or walk through the steps a little?