To make a long story short, if you aren't careful, bad things can happen when you lock down a CRM IIS server. There are many articles on the internet that cover this topic, but changes to IIS range from local security permissions to registry hacks. We learned the following details as a result of an IT department hardening IIS. The one thing a local IT department did in this situation was to remove “Domain Users” from the local users group.
I wanted to validate the SSRS connector installation on a fresh SSRS box installation. After building the base Windows 2008 server, installing and configuring SSRS, I thought it would be pretty straight forward to reinstall the Microsoft CRM 2011 SSRS connector. I was surprised when I got an error message and then noticed that “no default reports” existed on the new SSRS server. It turns out that Microsoft Dynamics CRM stores information in the config database that indicates that the SQL Server Reporting Services connector was previously installed. In the end, this makes sense because if you edited a “default” CRM report, you would not want a new CRM installation to write over the top of it. Although, it would be nice if when you uninstalled the last SSRS server that the bit would be reset to the default value so when you reinstall the SSRS connector it would reinstall the reports.
Here is how we came to understand the details on how Microsoft Dynamics CRM tracks information related to SSRS reports being installed.
- After we rebuilt the SSRS server, we ran the SSRS connector install on the SSRS server and no reports were installed and I received a new error message
- Since this was a development environment, we uninstalled CRM (since our deployment was all on one box except for SSRS) and reinstalled it to the exiting configuration and org databases and still no reports. My theory was that there must be a flag in the DB that tells the install not to republish reports because of it being an “existing” installation. You would not want to overwrite any customizations that you might have made to an existing MS report.
- We also found an our old friend “publishreports.exe” still included as part of the CRM installation on the IIS web server and not the SSRS server. If you recall this utility allows you to republish all the standard reports back to your SSRS installation hosting your CRM reports in CRM version 3 and 4.
We opened up a case with Microsoft support, and they clarified how it works. It stores a flag in the “MSCRM_Config” database that prevents the install from redeploying the reports when the SSRS connector is reinstalled. The following screenshots were provided by Microsoft Support and our direct relationship with them (Thanks Arpita!)
In the MSCRM_CONFIG database the Organization table now includes a value called “AreReportsPublished.” If this flag is flipped, the installation of the Microsoft CRM 2011 SSRS Data Connector will take no action.
The following are the steps to redeploy your reports:
1) Please check to verify that the flag has indeed been flipped (Value 1). The following SQL Statement should show you the info needed. AreReportsPublished should be the first column returned for each record.
Use [MSCRM_CONFIG]
Select * From Organization
2) If the value is 1, run the following update script to reset the flag back to 0……Be sure to set the proper Unique Name for the customers Org
Use [MSCRM_CONFIG]
Update Organization set AreReportsPublished = 0 where UniqueName = 'CRM'
3) Once the AreReportsPublished value is confirmed to be 0, you can run a repair on the Data Connector and it should read that value and import and publish the CRM standard reports.
The other option you have is to redeploy the reports using the “publishreports.exe” here’s how…
On the CRM server (not the SSRS server) change to the tools folder under CRM directory and run “PublishReports.exe “NameOfYourOrg”
Hope it helps!
Post by: Sean Shilling,
What if you have custom reports? How do you move those to the new SSRS site?
thanks,
Jeff