External access from a Virtual Environment

We do almost all our CRM development and testing within virtual images. This confers many benefits, but raises issues if you need connect the image to the outside world. One example is when registering software; another is when developing callouts / plugins that communicate with external web services.

The major issue arises with a self-contained image that is a domain controller in its own domain, as well as a SQL server and CRM server. Active Directory depends on DNS, so the image has to be its own DNS server, which forces it to have a fixed IP address, which we place on separate IP private IP subnet (e.g. 192.168.x.0). This essentially prevents this IP address and adapter being used externally. With just the one adapter and IP address, you can’t change the IP address to a dynamic one (e.g. from DHCP), because within the image that breaks DNS, which breaks AD, which breaks CRM.

So, the solution; add a separate network adapter to the image, using an external IP address. With Microsoft Virtual PC or Virtual Server, the steps to take are:

• Turn off the image
• Add a 2nd network adapter to the image, and associate it with the physical adapter in the host machine
• Start the image, and set the IP address on the 2nd adapter to something that will work on the external network; either dynamically assigned via DHCP, or to a reserved static address

This resolves the IP addressing issue, and if you only need to connect to an external resource by IP address than that’s as much as you need. However, you may still have a DNS problem. I’ve not investigated all scenarios, but I have met cases when, even if the 2nd adapter is configured for with external DNS server, the image still uses its own DNS server for all DNS requests. In this scenario the simplest solution is to configure a forwarder for the virtual DNS server, so that it forwards all DNS requests that it cannot resolve to your known external DNS server. This can be configured with the DNS management snap-in.

Using permissions to personalise SiteMap

CRM allows modification of the CRM application navigation structure via SiteMap. This is normally considered to provide one customised navigation structure for all CRM users, with the only difference between what one user sees and what one of their colleagues sees is what they have permission for.

For most entries in SiteMap, whether they are displayed or not depends on the user’s permission on the corresponding entity. For example, if a user does not have Read privileges at any level on the invoice entity, they will not see invoices in SiteMap. However, SiteMap allows more explicit control over what privileges are required to display any link; this is done via the Privilege element.

The following extract from the default SiteMap illustrates how this is used:

<SubArea Id="nav_managekb" ResourceId="Homepage_KBManager" Icon="/_imgs/ico_18_126.gif" Url="/cs/home_managekb.aspx" Client="Web"> <Privilege Entity="kbarticle" Privilege="Read,Write,Create" /></SubArea>
This specifies that the Knowledge Base link in the Services Area should only be displayed to users who have Read, Write and Create privileges on the kbarticle entity.

Privilege elements are interpreted based on these rules:

• In one Privilege element, the user must have all privileges listed in the Privilege attribute
• The level of privilege doesn’t matter; user level is sufficient
• Multiple Privilege elements can be specified for one SubArea; in this case the user needs to fulfil the permissions for every element

There is no requirement for the Privileges element(s) to be related to the entity (or external link) that is displayed.

Using the Privileges element
Given this, it is perfectly reasonable to create multiple SubArea elements in SiteMap that would show similar (but not identical) information, but only display one of them to any given user, based on their permissions. For example, you may have created custom portal pages, but the customer service staff should see a different page from that seen by marketing staff. Showing a different page for each set of users could be achieved with the following entries in SiteMap:

<SubArea Id="nav_portalService" Title="Company Portal" Url="http://server/supportportal.htm" Icon="/_imgs/ico_18_home.gif"> <Privilege Entity=”incident” Privilege=”Create” /></SubArea><SubArea Id="nav_portalService" Title="Company Portal" Url="http://server/marketingportal.htm" Icon="/_imgs/ico_18_home.gif"> <Privilege Entity=”campaign” Privilege=”Create” /></SubArea>

This works providing that support users can create cases (schema name incident) but not campaigns, while the opposite applies to marketing users.

This approach can work well assuming suitable privileges can be found to distinguish between different groups of users, but what if there aren’t any suitable permissions ?

Creating dummy entities for permissions
The Privileges element can refer to custom entities, and there is nothing to stop us creating one or more entities solely for the purposes of personalising SiteMap. For example, we could do the following:

• Create an entity called dummy_security
• Grant one role, ‘Support Role’, Write privilege on this entity
• Grant another role, ‘Marketing Role’, Assign privilege on the entity
• Ensure users are added to the appropriate role
• Change the above snippet from SiteMap to the following:

