Pogo69's Blog

December 21, 2012

Restricting Active Directory Domain Access in a Hosted CRM 2011 Deployment

Filed under: Cutting Code — pogo69 [Pat Janes] @ 10:21

Overview

Being primarily responsible for our hosted CRM 2011 environment, it has bothered me for some time that the ‘New Multiple Users’ button in CRM 2011 provides our hosted Users access to our Active Directory Domain structure.  This potentially includes our hosted client list AND the names of the Users in those Organisations.

multiple_users

In CRM 4.0, it was possible to update a setting to disallow the addition of Users in a specific Organisation.  Although an equivalent mechanism exists in CRM 2011, it is accessible only via a direct database update (I am the supported mechanism nazi around here, so I’d rather not go there) – even the Microsoft CRM SDK Team were unable to assist.

Light at the End of the Tunnel

An update to a post on the Dynamics CRM forums this morning pointed me in the direction of a supported mechanism to ensure that our hosted Users will have access only to the specific AD OU in which their CRM Users reside.  The post indicates how to affect such an update via direct database update:

http://social.microsoft.com/Forums/en-US/crm/thread/848ea8e6-3bd3-44d5-8363-cdb95a34c0d1

Support Mechanism to Set CRM Organisation Active Directory UserRootPath

The same can be achieved via the following supported code:

Microsoft.Xrm.Sdk.Deployment.DeploymentServiceClient deploymentClient = Microsoft.Xrm.Sdk.Deployment.Proxy.ProxyClientHelper.CreateClient(new Uri("http://<server>:<port>/XRMDeployment/2011/Deployment.svc"));
deploymentClient.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("<username>", "<password>", "<domain>");
// find org
var organizations = deploymentClient.RetrieveAll(Microsoft.Xrm.Sdk.Deployment.DeploymentEntityType.Organization);
var org = organizations.Where(o => o.Name.Equals("<orgname>")).SingleOrDefault();
// update UserRootPath setting
Microsoft.Xrm.Sdk.Deployment.ConfigurationEntity orgSettings = new Microsoft.Xrm.Sdk.Deployment.ConfigurationEntity
{
  Id = org.Id,
  LogicalName = "Organization"
};
orgSettings.Attributes = new Microsoft.Xrm.Sdk.Deployment.AttributeCollection();
orgSettings.Attributes.Add(new KeyValuePair<string, object>("UserRootPath", "LDAP://<domain>.<tld>/OU=<ou4>,OU=<ou3>,OU=<ou2>,OU=<ou1>,DC=<domain>,DC=<tld>"));
Microsoft.Xrm.Sdk.Deployment.UpdateAdvancedSettingsRequest reqUpdateSettings = new Microsoft.Xrm.Sdk.Deployment.UpdateAdvancedSettingsRequest
{
  Entity = orgSettings
};
Microsoft.Xrm.Sdk.Deployment.UpdateAdvancedSettingsResponse respUpdateSettings = (Microsoft.Xrm.Sdk.Deployment.UpdateAdvancedSettingsResponse)deploymentClient.Execute(reqUpdateSettings);

If you’re not sure what the distinguishedName of the target OU is, you can find it in the Active Directory Users and Computers tool:

Enable Advanced Features

AdvancedFeatures

 

Use Attribute Editor to Locate distinguishedName

distinguishedName

Thanks to Neil McD for the inspiration!

Advertisements

December 2, 2012

Adventures in Multi-Threaded CRM 2011 Client Development

Filed under: .NET, C#, CRM — pogo69 [Pat Janes] @ 12:52

Overview

I’ve recently completed development of a process to auto-magically batch import data on a weekly basis for a client.

I have no control over the format in which the data will be received; the client (or their technical representatives) have decided that I will receive the entire set of Active Associations (data representing a link between a Contact and Account) each import.  I will not, however, receive any indication of Inactive (that is, deactivated) associations; as such, the import process will “clean up” prior to each import, deleting all active associations and updating each affected Contact (setting a custom Status attribute to Inactive).

