Dynamics CRM Excel Connection failed (Microsoftj(ODBC SQL Server Driverj[SQI. ServeriLogin failed

Today came across an error which I think is worth blogging. A new user can access CRM via IE and Outlook, when exporting data to an Excel dynamic Worksheet, receives the following error:

Connection failed:
SQLState ‘28000
SQL Server Error 18456
(Microsoftj(ODBC SQL Server Driverj[SQI. ServeriLogin failed for user DOMAIN\Username’.

The error above also does not specify why the login failed. So to understand exactly what's hapenning, we can give it a try and use the Excel built-in Data Source connection and retrieve a more friendly error message:

Select From SQL Server, below type the SQL server name and click next, this should give you something like Access Denied.

For some reason, the user was not a member of the CRM ReportingGroup (which provides the SQL access) causing the Access Denied error.

Resolution
Adding user to the CRM ReportingGroup fixed the issue, providing read access and allow Excel to fetch CRM data.

Dynamics CRM Improving Reports Performance

When developing reports, specially reports with dashboards we can end up with complex SQL code which running against FilteredViews is slower than directly on a table. A great trick to improve the code performance, is to download all content of the filtered view onto a temporary table stored in memory and then run the SQL code against this table in memory.

The example I have below, on the first query I get 5 min run time against a few thousand opportunities, the second block of code, I store the data onto a temporary table and then query it, and reduce the time to 36 seconds. The result is a much happier customer.

Dynamics CRM Installing Email router on multiple servers

View the wiki technet article here: http://social.technet.microsoft.com/wiki/contents/articles/7527.dynamics-crm-installing-email-router-on-multiple-servers.aspx

This article will show how with a few tweaks the email router can be deployed onto multiple servers across multiple offices and processing emails for specific users/queues or for specific regions.

Consider the following deployment requirement:

• Client network spans multiple offices across multiple countries
• CRM processes 2000 emails a day.
• Lisbon, New-York, London and Toquio generate most of the emails produced by leads and opportunities.
• The Toquio office wants to have complete control of all outbound emails from CRM
• A few offices want to be able to control which domains are allowed to relay and block others.

Outgoing Email
To improve performance and resilience, configure a local SMTP service using the Windows Simple Mail Transfer protocol, managed via IIS and point your outgoing profile to the local service. The following are the advantages of using a local SMTP service, instead of an external server:

1. Performance wise is faster, the E-mail router can send emails locally which are queued for external delivery much faster than going externally.
2. Granular control for the outbound email. e.g. you can process 3 different domains on your organisation each domain with different amounts of emails processed each day, you can specify 3 different SMTP external servers for each domain, improving performance and scaling your deployment.
3. Better resilience by specifying on-the-fly different SMTP servers to relay email to, in case an external SMTP server fails.
4. E-mail logging, log all incoming and outgoing emails.

I would strongly recommend setting up a local SMTP service. How to configure a local SMTP server is beyond the scope of this article, you will find online many white papers on how to do this for different versions of windows server.

Scaling CRM E-mail router
A manual configuration is needed in order to make the email router scalable.

The following configuration file holds the core email router configuration:
C:\Program Files\Microsoft CRM Email\Service\Microsoft.Crm.Tools.EmailAgent.xml

There are two sections on the EmailAgent.xml file that makes scaling possible:

System Configuration:
<ConfigUpdatePeriod>3600000</ConfigUpdatePeriod>
 This key enables the email router to reload all users and queues from CRM on the scheduled period, by default every 1hour.

Provider Configuration:
<UserId>xxx-xxx</UserId>
<QueueId>xxx-xxx</QueueId>
This Section holds all the Users System Id's and Queues Id's which the email router will process email for.


To allow the email router to be deployed to multiple servers we need to make sure they don't overlap each other and end-up processing the same emails. To avoid the overlapping we need to change the <SystemConfiguration> <ConfigUpdatePeriod> and set it to 0, this will make sure that the email router will not pick up any new users or queues apart form the manually specified on the configuration file.
 <ConfigUpdatePeriod>0</ConfigUpdatePeriod>


The second part, will be to manually specify which users or queues we want each email router to process email for. We do this by adding the <UserId> <QueueId> entries below the <EmailAuthMode> tag, below is an example how this would look like

 <EmailAuthMode>Anonymous</EmailAuthMode>
    <UserId>xxxxxxxx-13d8-e011-9077-xxxxxxxx</UserId>
    <UserId>xxxxxxxx-19d8-e011-9077-xxxxxxxx</UserId>

    <QueueId>xxxxxxxx-19d8-e011-9077-xxxxxxxx</QueueId>

By manually specifying the users or queues we want the email router to process, we make sure no other email routers overlap the same users. Also specifying the configuration update period to 0 we force the email router to never check for new users in CRM, making sure one email router does not suddenly download all users and queues in the system and ends up overlapping with other email routers.

Deployment Scenarios

We have covered how to make the email router resilient, how to gain granular control over the domains allowed to send emails using a local SMTP server, how to manually configure the email router to process emails for specific users or queues, and how to stop overlapping with other email routers.

The below diagrams illustrate possible scenarios for our requirements:

Scenario 1 

New york and London only process emails sent from their queue, if their users send an email will be routed via the Lisbon Email router, Toquio has been given full control, and processes emails for their own queue and all their users.

Scenario 2

On this scenario the approach is to give each office full control over which users are allowed to send emails from CRM. The only disadvantage from the Scenario 1 is the time spent on managing the configuration file if your company have multiple users joining and leaving the company, but this would be decentralised and managed locally by the IT team in each office.

There are a few other scenarios which could be considered. e.g. we could have designed a scenario where we only use queues, this would be much easier to manage and the only drawback would be to create a workflow to re-write every single email leaving CRM changing the From field to include the CRM queue responsible for the user region, increasing the workload on the asynchronous services.

Conclusion

With a few configuration changes the email router can be deployed on complex environments and scale to meet enterprise requirements. Configuring a local SMTP server improves performance and resilience giving admins the flexibility and granular control over all inbound/outbound emails. Hope this post was helpful.

Dynamics CRM Excel dynamic spreadsheet not loading data

Users export CRM data onto a dynamic spreadhseet and no data is downloaded or displayed. I come across this quite often. Users specially women, change names when marry. when they go back to work, the first thing that happens is a request to change the name in Active Directory. This change will not reflect in CRM, and when exporting data to Excel, this will cause problems, the spreadsheet will use the current logon name and CRM holds the old domain logon name and this prevents data from loading.

A quick way to fix this issue, is to update the user domain logon name directly on the database, the following command will update the user domain logon name:

UPDATE SystemUser set DomainName = 'domain\new_user'

WHERE DomainName='domain\old_user'

Hope this helps. 

Dynamics CRM 2011 Mobile Express Read-only

I have posted a few weeks ago how to make the CRM 4.0 Mobile express read-only.
http://quantusdynamics.blogspot.com/2012/01/dynamics-crm-40-mobile-express-read.html 
This post is a "part-2" for Dynamics CRM 2011 Mobile Express.

The Mobile Express for 2011 is based on the same concept but with some cosmetic changes. The files to change in order to make Mobile Express read-only are the same but the CSS entries are slightly different.

Edit the below files to make CRM 2011 Mobile Express read-only:

The files to be edited are located on:

C:\Program Files\Microsoft Dynamics CRM\CRMWeb\_common\styles\mobile\

entityhome.css.aspx and change:

.newRecordButton
{
padding : 4px;
display: none;
}

on the entityform.css.aspx:

input.editButton
{
display: none;
<% if (CrmStyles.IsRightToLeft) { %>
float: right;
<% } else { %>
float: left;
<% } %>
width: 108px;
}

input.deleteButton
{
display: none;
<% if (CrmStyles.IsRightToLeft) { %>
float: left;
<% } else { %>
float: right;
<% } %>
width: 108px;
}

Reset IIS 

And this will hide the New, Edit and Delete buttons.