<SubArea Id="nav_portalService" Title="Company Portal" Url="http://server/supportportal.htm" Icon="/_imgs/ico_18_home.gif"> <Privilege Entity=”dummy_security” Privilege=”Write” /></SubArea><SubArea Id="nav_portalService" Title="Company Portal" Url="http://server/marketingportal.htm" Icon="/_imgs/ico_18_home.gif"> <Privilege Entity=” dummy_security” Privilege=”Assign” /></SubArea>

This would give users the correct link, without relying on existing privileges that may change based on changing job roles, or changing business requirements.

The one thing to watch for is to make sure you don’t grant Read or Create privilege on this new entity; this will ensure it will not appear to users anywhere in the normal CRM application. Note that members of the System Administrator role will necessarily see all the SubAreas, because they will automatically have full privileges on all entities.

Redeploying callouts by recycling the application pool, rather than IISRESET

When redeploying a callout on CRM 3.0, it is considered necessary to run IISRESET to unload the callout assembly from memory, and hence allow you to copy a new version of the dll. This can have several issues:

1. After an IISRESET, it takes long enough to reload / rerender CRM data to be annoying when frequently testing and redeploying versions of a callout assembly
2. If other users are using CRM, there will be an outage during the IISRESET
3. IISRESET affects all other web applications on the server

To minimise the impact of 1) above, Michael Höhne's local debugging mechanism is an excellent solution in a lot of environments.

However, there are still the occasions when the callout has to be redeployed on to the CRM server. There's not much that can be about 2), although the following suggestion helps a bit.

We can do something about 3). A callout is loaded into the process for the CRM application pool (CrmAppPool), and it is only this application pool that needs to be recycled, rather than the whole of IIS. An application pool can be recycled via the IIS management console, but there is an easy way to script this, as follows:

1. Open a command prompt
2. Change directory to %Windir%\system32
3. Run the following command:

cscript iisapp.vbs /a CrmAppPool /r

This will just recycle the application pool, after which you can copy the new callout assembly

Syntax issues using SQL Execute As and Revert statements

SQL Server 2005 provides a means to switch user context via the EXECUTE AS statement. This can be very useful when querying CRM Filtered views, as they use the SQL user context to determine what data to return and if queried using the CRM service account they return no data. An example of how this can be used in CRM callouts can be found here. I've also used a similar approach when running SSIS packages programmatically from a web application, as ASP .Net impersonation doesn't work properly in SSIS packages.


However, when testing this further, and checking the behaviour of REVERT, I found this only works correctly if you separate your SQL statements with semi-colons. Take the following 2 examples:


Execute as user='crmdom\admin'
select * from filteredaccount
revert


declare @sql nvarchar(2000)
Execute as user='crmdom\admin'
set @sql = 'select * from filteredaccount'
exec (@sql)
revert


Of these 2, the former appears to work, but the REVERT statement doesn't get processed, and the latter gives an 'incorrect syntax' error. But, if you add a semi-colon after the statement before REVERT, then they work fine:


Execute as user='crmdom\admin'
select * from filteredaccount;
revert


declare @sql nvarchar(2000)
Execute as user='crmdom\admin'
set @sql = 'select * from filteredaccount'
exec (@sql);
revert


Now, that is not what I expected in SQL syntax. I always thought semi-colons were an optional statement separator, but apparently not -though I expect this is a bug rather than by design.


Note, this behaviour happens on build 2047 of SQL 2005, whereas it works without semi-colons on build 1399, but that doesn't stop me using them as a matter of course with impersonation.

Changes to client-side code for CRM 4.0

Following on from looking at server-side code changes for CRM 4.0, here's what I know so far about client-side code changes. Again, this is based on the CTP3 code so may soon become out-dated. Another caveat is that it is based on investigation of the CRM client files, and not on the SDK documentation (which at time of posting is incomplete); hence, some of what I cover may well be undocumented and hence unsupported.

Calling CRM web services directly
This has never been a favourite approach of mine, as the creation of the SOAP data in client-code seems too fragile; I prefer to create server-side web pages or services to abstract the CRM web service. However, I know many people who make direct SOAP requests, and there is another consideration - if customising a deployment on Windows Live you will not be permitted to deploy server web code, so you will have to make direct requests.

If you do make direct requests, you will need to pass the new CrmAuthenticationToken within the SOAP header. Rather than generating this yourself, there is a new global method for this:

GenerateAuthenticationHeader();

CRM 4.0 now provides a proper web service interface for offline use. This can be accessed from client-side code via the http://localhost:2525 location.

New Global Variables
CRM 4.0 provides the following documented global variables related to the multi-language and multi-tenant features:

