Friday, March 16, 2018

SQL Browser Service and Microsoft Dynamic CRM On-Premise

Recently one of my Client asked to provide remediation for different vulnerability issues for various hosted applications on different Internet facing servers.
Microsoft Dynamic CRM was one of the hosted application too and it so happens that few of the action steps which needs to be taken as a remediation for vulnerabilities issues has impacted CRM and we were forced to modify configuration in Microsoft Dynamic CRM web and associated database to bring CRM up and running and keeping its protected from any unknown attack.

I would like to share my learning with you all today!

Let’s understand SQL Browser Service and Issue associated

SQL browser service helps in providing information related to SQL Server instances installed on the computer.

It listens on port 1434/udp and accepts unauthenticated requests by using SQL Server Resolution Protocol (SSRP). When this feature is enabled and publicly accessible from the Internet, attackers may use this service to launch denial of service attacks (amplification attacks using forged UDP packets).

So, Network Team decided to turn OFF SQL browser service and we need to look for some alternate approach to have CRM accessible.

It is possible to install Dynamic CRM On-premises using SQL Server named instance?

Answer is YES, we can install CRM on SQL Server named instance. 

When I looked over internet, I was getting just to turn on SQL browser service to resolve named instance (without its hidden impact). I decided to play around on an extra CRM slot that we use for our RnD.

The Challenges

The moment you turn off SQL browser service and do any of below actions, CRM will not be accessible.
  • Recycle CRM Application Pool
  • Re-Start IIS (Internet Information Services) 
  • Re-Start CRM Application Server

Microsoft CRM Application without SQL Browser Service

We need to provide SQL port number to CRM, so it correctly points SQL Server Named instance during any of these actions:
  • Fresh CRM Installations – from CRM installation wizard.
  • Importing Existing Organization – from deployment manager.
  • Creating a New Organization – from deployment manager

So syntax would be     :           SQLSERVER\InstanceName,PortNumber

An Example                  :           CLUB-VIPINJA01\NO15,1433

Note: After forward slash (\) its a SQL instance name and after comma (,) its a SQL Port

Resolution steps when CRM is already installed

      1) Changes in the Registry of CRM Application Server – (regedit)

The Key path that need  to be changed

OLD Value
Data Source= CLUB-VIPINJA01;Initial Catalog=MSCRM_CONFIG;Connection Timeout=60;Integrated Security=SSPI

NEW Value
Data Source= CLUB-VIPINJA01\NO15,1433;Initial Catalog=MSCRM_CONFIG;Connection Timeout=60;Integrated Security=SSPI

2) Changes in SQL Server DB

Select * From MSCRM_CONFIG.dbo.Organization

Provider=SQLOLEDB;Data Source=NUEW-SQEKWNP01;Initial Catalog=Production_MSCRM;Integrated Security=SSPI

Update the Connection string using SQL query.

Update Organization
Set ConnectionString = 'Provider=SQLOLEDB;Data Source=CLUB-VIPINJA01\NO15,1433;Initial Catalog=Production_MSCRM;Integrated Security=SSPI'

We can verify if CRM web is still accessible even after restarting IIS or recycling the CRM application pool.

How to check the SQL Port Number of our SQL Server installed.

I hope information provided here is valuable.


Tuesday, March 6, 2018

Email reminders to a Contact associated to an Account need to be mange well in Dynamic 365 CRM.

I came across a very common and usual requirement one day.

A business would like to send notifications using Microsoft Dynamic CRM to the contact added on the Invoice entity on due of Invoice end date.

I guess workflows would be the best suitable option here to send an email notifications, probably using the template and adding dynamic content would add a personal touch and even who is not from a developer background can construct a beautiful workflow.

But let me tell you a simple workflow may sometimes can be a very nasty to handle and play around.

Let me outline an example and share my small tips and learning.

On Invoice entity we have following relationships:
  •         Company              : One-to-Many Relationship between Account and Invoice.
  •          Primary Contact    : One-to-Many Relationship between Contact and Invoice.
  •          Due Date              : Date when Invoice is due.

We customize Primary Contact field to allow only contact associated under Company.

Now we will create our workflow which would simply send reminder email to a contact on Invoice due date.

I am creating a very basic workflow and not handling complex logic for workflow getting multiple times when an invoice due date get updated, just to stay focus on showcasing a different situation.


We are done with creating our workflow and we have activate it.

Now, we would test it with some sample data.
We have a Account with two contacts in CRM.

We created an invoice with information as depicted in below diagram.

According to our provided business scenario we need to send an email notification to Mr. Vipin Jaiswal on Invoice Due date (31-Dec-2018).

