Dealing with record level access restriction with Dynamics CRM

CRM works extremely well with giving users access to records based on a different set of security configuration tools : cumulative security roles with a “most permissive win” mechanism, business units, team memberships, access teams, hierarchy and so on. One thing it does not do well is restricting access to specific record. Imagine the following scenario:

  • Company ABC uses Microsoft Dynamics CRM to manage its cases
  • An employee leaves company XYZ to join ABC
  • After joining ABC, the new employee is given access to Dynamics CRM and has access to ongoing and past cases, say in his/her region
  • Company ABC has open cases against firm XYZ. The employee should not be able to see those cases because it represents a conflict of interest (e.g. he/she might give insights to his/her old friends at XYZ)

The following image provides an illustration of what we are trying to accomplish.

image

If you have worked with Dynamics CRM and know its security model, you can already read between the lines here. There are no easy ways to make this happen. This article provides a possible solution for this requirement based on a recent experience on a large CRM implementation.

Ruling out Team Membership & Ownership

In our scenario, members of a team or a business unit get access to all records  owned by their team or business unit. If we follow basic team/BU record ownership, the problem in this case is that once a record needs to be isolated from one individual member of the team, one of two things needs to happen:

  1. The record owner has to be changed (it can no longer be the team everyone is a member of) and the record needs to be shared with users who can still see it, but not with the individual that is getting the restriction applied OR
  2. The individual who is getting a restriction applied has to be removed from the team and all the records owned by his/her team need to be shared with him/her except the one he/she is restricted to see.  This technique implies heavy maintenance since the user will need to access new cases created for the team he/she used to be a member of.

Sharing in both cases can be done via the record share functionality, access team or any other mechanism that suits the business need. It should also be automated if there is a large number of records to share. You also need to save the information about the user and its restriction on a separate entity to be able to backtrack and understand why the records/ownership and so on have been modified.

Why we didn’t like the “Sharing” functionality

Using the out of the box sharing of record can cause problems in the long run. The PrincipalObjectAccess (POA) table gets bigger and bigger, causes performance issues and has to be cleaned up on a regular basis. In addition to that, the more access is controlled by sharing, the more you run the risk of having performance issues because of the complexity overhead in system lookups when it needs to display a record or list of records to users.

A solution using “Access Teams”

We decided to go with a design centered around Access Teams. The “Case” entity gets an “Access Team Template.” When a user is created and added to his/her group (team or business unit), we start a process to insert the user in the Access Team of each case related to his/her business unit. In order to restrict access to a specific case for a user, we can simply remove the user from the case’s Access Team.  For tracking purposes, we created a “Security Exception” entity that is created and has a relationship with a case and a user and an additional attribute that indicate the type and reason for the “Restriction”. To apply the access restriction on a case for a user (i.e. remove user from access team), we create a Security Exception record. We link the target case and user, and specify the restriction type. After the Security Exception record is created, a plugin is fired to perform validation and to remove the user from the case’s access team.

One of the reasons with went with that model was that we didn’t want to have a base security model that gets “broken” every time there a single person with restricted access, by changing record ownership or removing user from the team he/she is supposed to be a member of. With access teams, the record ownership remains the same, and users also remain on their team at all times which makes everybody happy and maintenance easier. The downside is that when a user is created, we have to look for all the cases in his/her business unit and manually (with code) add the user to each access team. Same when a case is created, users member of the case’s business unit have to be added to the case’s access team manually (also with code). That is the comprise we had to make.

As far as performance goes, the end results remain to be seen in the long run. Our system will have between 2000 and 3000 users when we complete our rollout. It will deal with over 1 million cases across multiple teams. Preliminary performance tests results look promising. I will repost if we get into any issue as it relates to performance.

It’s been a while! This feels great Smile  – Happy CRM’ing !

Synchronize CRM Online Data with Skyvia

I just discovered an interesting tool, thought I’d share a quick review.

Skyvia is a cloud data integration solution. It has connectors to a few data sources and is able to move data from one cloud based source to another based on configuration and schedule. Oh I forgot to mention, it’s FREE J