I will discuss the import process in another blog posting; the focus of this post is the “clean up”; specifically the development process of deleting and updating approximately 100,000 records (each) as efficiently as possible.

Early Bound Classes, Large Record Sets and Memory Consumption

The initial iteration of my clean up program, simply queried for a set of all Contacts that were currently set to CustomStatus == Active.  It then looped through each record and set them, one by one, to Inactive.  In pseudo-code:

var contactIds =
  (
    from c in xrm.ContactSet
    where c.new_status == 1
    select c.Id
  ).ToList();

foreach (Guid contactId in contactIds)
{
  Contact c = new Contact
  {
    Id = contactId,
    new_status = 2
  };

  xrm.Update(c);
}

This caused the console program to consume huge amounts of memory to the point that the program stopped working.  I thought it may have to do with the ServiceContext caching objects, so I turned caching off completely:

xrm.MergeOption = MergeOption.NoTracking;

It had no effect (on memory); so I started “batching” my updates.

Enter the ThreadPool

I decided to use System.Threading.ThreadPool to operate simultaneously on batches of records.  Initially, my code selected 1,000 records at a time:

var contactIds =
  (
    from c in xrm.ContactSet
    where c.new_status == 1
    select c.Id
  ).Take(1000).ToList();

foreach (Guid contactId in contactIds)
{
  ThreadPool.QueueUserWorkItem(id =>
  {
    Contact c = new Contact
    {
      Id = id,
      new_status = 2
    };

    xrm.Update(c);
  }, contactId);
}

This would run for a little while, before erroring.  Trawling through the CRM Trace Log files indicated that I was encountering Timeout errors.  WCF Services have a throttling mechanism that allows only a certain number of simultaneous connections.  So I throttled my client code:

ThreadPool.SetMaxThreads(10, 10);

The first parameter allows only 10 concurrently running threads in the ThreadPool.  All other requests must wait for a spare thread to become available, effectively throttling my multi-threaded client code.

But it still errored; this time, I started generating SQL Deadlock errors.  It was intermittent and random.  It appears that I still had threads doing multiple updates when the next batch of candidate Contacts was being selected.  So I had to separate the select and update operations by “waiting” for each batch of updates to complete before going back and selecting the next batch.

In order to wait for a batch of threads, I needed to set up an array of WaitObjects.  The maximum number of objects that can be waited on simultaneously is 64, so that became my batch size:

class Program
{
  #region Local Members
  private static int BATCH_SIZE = 64;
  #endregion

  #region Thread Worker Classes
  private class ContactUpdater
  {
    XrmServiceContext _xrm;
    Guid _contactId;
    ManualResetEvent _doneEvent;

    public ContactUpdater(XrmServiceContext xrm, Guid contactId, ManualResetEvent doneEvent)
    {
      this._contactId = contactId;
      this._doneEvent = doneEvent;
      this._xrm = xrm;
    }
    public void UpdateContact(Object o)
    {
      // de-activate contact
      Contact contact = new Contact
      {
        Id = this._contactId,
        new_Status = (int)Contactnew_Status.Inactive
      };

      // update
      this._xrm.Update(contact);

      // signal that we're done
      this._doneEvent.Set();
    }
  }
  #endregion

  static void Main(string[] args)
  {
    ThreadPool.SetMaxThreads(10, 10);

    using (XrmServiceContext xrm = new XrmServiceContext("Xrm"))
    {
      xrm.MergeOption = Microsoft.Xrm.Sdk.Client.MergeOption.NoTracking;

      // update ALL current active contacts
      while (true)
      {
        // retrieve batch
        List<Guid> contactIds =
          (
            from c in xrm.ContactSet
            where c.new_Status.GetValueOrDefault() == (int)Contactnew_Status.Active
            select c.Id
          ).Take(BATCH_SIZE).ToList();

        if (contactIds.Count == 0) { break; }

        ManualResetEvent[] doneEvents = new ManualResetEvent[contactIds.Count];

        // spawn processing threads
        for (int i = 0; i < contactIds.Count; i++)
        {
          ContactUpdater contactUpdater = new ContactUpdater(xrm, contactIds[i], (doneEvents[i] = new ManualResetEvent(false)));

          ThreadPool.QueueUserWorkItem(contactUpdater.UpdateContact);
        }

        // wait for all threads to complete before processing next batch
        WaitHandle.WaitAll(doneEvents);
      }
    }
  }
}