But let assume here on 1st-Apr-2018 that is before Invoice due date is reached, Mr. Vipin Jaiswal is no longer a Primary Contact for Account Microsoft.

The changes had happened on Account entity and Vipin Jaiswal was removed from the Contact sub grid. But on the Invoice entity CRM is still referring to Vipin Jaiswal as his Primary contact.
Our workflows would still be sending an email to the contact which is no longer associated with the account which invoice is related to.

The business would be interested in sending email notification to the changed Primary contact of an account which is been reflected at the account entity level.

Typically we should always be sending such email notification to the current contact which is associated to the account and not the contact that is got recorded on the Invoice creation.


• Account and Primary contact are tightly coupled.
• At the time of Invoice creation, we select Account first and then we are allowed to select only related contact as a Primary Contact.
• A Primary Contact can move from one organization to other, account will remains the same.
• In this case, we should always send an email notification to the Primary Contact of the Account and not the Primary Contact of any Invoice.


Please let me know if someone came across such issue and resolutions steps taken towards it.

Monday, December 25, 2017

Cancel Workflow using JavaScript and Web API in Microsoft Dynamic CRM

It was just another casual task, when business scenario got me into cancelling a specific waiting workflow in Microsoft Dynamic CRM for some specific entity.

First, I googled it looking around if someone had written something about it. I was getting lot of articles to execute workflows, but I was looking to cancel the waiting workflows. I decided to go-ahead and use SQL to find the Guid of specific workflow instance, so that I can execute my code to try canceling it.

I got surprised when I found multiple records of my workflow, but then on CRM UI it was just one.

Then I took a closer look at the workflow table and try to differentiate them and finally I was able to write a query to filter out the one that I was interested in.

(If you are not seeing multiple, just activate and deactivate a workflow few times and it will generate multiple records of the workflow)

So how to find the Guid of exact workflow that would get triggered based on name only?

Select top 100 categoryname,name,workflowid,statecodename,statuscodename, parentworkflowid,parentworkflowidname 
From FilteredWorkflow
Where name like '%Hold on%'
And category = 0 -- Workflow
And statecode = 1
And parentworkflowid is not null

Rest API request would be like:

&$filter=StateCode/Value eq 1
and ParentWorkflowId/Id nq null
and Name eq \'' + workflowName + "\"

How to find the Async Job Id that get triggered and it’s associated to our workflow?

From the above query, we can find the workflow Id which is responsible for activating the instance of the workflow. That’s why it may have been named as Workflow Activation Id in AsyncOperation Table/Entity.

Now we use workflow id to filter out the instances, which we actually might be interested in.

&$filter=primaryentitytype eq 'invoice'
and operationtype eq 10
and  statecode eq 1
and ( statuscode eq 0 or  statuscode eq 20 or  statuscode eq 10)
and  _regardingobjectid_value eq 638F13B4-4AE9-E711-80DA-6C626DCF4746

and ( _workflowactivationid_value eq 98CA71E7-EEE6-E711-80D9-6C626DCF4746
      or _workflowactivationid_value eq 1AC0E180-39E9-E711-80DA-6C626DCF4746)

Finally, to cancel the workflow, here is a code.