As a test, I configured it to connect to my Dynamics CRM Online sandbox organization and my Zoho CRM organization:

Once my connections are created and validated, I create and design a package to transfer contacts from Dynamics CRM Online to Zoho CRM:

As you can see on the screenshot above, Skyvia gives you the ability to create synchronization tasks (1) and also to schedule the execution your packages (2). The package can also be ran manually.

The tool can be very handy especially if you have simple synchronization needs between CRM and other systems. The supported data sources are Dynamics CRM Online, MySQL, PostgreSQL, SQL Server, Salesforce.com, Zoho CRM, Sugar CRM. Obviously such a tool does not replace the need for more complete and mature data integration products such as Scribe and SSIS connectors, but it provides an easy way to achieve simple data integration between multiple cloud based systems. Works perfectly for me demos! And it’s free (at least for now).

Dynamics CRM and Address Validation

Last year I was involved in a couple of CRM projects for which we had heavy requirements for address validation. The main scenarios that we encountered are the following:

  • All existing addresses must be validated and/or corrected (existing records)
  • New addresses saved in the system should be validated and/or corrected (synchronous validation)
  • When an address is entered, there should be an auto-complete functionality to help users enter the correct address

That was content for great research. First I want to share some facts about what you usually get with address validation solutions and then I will talk about the different solutions we used for the scenarios mentioned above.

About Address Validation Tools

An address validation solution is a tool that helps make sure an address is valid according to a standard (post service, government, other). It does so by taking an address as input, parsing it, comparing it to addresses in a proprietary database using match algorithms and returning a corrected/validated when it is found. There are plenty of address validation tools out there. Most of them have some common points.

First, they are sold as web services (cloud based or on premise) or as local APIs (.NET Libraries). If you are going with a local installation, it usually includes one or more address databases along with a web service. That means you will require little space on a server to install your address validation tool. Most vendors also offer a batch address validation/correction module.

Second, most of them function the same way: you send an address as an input and you get the validated/corrected address as an output. When an address is corrected, you get the changes that have been made (e.g. postal code changed, street type added etc.). In the same logic, when an address is not validated, you also get a validation status and sometimes a reason why the address is not validated. The batch processes usually provide a way to upload an address spreadsheet and get an output spreadsheet with the validation results.

Lastly, the pricing models are similar. You get can buy a certain amount of transactions (monthly, yearly or other). The price will vary based on the set of tools selected.

Obviously, there are other things to take into account. Some vendors only offer validation for specific countries and regions, some provide more than just address validation, for example address standardization, geocoding, multi-language support and more.

  • Correcting Existing Addresses

There are many ways to tackle this. The easier way is to use the validator’s batch processing module. When using Dynamics CRM, you can easily export your address using the “Export to re-import” functionality as shown here. Once you have your spreadsheet, you can easily import it to your address validation tool and get the corrected rows back into your CRM. There are two issues with this approach:

  • There is the 10000 records limit when you export data to Excel spreadsheets. If you have more rows to correct, then you need to consider an alternative solution
  • When an address is sent for validation, you may want to indicate a validation status in CRM which you can’t do by simply reimporting the corrected rows

In our case, we decided to write an application that reads all the rows for which we need to validate an address. For each row, a web service call is made to the address validation service provider, once we get the result, we store the corrected address as well as the correction details. When an address is not corrected, we store the details (if available) and we send the record through a manual processing for someone to perform the validation manually.

  • Address must be validated when saved to CRM

If you are adding this feature when building your system (before there is any address in your CRM), then you should never need the logic described in the point above.

