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 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
Data Source= CLUB-VIPINJA01;Initial Catalog=MSCRM_CONFIG;Connection Timeout=60;Integrated Security=SSPI
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.
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.