Vulnerability issues associated with SQL Browser Service

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

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\configdb

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

Output

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.

Thanks. 

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 1^st-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.

Conclusion

• 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.

Thanks.

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