function Cancel_Waiting_Workflows(asyncId)
       var entity = {};
       entity.statecode = 3;
       entity.statuscode = 32;

       var req = new XMLHttpRequest();"PATCH", Xrm.Page.context.getClientUrl() + "/api/data/v8.1/asyncoperations(" + asyncId + ")", false);
       req.setRequestHeader("OData-MaxVersion", "4.0");
       req.setRequestHeader("OData-Version", "4.0");
       req.setRequestHeader("Accept", "application/json");
       req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
       req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
       req.onreadystatechange = function() {
              if (this.readyState === 4) {
                     req.onreadystatechange = null;
                     if (this.status === 204) {
                           //Success - No Return Data - Do Something
                     } else {

It was great to explore in Dynamic CRM.

Keep Learning!!!

Sunday, October 15, 2017

Failed to import Business Process 'Lead to Opportunity Sales Process' in Microsoft Dynamic 365 CRM

I am here to explain one problem that I faced, when doing import of a solution in Microsoft Dynamic 356 CRM environment. I got an exception when I tried to import my managed solution from one Dynamic 356 CRM environment to other newly instantiated one.

Before I promote my solution to Production instance, I wanted to make sure that my solution is error free and there is no component dependency, which usually one have faced during import of a solution.

So I decided to make a new test environment and do testing there and if everything is working well, I can take further steps to move it to production environment.

I did some of the changes in existing standard business process flow like removing some of the fields and adding few as my client business requirements. When I exported the solution both in managed and un-managed formed, I did not receive any complaints from Dynamic CRM platform.

When I tried to import the managed solution to my new test environment, the import wizard complains about below listed error.

Failed to import Business Process 'Lead to Opportunity Sales Process' because solution does not include corresponding Business Process entity 'leadtoopportunitysalesprocess'.

Failed to import Business Process 'Opportunity Sales Process' because solution does not include corresponding Business Process entity 'opportunitysalesprocess'.


We need to add corresponding entity to our solution, as we did some changes in Standard out the box business process.
  • These corresponding entities do not appear under standard entity list.
  • They also don’t get added when you may try to add all required components.
  •  In fact, I found them under 1:N relationship of respective entity.

Here is a screen shot of an Opportunity Sales Process as a Related entity which appear in 1:N relationship of Opportunity entity.

Similarly, we can add Lead to opportunity Sales Process entity under 1:N relationship of Lead entity. Once we do this, we can see both entities which were missing in above exception are listed in entities list of solution.

After adding these entities, my import customization went successful.

Friday, February 10, 2017

How to create Business rules in Microsoft Dynamic CRM 2016.

Dynamics CRM 2016 is here and with it comes some exciting new features. Microsoft have update Business rule editor with drag and drop features. Let’s try to think of a scenario and use this new enhanced feature.

Business Scenario :

On quote entity, we have need to display Primary contact based on account selected in Potential Customer. If Potential Customer is not selected then we need to lock the Primary Contact.

Solution :

1) Create a One to many relationship between Contact and Quote.

2) Change the field property to display only related records.

3) Create a business rule to Lock the Primary contact until valid Potential Customer not populated by user.

The editor also show us the tips that we are dragging control to valid area. After attaching the Action Control, we are then supposed to populate its properties.

After locking the primary contact, we then clear the value if any in Primary Contact.

We then unlock primary contact if valid potential customer provided by having condition branch.

We then save and activate the Business Rule. CRM does a validation check and if there are no error it display a “Validation successful” messages.

I hope you find this Microsoft Dynamic CRM feature interesting and ease of use.

Vipin Jaiswal

Wednesday, December 14, 2016

Lookup is slow in Microsoft Dynamic CRM.

Problem Statement :

Lookup is unresponsive and painfully slow in Dynamic CRM.

Objective :

To identify the cause which is making Location lookup to be unresponsive.

Lookup in Dynamic CRM

A lookup represent parent child relationship between two entities.
For an example :
An Account (Organization) can have many contacts associated.
CRM by default have 1-Many relationship between account and a contact.

What SQL Index has to do with Lookup

Purpose of Indexes is to fine tune search operation in a table/entity.
In Dynamic CRM each table/entity have a Primary key which becomes a unique clustered index.
One table can have only one Unique-Clustered Index and can have Many Non-Unique-clustered indexes.

If overall Dynamic CRM is suffering from a performance issues, one may think of Re-building Non-Unique Clustered Indexes.
  • It is recommended to schedule a job which takes care of Re-building indexes from time to time.
  • Re-building of indexes may involve extremely high usage of memory, CPU and other resources so it should be done mostly during non-working hours, to avoid impacting business operation.

One should be able to analyse and see if overall CRM performance is at stake or there are few Lookup’s which may causing a performance problem and need to be taken care off.

Now, let see how internally Lookup types get represented  in SQL.
Diagram is from On-Premise solution but it would be same for On-Line CRM.

A Primary Key is a 32 Bit Guid represented in format like this (E5E154C1-62D0-E311-940C-005056946CA6).

A Lookup which is a primary key from related entities is a same represented in Guid format along with its Primary name (string) attribute, as we can see in diagram.
  • ParentCustomerId                 - Lookup Guid Column
  • ParentCustomerIdName         - Lookup Name (string)

How can we create Indexes in Dynamic CRM

For On-premises deployments of Dynamics CRM we can add SQL indexes as needed directly in SQL.

For CRM Online we are required to open a support ticket and make the request to Microsoft Support.

(We have to do a homework and be sure that the index will solve the issue and not leave database performance unaffected or, worse, slow down your CRM)

Enabling Quick Find Record Limits to “YES”

Navigate to Settings -> Administration -> System Setting page.

Understanding Quick Find in Dynamic CRM

Quick Find allows you to search for a record from a grid view.

Every entity have Quick Find Active view and can be configure from customization page.

When you open the configuration page of Quick Find Active view, we get an option to “Add Find Columns”.

We can tick on attributes which we think are beneficial for a quick search.

Don’t forget to save and Publish your changes.