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.


No comments: