Posts Tagged ‘CRM 3.0’

Troubleshooting CRM Data Connector

The background:

So, I was called in to troubleshoot a CRM Reporting issue a client had, they had CRM Server front end, SQL Server 2008 + SSRS on the backend server – simple setup.

First thing first, CRM Data Connector is just another name (the real name) for Microsoft Dynamics CRM 4.0 Connector for SQL Reporting Services.

A quick revision and the purpose of the Data Connector

From Barry Givens’ post:

  1. Reports in CRM 4.0 are running out of this thing called “SQL Reporting Services Report Viewer” which is plainly an ASP.Net control that runs on the CRM 4.0 Web server. So when users view reports in CRM 4.0, they are hitting up a URL on the CRM 4.0 Web Server.
  2. Because of this, CRM 4.0 reports are always run in a delegated mode, the CRM and SSRS integration has to handle security. This requires us to use integrated authentication and configure trust for delegation between the CRM server, the SSRS server and the SQL server with the CRM database. [Refer to HOW TO: Configure Kerberos authentication for Microsoft CRM 3.0 and Microsoft SQL Server Reporting Services and Microsoft CRM 3.0: Additional Setup Tasks Required if Reporting Services Is Installed on Different Server.]
  3. MSFT decided to make our life less miserable by shipping this Data Connector with CRM 4.0. It is essentially an SSRS Data Processing Extension and handles all of the delegation for us so that we no longer have to fiddle around Kerberos etc, which wasn’t an option in version 3.0.

Right, back to our problem at hand;

The symptoms:

1. When users try to run a SSRS report via CRM Web App, they get a 401.

