Configure MS Dynamics CRM 2011/2013/2015 to use multiple Report Servers (SSRS)

This week I've had quite a bit of fun turning the resilience up to 11 for our production environment of MS Dynamics CRM 2013.

In this post I will discuss how to configure Ms Dynamics CRM 2013 to use multiple SSRS servers, thus ensuring that reporting functionality is as resilient as the rest of the system.

In order to achieve this we need to make changes to AD, the SSRS configuration and finally MS Dynamics CRM. 

It's worth pointing out that this could well be overkill for your system, and to a certain extent it is for ours, but the whole architecture must be resilient is the diktat from above so ...

Pre-Requisites:

• 1 x Load Balancer (Distributing traffic to SSRS Servers on correct port, normally 80).
• 1 x VIP.
• 1 x DNS Record (For VIP above).
• 2+ x SSRS Servers in a scale out deployment.
• SSRS configured to use a domain account.
• Permissions to set SPNs on your domain and edit at least ssrs service account.

My Setup:

DNS record is: CRMReports.dev.local
SSRS Service Account:  dev\svc-ssrs

1. Active Directory

The first thing to do is to set up an Service Name Principal for the account that's running the SSRS service, which can be done with the following commands:

setspn -S HTTP/<VIP FQDN> <SSRS Service account>
setspn -S HTTP/<VIP Name> <SSRS Service account>

So in my case:

setspn -S HTTP/CRMReports.dev.local dev\svc-ssrs
setspn -S HTTP/CRMReports dev\svc-ssrs

The next thing is to ensure that the account is enabled for delegation, so from Active Directory Users and Computers console.

Note that the delegation tab will only appear after an SPN has been set up for that account.

2. SSRS

The first thing to do in the report server, is to enable Kerberos authentication, which can be done by editing the rsreportserver.config file. This file is normally found in this directory: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\

Look for the Authentication section and enable Kerberos and Negotiate as below (I've commented out NTLM in case I wanted to go back)

<Authentication>
  <AuthenticationTypes>
   <RSWindowsKerberos/>
   <RSWindowsNegotiate/>
   <!--<RSWindowsNTLM/>-->
  </AuthenticationTypes>
  <RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
  <RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
  <EnableAuthPersistence>true</EnableAuthPersistence>
 </Authentication>

From the Reporting Services Configuration Manager, Click on Web Service URL and then click on Advanced

Click on Add as highlighted.

Enter the host header name, which will be the DNS Record for the VIP, in my case: crmreports.dev.local and click OK to accept.

Repeat this process for the Report Manager Url.

Once done repeat all steps on section 2 on the other report servers.

3. MS Dynamics CRM

The process is relatively simple and it involves editing the organization to point to the new report server dns, i.e. crmreports.dev.local in my case.

From the Deployment Manager, select organization and Disable your organization(s).

Click Edit Organization.

Set the new report server Url.

 Ensure that all checks are ok.

Congratulations, you now have a resilient reporting service for MS Dynamics CRM.

Disable Export to Excel from SQL Server Reporting Server 2008/2012

We had an interesting request last week from the business. They were concerned that some users were exporting data to excel and running the business from excel, which was quite surprising as I distinctly remember making sure that nobody had permissions to do so.

Turns out that they were exporting the reports which are old fashioned SSRS reports.

So we decided to disable export to all formats but PDF.

Look for the rsreportserver.config file and change the Render section to this (in essence add Visible="false" to any extension you don't want to allow to be exported):

<Render>

<Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport,Microsoft.ReportingServices.DataRendering" Visible="false"/>

<Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false"/>

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering" Visible="false"/>

<Extension Name="ATOM" Type="Microsoft.ReportingServices.Rendering.DataRenderer.AtomDataReport,Microsoft.ReportingServices.DataRendering" Visible="false"/>

<Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering"/>

<Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RGDIRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false"/>

<Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>

<Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/>

<Extension Name="RPL" Type="Microsoft.ReportingServices.Rendering.RPLRendering.RPLRenderer,Microsoft.ReportingServices.RPLRendering" Visible="false" LogAllExecutionRequests="false"/>

<Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false"/>

<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/>

</Render>

The found version is 'C.0.8.40'. The expected version is '162'. (rsInvalidReportServerDatabase) - SSRS 2012

This morning I was trying to create the first organization in a MS Dynamics CRM 2011 deployment and when it tried to validate the SSRS URL, it failed with this error:

The version of the report server database is either in a format that is not valid or it cannot be read. The found version is 'C.0.8.40'.  The expected version is '162'. (rsInvalidReportServerDatabase)

I navigated to the SSRS URL and I got the same error.

It turns out that the error is fairly well known and can be easily resolved. All you need to do is recreate the reports database.

1. Open Reporting Service Configuration Manager. [Start | All Programs | Microsoft SQL Server 2012 | Configuration Tools | Reporting Service Configuration Manager]
2.  Connect to the report service instance that is giving you the problem
3. Click Database.
4. Click Change Database.
5. Select Create a new report server database and follow the wizard.

This should get rid of this issue.

It would be a good idea to get rid of the old report database, to prevent any confusion, after you've backed it up, of course.