The design here is very simple:

  1. Create a plugin on the address save or update (could be a custom entity or OOB such as Account or Contact)
  2. The plugin calls the address validation web service
  3. The plugin handles the business logic that is executed post validation (e.g. overwrite input address or creates a manual validation activity/task linked to the address being processed

Keep in mind that in point b, we call an external web service. This design works well for on premise installations. For CRM Online, you may not be able to reach those external web services if there are not hosted in Azure.

  • Address Auto-Complete Feature

For this client request, my colleague had experience with a CRM Add-On that does just that. It is called PostCodeAnywhere. It is a light weight solution for CRM (it only contains a few web resources). Here are a few screenshots.

It is worth noting that they support a lot of different countries so it makes it a very handy tool if you work for a company that stores international addresses. You can configure it to work on OOB address fields as well as custom fields.

I will also mention that using this tool is great to help system users entering correct addresses. However, there is no way to know (from an auditing perspective) that an address was entered using the add-on. Anyone could overwrite it manually.

A few tools

Here are some of the tools that we tried, used and/or tested for address validation:

  • Service Object : Great overall experience. The product is very good and the support and sales team are very responsive, efficient and flexible. They offer validation web services for US and Canada only.
  • Melissa Data : The product list is impressive (on prem, hosted web services, SSIS integrated tools etc.). They offer a global address validation web service.
  • nCode : The product has a lot of search algorithms for address validation. It comes as an on premise web service installation. It only support Canadian addresses.

The Google MAP and Bing APIs are also worth taking a look at.

Have fun!

Dynamics CRM Archiving Solutions Revisited

I wrote about Archiving solutions for Microsoft Dynamics CRM about 2 years ago. I still get a lot of questions from the article and from customers in the field about what they should do when it comes to archiving their Dynamics CRM data. While there is no obvious answer, my generic approach is to make sure my clients understand that they shouldn’t try to build an archiving solution before their system has become slower. They should see it more as an opportunity to perform a thorough review of their implementation and isolate the bottleneck(s) and pain point(s) and see if those can be fixed.

A few months ago, I gave a presentation on the topic that you can see below. I go from the reasons to archive data to recommendations. Hope this helps!

Dynamics CRM Solution Architecture

Hello readers!

I just completed a series of very animated presentations on Solution Architecture for Dynamics CRM in a few different cities in the east of Canada. I thought I would share the slide deck.

As a summary, I complied a few thoughts about the CRM Solution Architect’s role and typical design consideration. Enjoy the content and feel free to reach out to me with questions and comments.

CRM 2013 – Leveraging Actions to get around JavaScript cross-domain challenges

Last year, I wrote about the challenge of cross-domain calls from JavaScript with CRM 2011. The issue was related to fact that from a security perspective, you could not have JavaScript functions executing on a CRM form event or when a ribbon button is clicked calling web services outside of the CRM domain. I proposed a few workarounds here but the bottom line is that in all cases, there was some sort of a negative impact in each solution. With CRM 2013, actions processes are a great way to get around the browsers’ cross domain restriction.

In this article, I am providing an example of how actions can be used to make a request to a web service outside of the CRM domain from a record’s form event.

Scenario

When users call an incident management center, the agents need to capture the temperature at the time of the incident in the city where it occurred. In order to do so, we decide to create a button on the command bar that agents can click on and that will perform the following tasks:

  • Read the City and Country information from the form
  • Call an Action that
    • Takes the City and Country as parameters
    • Use an external web service to get the temperature in the city
    • Returns the temperature as output parameters
  • Sets the temperature field value on the form

Note: In order to achieve this directly with JavaScript, performing Step 2 of the action would require to make a cross-domain call from JavaScript.

Process Configuration

To start things off, let’s start be creating an Action of type process. This action will have 2 input values (City and Country) and 2 output values (Temperature in Fahrenheit and in Celsius).

The steps are really simple. The action needs to a custom workflow activity that will connect to a weather web service and return the temperature in two output values of its own.

Custom Workflow Activity

Here you can see what the custom workflow activity code looks like. It’s very straight forward in that it only has 3 steps:

  1. Read the city and country inputs
  2. Initialize the web service client object and make the web service call
  3. Set the values back in the output fields to be available in the process

Calling Action from JavaScript

This is the last piece of the puzzle. At this point, we simply need to call the action created from a JavaScript event (ribbon button clicked, on change of a value etc.). In order to achieve this, create a function that is called when the client even occurs. That function should execute the Action, read the output values and set the field values on the form. This has to be done with a SOAP call. There are two ways to do this. You can:

  • use Deepak’s example as show in his blog post here or
  • use the CRM 2013 Sdk.Soap.js action message generator published by the Microsoft CRM SDK team to generate a request and response class you can use in your call to call the action using JavaScript.

Wrap up

This is another great example of how actions can be leveraged to work around challenges that we’ve dealt with in the pre-CRM 2013 era. Keep in mind that in order to call an external web service from the custom workflow activity, there are server security elements to take into consideration (e.g. can my server talk to the web service? etc.)

Hope this helps!

Dynamics CRM and multi-tenancy

It’s a topic that is often discussed and after Microsoft released its whitepaper on it late last year, I thought it would be interesting to write a short summary of some of the enterprise challenges that it addresses.

Definition

Let’s take a look at a few definitions of the term “Multitenancy

Multitenancy refers to a principle in software architecture where a single instance of the software runs on a server, serving multiple client-organizations (tenants).” – Wikipedia

Multi-tenancy is an architecture in which a single instance of a software application serves multiple customers. Each customer is called a tenant. Tenants may be given the ability to customize some parts of the application, such as color of the user interface (UI) or business rules, but they cannot customize the application’s code.” – WhatIs.com

Multitenancy is a reference to the mode of operation of software where multiple independent instances of one or multiple applications operate in a shared environment. The instances (tenants) are logically isolated, but physically integrated” – Gartner IT Glossary

Relating these definitions to Microsoft Dynamics CRM, we can view the CRM Server installation as the single instance of the application and the CRM Organizations created on the CRM server as tenants. The tenants are technical groupings within the application, such as separate databases within SQL Server.

Common Enterprise Challenges and Solutions based on Multi-tenancy

Function Localization

  • Description: Provide a common core of functionality, but with local functional variations – with “proper” delegation
  • Example: Multi-national company with business models that vary based on market size, legal compliance or other factors
  • Solution: Using multi-tenancy or multiple instances allows each business area or local region to have an independent implementation with its own local variations.

Master data management

  • Description: Provide a consistent, managed core of information, perhaps distributed only Selectively and enable smooth transitions without big bang replacements
  • Example: You may have an organization that operates in multiple areas and the terms/rules differ from one place to another. These different areas may need to manage their master data independently from one another. In these types of scenarios, it is important to maintain the data that is common across the different components and particularly critical is managing changes to that data. While there are different approaches for accomplishing this goal, many scenarios benefit by having a “master” for certain data sets because it provides for change management through that central master data source (master data management, or MDM)
  • Solution: This approach requires that the central master data be synchronized to all instances so that each instance has access to the latest version of the core information

Physical distribution

  • Description: Mitigate network latency
  • Example: For business solutions that support users that are physically distributed over large distances (global deployments), using a single instance may not be suitable because of the implications (such as WAN latency) associated with the infrastructure over which the users connect.
  • Solution: Distributing instances to provide users with more local access can reduce or overcome WAN-related issues, as the access occurs over shorter network connections

Security/privacy

  • Description: Accommodate legislative/national differences (e.g. patient confidentiality, Swiss banking, third-party use)
  • Example: This is usually a resulting of some sort of legal compliance. For example in Canada, healthcare patient information cannot be transferred from one province to another. If there was a national platform, there would have to be a way to prevent people from access data from patients living in other provinces.
  • Solution: In these types of scenarios, some or all of the data is stored locally, and potentially some of the data is stored centrally

Scalability

  • Description: Accommodate extreme volumes and/or extensive use of Service Scheduling; Provide for isolation of workloads (e.g. web site, customer kiosks)
  • Example: While a single instance of Microsoft Dynamics CRM can scale up and out to support the growth of a customer’s business, with very high data volumes or levels of complexity, there are additional considerations.
  • Solution: for scenarios in which groups of users work independently of each other in operational terms, it may be possible to host the groups on separate Microsoft Dynamics CRM instances and to use reporting to combine results across business areas for management oversight

Multi-tenancy Challenges and Solutions Patterns

The whitepapers has a full detailed section on possible solutions to the challenges introduced by multi-tenancy. These challenges come from the fact that the separation has to be managed. You may want to synchronize your metadata, or expose data from on CRM organization within a different organization.

SSIS Tools for Dynamics CRM

ETL is an everyday subject in medium to large enterprise ERP or CRM project implementations. When it comes to Dynamics CRM, there are many different ways to insert, update and delete data. Using Microsoft’s SQL Server Integration Services (SSIS) is a good solution because of its cost (included in the SQL Server suite), development environment familiarity (Visual Studio, SQL Server Management Studio).

The problem with Dynamics CRM in this context is that its database is not supposed to be accessed directly to read, create, modify or delete data (read operations are OK using Filtered Views). These operations must be done via the application’s API. Fortunately, there are third party tools that provide a “bridge” between SSIS and CRM through custom SSIS components. These tools transform their SSIS components into MS CRM API calls in the background, allowing to perform ETL by using Microsoft’s recommended and supported approach.

Here are some tools that I’ve seen or heard of that will help do your MSCRM data processing with SSIS:

  • Kingsway Soft’s SSIS Integration Toolkit for Microsoft Dynamics CRM
    • Notes
      • Supports CRM 3.0, 4.0, 2011, 2013 and Online
      • Comes with 4 major components: Connection Manger, Source Component, Destination Component, Option Set Mapping Component
    • Pricing Information
      • Free Developer License
      • Single Server Perpetual License @ USD $1295
      • Single Server One Year License @ USD $695
      • Single Server Annual Maintenance and Upgrade @ USD $295
      • Enterprise License – Contact for details
  • RSSBus‘s Dynamics CRM SSIS Components
    • Notes
      • Supports CRM 4.0, 2011 and Online (presumably 2013 as well but no official word on that on their website)
      • Free 30 day trial available
    • Pricing Information
      • Full SSIS Component Subscription (multiple workstation, Royalty-Free distrubiton) @ USD $999
      • Single Machine License @ USD $359
  • Team4 SSIS Connector for Microsoft CRM
    • Notes
      • Supports CRM 4.0, 2011
      • It looks like the product hasn’t been updated in a while given the lack of update on the website
    • Pricing Information
      • Connector is Licenses per server @ EUR 4600
  • CozyRoc Dynamics CRM Source and Destination Components
    • Notes
      • Mature product
      • The web site doesn’t mention support for CRM 2013 (it is supported though)
    • Pricing Information
      • Varies based on the Components you buy
      • Between $400 and $2500
  • Devart’s SSIS Data FlowDevart’s SSIS Data Flow Components
    • Notes
      • Devart has a large portfolio of data integration products
    • Pricing Information
      • Single License @ USD $249.99

 

Cheers!

Silverlight, HTML5 and Dynamics CRM

The Glory Days of Silverlight

Silverlight is Microsoft’s plugin for web-browsers that enables running rich Internet applications, with features and purposes similar to those of Adobe Flash such as multimedia, graphics, animations etc. Shortly after Silverlight was introduced to the market in 2007, Microsoft quickly started to build knowledge around how to write and deploy rich applications and integrate them with Microsoft Dynamics CRM starting at version 4.0.

When you thinking about it, the need for custom UI integration with Dynamics CRM has always been there since the earlier versions. As CRM solution providers, most of us have been in situations where we can come up with a complex data model and we feel like the out of the box UI capabilities won’t be user friendly enough to drive user adoption. Situations like that created the need to build more user friendly interfaces to simply make people’s lives easier when they start using the CRM/XRM application.

To better illustrate, here are a few examples of when we would want to write a custom UI components in CRM

  • Display a tree view of related records with parent/child relationship.
  • Display complex search results: I’m thinking about PowerSearch which is a Global Search add-on for CRM. If you want to search for multiple entity types at once, a custom UI is required to display all results on a single page/view
  • Display timesheets, Gantt project management charts in Professional Services Automation solutions such as Assistance PSA and XRM1 (view screenshots below)

The Decline of Silverlight and the Rise of HTML5

With the emergence of HTML5, it seems we are headed towards a future in which browsers will support HTML5 tags natively thus enable rich content without the need of plugins like Silverlight or Adobe Flash. If some of us as individuals don’t believe this is true, Microsoft and Adobe seem to believe it is since they both dropped or significantly slowed down the evolution of their platforms. Silverlight’s latest major release (version 5) came out in 2011 in a world in which we see companies releasing software solutions at a very fast pace. The emergence of HTML5 have been well documented over the past year. There are still a lot of skeptics out there and that is understandable given how long it’s taking for the HTML5 standard to be completely defined and made available in all browsers.

Dynamics CRM: HTML5/JavaScript or Silverlight

What does this all mean for us Dynamics CRM integrators? The need to have custom UI controls is still existent and it will not go away even with all the new flexibility that we get with CRM 2013. Some data models will always be complex enough to require a better UI to give the solution its best chance of being used. In addition to that, there are still plenty of CRM add-ons built by Microsoft Partners that still use Silverlight 5 as a key piece of their solution. Below is a decision matrix that I came up with for us CRM Solutions providers going forward in making a technology decision when building new UI pieces for MSCRM.

What about the upgrade question? You have Silverlight controls and are wondering if you should built new controls in HTML5 and JavaScript. It’s your decision. Silverlight is not dead, Microsoft is still supporting it and it will for a long time. If you want to learn the new technology and have the time and money to do so, then go for the HTML5 remodeling. Keep in mind that it is a risk given that we have no idea what the lifespan of HTML5 will be.

What about buying an add-on that heavily relies on Silverlight controls? I don’t have a problem with that as long as it’s OK for you to install Silverlight on all client computers. Moving controls from Silverlight to HTML5/Javascript is A LOT of work and represents a significant amount of work for the add-on solution providers. They will upgrade when the time is right for them to do so (hopefully).

Start your CRM Development in Azure!

I attended a presentation by Microsoft TFS ALM MVP Wes MacDonald on Windows Azure  a couple of weeks ago. If you have some level of MSDN Subscription, Microsoft is giving away free $$$ for Windows Azure services.

Knowing that, I decided to use some of my free $$$ to do some CRM development in  Azure. The idea is to create a Virtual machine and just get a feel of the overall experience. To kick things off, we need to activate our Azure Benefits by logging onto our MSDN subscriber account. Navigate to “My Account” and you will see the list of Subscription Services you get based on your level. Click on “Activate Windows Azure” and go thought the activation process. It takes a few minutes.

After that, the rest of the steps are pretty standard. You need to create a VM. There are a couple of options when it comes to doing that.

  1. You prepare your own VM using HyperV and run SysPrep. When that step is completed, you can upload your VM to Azure and boot it…
  2. You can pick from a Template Virtual Machine. In my case, I selected Windows Server 2012 and SQL Server 2012 SP1 (saves me some time)

Once you have done all the steps to create your VM (with Template), you get a RDP file that enables you to connect to your Virtual Machine in Azure. From that point on, it’s standard CRM installation. If you are used to building CRM VMs for development on a local laptop or workstations, typical steps are installing Active Directory, promoting your machine to Domain Controller and installing CRM. It took me about an hour tops to complete the installation (without the development tools i.e. Visual Studio).

For the price and if you are always going to have internet access, it’s much easier to use an Azure Virtual machine for your development. Here are my arguments for doing it:

  • Powerful servers
  • Quick install, fast and easy access (you get to select the data center where you want to VMs to be… Select something close to your location, East US for myself)
  • Possibility to have a separate server for each installation components (SQL, CRM, AD, SharePoint etc…) at a very low cost which you can hardly do on VMs on your local machine
  • Low cost (good discount for MSDN account holders, plus you are only charged when your VMs are running)
  • Azure UI very simple and intuitive
  • Easy integration with Visual Studio 2012 and 2013
  • No need to maintain your own infrastructure and deal with network, hardware and software maintenance

After trying this, I’m leaning towards using moving all my personal development activities to Azure. The cost is low and controllable and it gives me flexibility when it comes to building a better server infrastructure for my CRM Development which I could never have if I work on my laptop (as powerful as it is). Also, it’s worth noting that I am only focusing on infrastructure here but Windows Azure enables you to do much more (Web hosting, storage/backup, mobile, media etc.). If you are looking for a cloud solution, you should take a hard look at Windows Azure!

Cheers