Conclusion

This provides a framework for the multi-threaded updating of a large number of CRM Entity Instances.  With a little bit of tinkering you could get it to fit most batch update situations.

You could probably also play with the ThreadPool maximum thread count; I haven’t, but 10 seemed like a conservatively good number.

December 1, 2012

CRM 2011 OData, REST and the QueryString

Filed under: CRM, Javascript — pogo69 [Pat Janes] @ 12:06

Introduction

As with most CRM developers, I have loved the introduction of the newly introduced OData endpoint in CRM 2011.  It provides a simple, yet highly powerful mechanism to perform basic CRUD operations against a CRM 2011 Organisation.

Zen, and the Art of OData Query Construction

What makes working with REST so easy from client applications (Javascript and Silverlight in the case of a CRM 2011 developer), is that operations are performed by constructing an appropriate address via an HTTP Request’s QueryString.

A corollary of the implementation mechanism of a “Restful” web service, is that we can “play” with our queries directly in Internet Explorer.  I did this recently (actually I do it all the time; but I digress) to demonstrate to a fellow developer how to construct an OData query; the following is a reconstruction of that demonstration.

The Endpoint

In order to address the OData Endpoint, you must navigate to the following address:

http://<OrganisationRoot>/XrmServices/2011/OrganizationData.svc

Authentication

NB: The OData endpoint is not supported for use outside of Javascript and Silverlight within the CRM deployment.  Thus, in order to address the OData endpoint from the Internet Explorer QueryString, you must first successfully authenticate with the CRM Organisation.

If you are connecting from within the internal network on which the CRM is deployed, it is likely that you will be able to connect automatically due to Windows Integrated Security.  Otherwise, you may first have to open CRM in another tab.

EntitySet

Navigation to the root of the OData endpoint results in a collection of EntitySets being returned.  The collection is a list of the Entity Types on which OData operations may be performed.

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<service xmlns="http://www.w3.org/2007/app" xmlns:app="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="http://<OrganisationRoot>/XRMServices/2011/OrganizationData.svc/">
    <workspace>
        <atom:title>Default</atom:title>
        <collection href="OpportunityCompetitorsSet">
            <atom:title>OpportunityCompetitorsSet</atom:title>
        </collection>
        <collection href="SystemFormSet">
            <atom:title>SystemFormSet</atom:title>
        </collection>
        <collection href="RoleSet">
            <atom:title>RoleSet</atom:title>
        </collection>
        ...
    </workspace>
</service>

OpportunitySet

The Query that we were building can be defined as follows:

“When was the most recently Won Opportunity attached to the current Account?”

So, we need to interrogate the OpportunitySet.  We do this by simply appending a forward-slash and the name of the EntitySet to the end of the address:

http://<OrganisationRoot>/XrmServices/2011/OrganizationData.svc/OpportunitySet