• USER_LANGUAGE_CODE - Code (LCID) of the current user's language
• ORG_LANGUAGE_CODE - Code (LCID) of the base language
• ORG_UNIQUE_NAME - Current organisation name

New Object Properties and Methods
Note, everything in this section may be undocumented and hence unsupported. As you may know, the crmForm and field objects are implemented as HTML components (.htc files) in the /_forms/controls directory. A comparison between the CRM 3.0 and CRM 4.0 versions show the following changes:

All CRM 3.0 properties and methods seem intact, even the undocumented ones. I've not tested this exhaustively, but all current code works fine.

The crmForm object has the following new members:

• fireSaveEvent
• HideField - I hope this will become supported, as it will avoid direct DHTML manipulation
• VerifyFieldIsSet
• BypassValidation - a property to tell CRM not to validate fields

The Lookup control has more members, mostly to do with the new auto-resolve functionality:

• AutoResolve and ResolveEmailAddress boolean properties
• AreValuesDifferent method allows comparison between the lookup contents and a supplied array of values

The Picklist control exposes the underlying properties of the select HTML element, SelectedIndex and SelectedOption.

Across these, and the undocumented members from CRM 3.0, what I see as most significant is the extent to which they will be supported in CRM 4.0. We shall see.

Changes to code to use CRM 4.0 web services

I've recently been rewriting some of our products for CRM 4.0, and here's a summary of the server-side code changes required when converting code from using the CRM 3.0 web services to the CRM 4.0 web services. Note that this is based on the CTP3 release of CRM 4.0, so may not be accurate for release code.

Use of the CRM 3.0 web service
CRM 4.0 retains the CRM 3.0 web service, along with an implementation of the CRM 3.0 microsoft.crm.platform.callout.base.dll. This allows you, as far as I can tell, to retain code written for CRM 3.0 and have it work with CRM 4.0. So far I've found 2 limitations to this:

1. If you use the CRM 3.0 Web Service, you will access the default organisation, and will not be able to use multiple organisations. You will also have to use AD authentication
2. A few of the CRM 3.0 Web Service methods have been deprecated and won't work. The main class of these are those used to manage workflow

If you look in the directory structure of CRM 4.0 you'll see the following:

mscrmservices\2006\CrmService.asmx (used for CRM 3.0 web service access)
mscrmservices\2007\CrmService.asmx (used for CRM 4.0 web service access)

However this post is mostly about the changes you need to make if you are targetting the CRM 4.0 we service.

The CrmDiscoveryService service
The first major differences with respect to the web services with CRM 4.0 is that there is a third web service, the CrmDiscoveryService. This is primarily used to enumerate the organisations in use and provide the connection information for them. This service will be necessary in a multi-tenanted environment; whether you need to use it in a single-organisation on-premise implementation is open for debate (which deserves a separate post).

Setting up the CrmService service
The first key difference is the use of the CrmAuthenticationToken property. This became necessary to accommodate multi-tenancy and the new authentication type. It also replaces the CallerIdValue property. With the Enterprise edition of CRM 4.0 you have to set the OrganizationName; I don't know yet if it will be required in the Professional edition.

So, combining the 2 services above, you have 2 ways to setup your CrmService instance. Both assume the variable Org contains the organisation name. First, without using the CrmDiscoveryService:

CrmService svc = new CrmService();
svc.Url = Server + "/MSCRMServices/2007/CrmService.asmx";
svc.Credentials = System.Net.CredentialCache.DefaultCredentials;
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0;
token.OrganizationName = Org;
svc.CrmAuthenticationTokenValue = token;

Alternatively, you can use the CrmDiscoveryService to retrieve the data required for the CrmService instance:

CrmDiscoveryService disco = new CrmDiscoveryService();
disco.Url = Server + "/MSCRMServices/2007/AD/CrmDiscoveryService.asmx";
disco.Credentials = CredentialCache.DefaultCredentials;
RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
RetrieveOrganizationsResponse orgResponse = (RetrieveOrganizationsResponse)disco.Execute(orgRequest);
foreach (OrganizationDetail orgdetail in orgResponse.OrganizationDetails)
{
 if (orgdetail.OrganizationName == Org)
 {
  CrmAuthenticationToken token = new CrmAuthenticationToken();
  token.AuthenticationType = 0;
  token.OrganizationName = Org;
  crmservice = new CrmService();
  crmservice.Credentials = CredentialCache.DefaultCredentials;
  crmservice.CrmAuthenticationTokenValue = token;
  crmservice.Url = orgdetail.CrmServiceUrl;
 }
}