[{date time}] Process: w3wp |Organization:{Org Guid}
|Thread: 5
|Category: Application
|User: 00000000-0000-0000-0000-000000000000
|Level: Error
| ErrorInformation.LogError
>MSCRM Error Report:
--------------------------------------------------------------------------------------------------------
Error: Exception of type 'System.Web.HttpUnhandledException' was thrown.
Error Number: 0x80040494
Error Message: The request failed with HTTP status 401: Unauthorized.
Error Details: The request failed with HTTP status 401: Unauthorized.
Source File: Not available
Line Number: Not available
Request URL: http://{IPAddress}:5555/{OrgName}/CRMReports/rsviewer/reportviewer.aspx
Stack Trace Info: [WebException: The request failed with HTTP status 401: Unauthorized.]
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.SetParameters(IEnumerable`1 parameters)
at Microsoft.Crm.Web.Reporting.SrsReportViewer.ConfigurePage()
[CrmReportingException: The request failed with HTTP status 401: Unauthorized.]
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)
[HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown.]
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.{OrgName}_crmreports_rsviewer_reportviewer_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

2. However, users can directly run the reports via report server URL.

3. When user click on the Preview arrow to the left of each report, like the one shown below;

image

They are greeted with the following error message;

“Reports cannot be run because the Connector for Microsoft SQL Server Reporting Services, a required component for reporting, is not installed on the server that is running Microsoft SQL Server Reporting Services.”

image

4. This is accompanied by the following error message in the CRM Web trace;

[{Date & Time}] Process: w3wp
|Organization:{Org Guid}
|Thread: 6
|Category: Application
|User: 00000000-0000-0000-0000-000000000000
|Level: Error
| ErrorInformation.LogError
>MSCRM Error Report:
--------------------------------------------------------------------------------------------------------
Error: Exception of type 'System.Web.HttpUnhandledException' was thrown.
Error Number: 0x80040492
Error Message: MSCRM Data Connector Not Installed
Error Details: MSCRM Data Connector Not Installed
Source File: Not available
Line Number: Not available
Request URL: http://{IPAddress}:5555/{OrgName}/_grid/preview.aspx?type=9100&id={Report Guid}
Stack Trace Info: [CrmException: MSCRM Data Connector Not Installed]
at Microsoft.Crm.ObjectModel.ReportService.GetReportServer(ExecutionContext context, Boolean verifyAndConfigDataConnector)
at Microsoft.Crm.ObjectModel.ReportService.ListSnapshots(Guid reportId, ExecutionContext context, String[]& HistoryIds, DateTime[]& CreatedDates)
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
at Microsoft.Crm.Application.Utility.Util.RaiseXMLError(Exception exception)
at Microsoft.Crm.Application.Pages.Grids.PreviewPage.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)
[HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown.]
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.{OrgName}__grid_preview_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Diagnosis and resolution:

The fact that users are able to browse to the reports via report server and getting a 401 via CRM Web application, suggests the Data Connector is playing up. The second error message about Data Connector confirms this assumption. So we decided to reinstall Data Connector. The steps we did this in are the following;

1. Uninstall Data Connector from Add and Remove Programs.

2. Reinstall Data Connector from command line and the following configuration file.

3. Restart SSRS.

4. Recycle CRM AppPool or IISRESET.

5. Verify it works by firing up new IE and run up the reports.

6. Ask for your managers for a pay rise.

IMPORTANT points to remember when installing CRM Data Connector:

  1. Data Connector must be installed on the same server as the SQL Server Reporting Services instance that your CRM Server is using!
  2. If CRM is using a SQL Server 2008 backend, we must download an installer update file (.msp) from this KB article [How to obtain the setup updates for Microsoft Dynamics CRM 4.0] and install it via command line and a XML configuration file. (Refer to page 97 of the Install Guide for details.)
  3. If there are more than more SQL Server Reporting Services instances on the same server, the Reporting Server Uri and the instance name must be specified in the configuration file. (Like the one shown below.)
  4. Always remember to restart SSRS and IISRESET the CRM Web application before verify this has worked.

<crmsetup>
<srsdataconnector>
<configdbserver></configdbserver>
<autoupdateconfigdb>1</autoupdateconfigdb>
<reportserverurl>http://servername/reportserver_SSRS2008</reportserverurl>
<autogroupmanagementoff>0</autogroupmanagementoff>
<instancename>SSRS2008</instancename>
<configsku>OnPremise</configsku>
<!-- Set enabled = true for DB webstore integration.  Set configdb="true" for config db webstore integration-->
<webstore enabled="false" configdb="false" />
<monitoring>
<!-- Monitoring service account name and password. It can not be local system or network service account -->
<serviceaccountname></serviceaccountname>
<serviceaccountpassword></serviceaccountpassword>
</monitoring>
</srsdataconnector>
</crmsetup>

There you go, happy CRM’in 🙂

Advertisements

Malformed email header by CRM 3.0?

                                  

I have been spending sometime  troubleshooting a production issue for a client .

Background:

This is a CRM 3.0 implementation. When a member of public fills up a web form to inform the client of a gas or electricity incident, it calls upon CRM WebService to create a custom entity “Incident” in CRM to capture some information. This in turn, triggers a workflow sending an email with the incident details to DMZ Global. DMZ Global will then interrogate this message, package it up and send it onto Telecom’s eTxt. eTxt will then send it off to a pager that’s on call.

Lost yet? Essentially, a message that’s entered through a web page will jump through a few hops and get sent to a pager.

Problem:

The messages will choose to arrive at the pager as they please.

i.e.; The messages will arrive sometimes and not others, there is no obvious patterns in why some arrives while others don’t.

Investigation:

We were able to pick up some traces for a few failed and successful messages through the full route.

Client Website -> Client SMTP servers -> DMZ Global -> Telecom eTxt  -> Pager

We came to realise that the ones that DID arrive at the pager has the following Email Headers.

From: =?utf-8?B?Q1JNX0ludGVyYWN0aXZlIFBSRCBTeXN0ZW0gQWNjb3VudAA==?= <energysafety@client.com>

The ones that did NOT arrive at the pager have this following Email Headers.

From: =?utf-8?B?Q1JNX0ludGVyYWN0aXZlIFBSRCBTeXN0ZW0gQWNjb3VudAA=pre>?= <energysafety@client.com>

Thanks to this free online decoder. UTF-8 Base 64 string

Q1JNX0ludGVyYWN0aXZlIFBSRCBTeXN0ZW0gQWNjb3VudAA==

translates to

CRM_Interactive PRD System Account

This is the first and last name specified in CRM for a System Administrator account. In this instance, the workflow that sends out emails is executed under the security context of this account.

A couple of interesting observations;

1. The names are UTF-8 Base 64 encoded by CRM.

2. There is a incomplete pre> tag that’s injected towards the end of this encoded string.

One other point of interest is, when I change the first and last name to “Energy” “Safety” respectively. The from Email header will NOT be encoded;

From: "Energy Safety" <energysafety@client.com>

A couple of questions we asked ourselves were,

1. Why are some names UTF-8 based 64 encoded by CRM and not others?

2. Where did this incomplete Pre> tag got injected into the from address?

It’s confirmed that DMZ Global will conditionally inject <Pre> tags based on its ways of determining if the message is HTML based. Its parsing mechanism may have mistaken this UTF-8 Base 64 encoded string as HTML and somehow inject this incomplete Pre> tag into it. However, I’m not ruling out that the two internal SMTP servers may have been the culprit here.

Resolution:

Since the root cause of all is the name is UTF-8 Based 64 encoded. So we decided to make CRM NOT encode it, we achieved this by changing the first and last names in CRM to single word;

image

This prevented CRM from encoding the names with UTF-8 Base 64 and therefore prevented the SMTP Servers or DMZ Global from *sporadically* injecting this incomplete Pre> tag into the from email header.

Lessons Learnt

1. CRM 3.0 WILL encode the email addressees’ names with UTF-8 Base 64 if you have special characters like ‘_’ (I can’t find anything on CRM encoding email address names in the header, can anyone pin down an article that describes when it encodes them and when it doesn’t?)

2. The SMTP servers or DMZ Global MAY see the encoded string as HTML and decides to inject an incomplete Pre> tag, ONLY THEN, it becomes a malformed email header and Telecom’s eTxt will understandably fail to parse it out and send it onto the pager.

peculiar.