This will return a collection of every Opportunity in the system (with ALL of each Opportunity’s attributes):

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<feed xml:base="http://<OrganisationRoot>/XRMServices/2011/OrganizationData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">OpportunitySet</title>
  <id>http://<OrganisationRoot>/XrmServices/2011/OrganizationData.svc/OpportunitySet</id>
  <updated>2012-11-30T21:44:25Z</updated>
  <link rel="self" title="OpportunitySet" href="OpportunitySet" />
  <entry>
    <id>http://<OrganisationRoot>/XRMServices/2011/OrganizationData.svc/OpportunitySet(guid'9cdd7cfd-6a34-e211-bea2-00012e244939')</id>
    <title type="text">Test</title>
    <updated>2012-11-30T21:44:25Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="Opportunity" href="OpportunitySet(guid'9cdd7cfd-6a34-e211-bea2-00012e244939')" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/opportunitycompetitors_association" type="application/atom+xml;type=feed" title="opportunitycompetitors_association" href="OpportunitySet(guid'9cdd7cfd-6a34-e211-bea2-00012e244939')/opportunitycompetitors_association" />
    ...
    <category term="Microsoft.Crm.Sdk.Data.Services.Opportunity" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:ParticipatesInWorkflow m:type="Edm.Boolean">false</d:ParticipatesInWorkflow>
        <d:new_IsParentOngoingOpp m:type="Edm.Boolean">false</d:new_IsParentOngoingOpp>
        <d:TimeZoneRuleVersionNumber m:type="Edm.Int32" m:null="true" />
        ...
      </m:properties>
    </content>
  </entry>
</feed>

<entry>

Each <entry> node represents an Entity instance; in this case, an Opportunity.

<link>

The <link> nodes beneath <entry> represent child Entity Collections attached to the individual Entity instance.

<content><m:properties><d:AttributeSchemaName /></m:properties></content>

Each property is represented by a child node of <content><m:properties>.  The attribute nodes are named with the SchemaName of the attribute as defined in your CRM Customisations; the data type is represented by the m:type attribute.

OData Operators

$filter

The $filter operator provides a mechanism to “filter” the returned result set; much like the where clause in SQL.

When was the most recently Won Opportunity attached to the current Account?”

So, we need to return only Won Opportunities for the current Account.  Thus, our filter will look like the following:

/OpportunitySet?$filter=StatusCode/Value eq 3 and CustomerId/Id eq (guid’00000000-0000-0000-0000-000000000000′)

In other words, return all Opportunities with a StatusCode of 3 (Won) and a CustomerId GUID identifier matching that specified.  See the CRM SDK and the OData Specification for additional details regarding the $filter syntax.

$top and $orderby

When was the most recently Won Opportunity attached to the current Account?”

We need the most recently Won Opportunity.  For the purposes of our query, most recently Won is defined as the most recent Actual Close Date.  Thus we need to order our result set by Actual Close Date descending; and return only the top 1 entry:

/OpportunitySet?$orderby=ActualCloseDate desc&$top=1

$select

When was the most recently Won Opportunity attached to the current Account?”

In order to determine when the most recently Won Opportunity occurred, we only care about the Actual Close Date.  With the $select operator, we can choose which attributes get returned in the result set:

/OpportunitySet?$select=ActualCloseDate

Consolidated Query

If we put the query operators together, we end up with:

/OpportunitySet?$filter=StatusCode/Value eq 3 and CustomerId/Id eq (guid’00000000-0000-0000-0000-000000000000′)&$top=1&$orderby=ActualCloseDate desc&$select=ActualCloseDate

The result set will look something like:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<feed xml:base="http://dev.mrcrm.com.au:8888/MrCRM/XRMServices/2011/OrganizationData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">OpportunitySet</title>
  <id>http://dev.mrcrm.com.au:8888/MrCRM/XrmServices/2011/OrganizationData.svc/OpportunitySet</id>
  <updated>2012-12-01T01:29:33Z</updated>
  <link rel="self" title="OpportunitySet" href="OpportunitySet" />
  <entry>
    <id>http://dev.mrcrm.com.au:8888/MrCRM/XRMServices/2011/OrganizationData.svc/OpportunitySet(guid'3b225dd4-28e3-e011-8abe-001517418134')</id>
    <title type="text">5 User Hosted</title>
    <updated>2012-12-01T01:29:33Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="Opportunity" href="OpportunitySet(guid'3b225dd4-28e3-e011-8abe-001517418134')" />
    <category term="Microsoft.Crm.Sdk.Data.Services.Opportunity" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:ActualCloseDate m:type="Edm.DateTime">2012-03-18T14:00:00Z</d:ActualCloseDate>
      </m:properties>
    </content>
  </entry>