This code uses AD authentication. To use another authentication type, change the url to the CrmDiscoveryService, and change the AuthenticationType property of the CrmAuthenticationToken.

Using the CrmService service
The good news is, almost everything stays the same as before, and there is no need to rewrite code, other than to pass in an Organisation name. However, if you serialise and deserialise entities you'll have to pay attention to the Xml namespaces, as some classes retain the CRM 3.0 Xml namespaces, and some have a new Xml namespace. For example, from the generated proxy code:

[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://schemas.microsoft.com/crm/2006/WebServices")]
public partial class DynamicEntity : BusinessEntity

[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://schemas.microsoft.com/crm/2007/WebServices")]
public partial class account : BusinessEntity

Fortunately for me, the DynamicEntity is only class that I regularly serialise, and the namespace hasn't changed.

Using the MetadataService service
Here, your code has to change, as this has moved over to the Request model with an Execute method. So, for example, the following CRM 3.0 code:

EntityMetadata em = mdSvc.RetrieveEntityMetadata(EntityName.account.ToString(), EntityFlags.IncludeAttributes);

will need to be rewritten as:

RetrieveEntityRequest req = new RetrieveEntityRequest();
req.LogicalName = EntityName.account.ToString();
req.RetrieveAsIfPublished = true;
req.EntityItems = EntityItems.IncludeAttributes;
RetrieveEntityResponse resp = (RetrieveEntityResponse) mdSvc.Execute(req);
EntityMetadata em = resp.EntityMetadata;

Which is longer, but does illustrate one new feature, the ability to choose between the published and the unpublished information.

Another big difference relates to the way labels are used, which comes from the multi-language features. Again, some CRM 3.0 code:

PicklistAttributeMetadata pam = mdSvc.RetrieveAttributeMetadata(EntityName.account.ToString(), "industrycode")
foreach(Option o in pam.Options)
 if (o.OptionValue == 1)
  Console.Writeline(o.Description);

Which becomes:

RetrieveAttributeRequest req = new RetrieveAttributeRequest();
req.EntityLogicalName = EntityName.account.ToString();
req.LogicalName = "industrycode";
req.RetrieveAsIfPublished = true;
RetrieveAttrbiuteResponse resp = (RetrieveEntityResponse) mdSvc.Execute(req);
PicklistAttributeMetadata pam = (PicklistAttributeMetadata) resp.AttributeMetadata;
foreach (Option o in pam.Options)
 if (o.Value.Value == 1)
  Console.Writeline(o.Label.UserLocLabel.Label);

The Option.Value property is now of type CrmNumber, and there is a Label property that allows access to all localised labels.

The MetadataService also now supports modification to be metadata, but as that's new functionality I'm not going to cover it here.

Using Microsoft assemblies
With CRM 3.0, the only Microsoft CRM .Net assembly you were permitted to reference was microsoft.crm.platform.callout.base.dll. With CRM 4.0, there are 2 assemblies you can reference:

• Microsoft.Crm.Sdk.dll
• Microsoft.Crm.SdkTypeProxy.dll

At the moment I've not investigated them fully, as they are not well documented, and subject to change prior to release. The main new feature though is the implementation code for a PropertyBag, which is a collection class that allows, among others, easy manipulation of the properties of a DynamicEntity:

Microsoft.Crm.Sdk.DynamicEntity de = new Microsoft.Crm.Sdk.DynamicEntity();
de.Name = EntityName.account.ToString();
de.Properties.Add(new StringProperty("name", "My Co"));
if (de.Properties.Contains("name"))
 Console.Writeline(de["name"].Value);

This uses a bit of helper code from the Sdk for a partial class for StringProperty with a constructor that takes 2 parameters.

Other changes
One minor annoyance is that both the CrmService and MetadataService classes have classes with the same name, e.g. AttributeType. This means you cannot be as indiscriminate with your use of using or imports statements as I like to be.

Retrieving Billable time and activity time for cases

When a case is resolved in CRM, the user can specify the amount of Billable Time spent on the case, and they can also see the total activity time spent on the case:


Both sets of information can be useful, and are necessarily stored in the CRM database. However, how you retrieve these figures is not well documented.

Where the data is stored
Data about the case is stored in 3 main entities (note that I'm using the schema name for the entities, and in the schema a case is called an incident):

incident: This is the main case entity, which stores the information you see on the main form, but does not include and of the duration information listed above. The statecode field identifies whether a case is open, resolved or cancelled.

incidentresolution: This is strictly a special type of activity, and stores the user entered data in the Case Resolution dialog. The 'Resolution Type' is stored in the statuscode field, and the user-entered 'Billable Time' is stored in the timespent field.

activitypointer: All activities related to a case have an activitypointer record. The duration of each closed activity is stored in the actualdurationminutes field.

There are a couple of additional complexities to the data storage:

1. It is possible for a case to have been resolved, then reactivated, and resolved again. This results in 2 incidentresolution records, so you need to identify the correct one. I always go by the most recent
2. The incidentresolution entity is a type of activity, hence has an associated activitypointer record. Fortunately, this record has no value for the actualdurationminutes, so doesn't affect any calculations of total activity time. However, you do have to be aware of this if you want a count of the number of activities

Retrieving the data
Case data is normally retrieved via reports, and hence SQL queries. The following query shows how to extract the correct Billable Time and Total Activity Time:

select i.incidentid, i.title, ir.actualend
, max(timespent) as BillableTime
, isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime
from filteredactivitypointer a
join filteredincident i
on a.regardingobjectid = i.incidentid
left outer join filteredincidentresolution ir
on i.incidentid = ir.incidentidand ir.statecode = 1 -- Resolved incident resolutions
and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1) -- Most recent ir activity
where i.statecode = 1 -- Resolved cases
group by i.incidentid, i.title, ir.actualend

A few comments on the query:

1. I'm only returning resolved cases in this example, but I've used an outer join to incident resolution to make it easy to change the i.statecode filter to include unresolved cases
2. I'm using isnull on the actualdurationminutes field to return 0 if there is no activity time
3. If you add any other fields from the case, or related entities (e.g. account), you'll need to add them to the group by clause as well as the select clause, or join to them in a separate query

Displaying the data in CRM
As the billable time and activity time are not stored in the incident entity, they cannot be displayed directly on the case form or in any views. To display this information in the case form, my preferred approach is to create a report using the above SQL query, and display this in an IFrame on the case form, using the techniques described in Writing Reports to display in IFrames

In CRM 3.0 there is no useful way to display this data in a view. In CRM 4.0 this should change, though it may not be as easy as you'd like; CRM 4.0 allows display of related data in a view, but it looks like this is only data from parent entities (e.g. the account to which a case belongs), not the summarised data from child entities that we're discussing here. However, the new PlugIn model allows intercepting of the view data, and should permit such display of child data with a bit of code. This is something I'll be posting on in the future, but it will have to wait till the CRM 4.0 SDK documentation is finalised.

Workflow problems on CRM 3.0 with .Net framework 2.0

If you have .Net framework 2.0 or higher it can adversely affect the Workflow engine. This can give one or more of the following problems:

1. Workflow rules do not run (errors can be found in the event log, pointing you at workflow monitor, but nothing useful can be found in workflow monitor)
2. Bulk import operations never go beyond pending, even though the workflow service is running
3. When starting Workflow Manager on the CRM server you get an error message 'Workflow Manager cannot start...configuration file is corrupted'
4. You cannot attach to the CrmWorkflowService to debug workflow assemblies with Visual Studio .Net 2003

This can be because the Crm Workflow Service is running with the 'wrong' version of the .Net Framework. To correct this, create a text file with the following (case-sensitive) text:

<configuration>
<startup>
<supportedRuntime version="v1.1.4322"/>
</startup>
</configuration>

Save the file as CrmWorkflowService.exe.config in the \Microsoft CRM\Server\bin directory. Then restart the CRM Workflow Service.

Using reports in IFrames and from ISV.Config

This blog is not the first one I've posted on; I've previously been invited to post on the Microsoft CRM Team blog, and I posted this article Writing Reports to display in IFrames and from ISV.Config

It seems unnecessary to repeat the post here, so I won't. However I will soon be posting some additional reports that build on this technique.

It has to start somewhere - an introduction

It seems rude not to start with an introduction, so here goes. I've been working with MS CRM as my main technical focus for over 3 years, and became a Microsoft CRM MVP in April 2007. My main online involvement with the 'CRM community' thus far has been in the CRM newsgroups and forums, mostly helping with individual issues. This is the next step...

There are several reasons for starting this blog, the main one is to give me more space to cover fuller solutions or ideas than can be covered in the forums. These will be biased towards MS CRM customisation and development, but will doubtless stray into related technologies - SQL, Reporting Services, SharePoint, SSIS and .Net development - as well as infrastructure issues as I find them.