</feed>

If the result set is empty, there were NO Won Opportunities attached to the current Account.  Otherwise, the <d:ActualCloseDate /> node contains our answer.

Javascript – How to Use our Query

In the interest of completeness, I will quickly show how to use our query from a Javascript Web Resource (code assumes the existence and availability of the JSON and jQuery libraries.

var serverURL = Xrm.Page.context.getServerUrl();
var accountId = Xrm.Page.data.entity.getId().replace(/[\{\}]/, "");
var query = serverURL + "/OpportunitySet?$filter=StatusCode/Value eq 3 and CustomerId/Id eq (guid'" + accountId + "')&
$top=1&$orderby=ActualCloseDate desc&$select=ActualCloseDate";
$.ajax({
  type: "GET",
  contentType: "application/json; charset=utf-8",
  datatype: "json",
  url: query,
  beforeSend: function (XMLHttpRequest) {
    XMLHttpRequest.setRequestHeader("Accept", "application/json");
  },
  success: function (data, textStatus, XmlHttpRequest) {
    var results = data.d.results;

    if (results.length == 0) {
      alert("No Won Opportunities found!!");
    }
    else {
      var dateResult = results[0].ActualCloseDate;
    }
  },
  error: function (XmlHttpRequest, textStatus, errorThrown) {
    alert('OData Select Failed: ' + odataSelect);
  }
});

See the SDK for more information on the idiosyncrasies of date processing from OData:

http://msdn.microsoft.com/en-us/library/gg328025.aspx#BKMK_WorkingWithDates

SSRS Reports in CRM 2011 with SQL Server 2008 (pre-R2) – Implications for Managed Solutions

Filed under: Cutting Code — pogo69 [Pat Janes] @ 05:53

Overview

I am writing this blog posting as a warning to all regarding the deployment of SSRS Reports into a CRM 2011 Organisation wherein the SQL Server instance is pre-version 2008 R2.

SSRS 2008 “Compatibility” in BIDS

If your SSRS Report is created in SQL Server 2008 R2 BIDS (while CRM 2011 supports the use of SQL Server 2008; most CRM 2011 deployments are, to my knowledge and certainly in my experience, running SQL Server 2008 R2), you will have an option when creating the Report to set “compatibility” to:

  • SQL 2008 R2
  • SQL 2008
  • SQL 2005

This forces BIDS to store your report using the corresponding XML Schema.

You could reasonably assume that selecting SQL 2008 would produce a report that will upload without error into a SQL Server 2008 Reporting Service deployment.  Unfortunately, if you add a Chart to your SSRS Report, such is not the case.  Uploading the report will fail with an “index out of bounds” error.

Implications

I first discovered this issue while attempting to import the PowerMailChimp solution into a client’s CRM 2011 Organisation.  The client has on OnPremise installation using SQL Server 2008 (pre-R2).

I checked (and re-checked) the schema definition in the offending Report (2008) and that the Report conformed to the SSRS 2008 schema (it did).  But it still would not import.  If I removed the report from the solution; the solution imported without error.

After a very frustrating session of Googling and experimentation, I confirmed and reliably reproduced the above error.  If you create a Report in BIDS 2008 R2, setting compatibility to SQL 2008 is not sufficient to ensure true compatibility if your Report contains a Chart.

Conclusion and Recommendation

If you are packaging a Managed Solution for distribution to an arbitrary CRM 2011 deployment that contains SSRS Reports, you must either:

  1. Create your Reports in an SSRS 2008 (pre-R2) deployment

Highlight to potential Users that the solution is compatible only with SQL Server 2008 R2+

Blog at WordPress.com.