How to setup load balanced SSRS Servers for CRM 2016


While not very common, there are times when a customer or client requests two servers for the CRM SRS data connector/SSRS to be installed and have load balancing configured for the servers. The idea here is failover; when one server hosting SSRS crashes, all incoming requests are routed to the secondary server. There are a couple of ways to do this, but the following method is the most straightforward and simplistic way.

1.    Create a Virtual IP(VIP) to route to each SSRS server.

In order for this method to work, a virtual IP will need to be created. The virtual IP will accept incoming data packets, then route the requests to each of the IP addresses of the physical servers that will have SSRS installed.

2.      Install and configure SSRS on each server.

Install and configure SSRS on each server as you typically would by pointing to the SQL server and/or instance where you would like the report server databases to be located.

3.    Install the CRM SRS data connector on each server.

Grab the installation files for your respective version of CRM and install the data connector on both servers. Install the data connector as you would for a typical CRM deployment.

4.      Configure the host file on each SSRS server.

On both SSRS servers, open the host file: C:\Windows\System32\Drivers\etc\hosts. Edit the host file by adding the IP and DNS name of the virtual IP created in step 1:

host

 

 

 

 

 

 

 

 

5.      Add the BackConnectionHostNames registry key with the server name and FQDN.

Open Registry Editor on one of the SSRS servers, and locate the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0.

Right click MSV1_0, point to New and then click Multi-String Value. Type BackConnectionHostNames, then press ENTER.

Right click BackConnectionHostNames, then click Modify.

In the Value data box, type the host name of the VIP and FQDN of the VIP, and click OK.

multi

 

 

 

 

 

 

Repeat these steps for the other SSRS server.

6.      Add hostname and URL root values.

On one of the SSRS servers, make a backup of the reportserver.config file located here: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer.

Right click the original reportserver.config file and choose edit.

Modify the hostname and URL root tags of the .xml file located under the service tag by adding the FQDN of the VIP as shown below:

reportserverconfig

 
 
 
 
 
 
 
 
 

Save the file after making the changes.

Repeat these steps for the other SSRS server.

7.   Restart both SSRS servers.

8.   During the import or new creation of an organization in CRM, specify the name of the VIP.

Use the VIP created in step 1 when prompted for the reporting services URL during a new org creation or an org import:


CRM Organization Import Issue and SSRS MaxRequestLength


When importing an organization to CRM 2011 we came across an error during the import wizard process which was causing the import to fail:

14:54:33| Info| PublishReportsFromDatabase: Creating report in Reporting Services. ReportId: 9f973403-bc84-e111-88bc-0050569e0001, Name: INVOICE PAYMENTS
14:54:34| Error| Error while updating organization information: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.Crm.Reporting.RuntimeReportServer.UploadReport(String path, Byte[] reportDefinition, String name, String description, Boolean overwriteExistingReport)
at Microsoft.Crm.Reporting.RuntimeReportServer.UploadReport(SRSReport report, String reportNameOnSrs, String name, String description, Boolean isSharedReport, Boolean overwriteExistingReport)
at Microsoft.Crm.Setup.Server.Utility.ReportsUtility.OrganizationPublishReportsScaleGroup(IDbCommand command, Uri reportingUrl, String orgUniqueName, Boolean ignoreCustomReportsFailure, Boolean publishOnlyCustomReports)
at Microsoft.Crm.Tools.Admin.DBImportHelper.RePublishReports(IDbCommand command, Guid organizationId, String organizationUniqueName, Uri reportUrl)
at Microsoft.Crm.Tools.Admin.ImportOrganizationInstaller.UpdateOrganizationInfo(Guid organizationId, OrganizationGroupsInfo organizationInfo, String organizationFriendlyName, String organizationUniqueName, Uri reportServerUrl, Int32 PercentUpdateOrganization, ICollection`1 users)

This was not an upgrade of any sort – just simply a CRM migration to a new environment so there were no versioning differences from a CRM perspective. While the error above is lacking much detail, it did give us enough to begin troubleshooting. We could clearly see that this issue was occurring with a report titled “Invoice Payments” but knew nothing else. After obtaining a copy of the report’s RDL file, we didn’t notice anything wrong in particular with the way the report was written but did think it was rather large for an RDL file – nearly 5MB – there were quite a few embedded images.

We decided to attempt uploading the report directly into SSRS and were met with a much more helpful error message – “Maximum request length exceeded”. What this was telling us rang a bell with what we noticed earlier regarding the RDL file size. By default, SSRS has a limit on the report file size that it will allow to be imported. This limit is 4MB but can be increased by doing the following:

Open the web.config of the Report Manager (%\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager) and find the “executionTimeout” setting. It should look something like this:

On this line, add the maxRequestLength attribute with the value (in KB) needed to upload the report. This value is not in here by default. It should now look like this (this shows a 10MB limit):

Save the file and then repeat these steps in the web.config of the Report Server (%\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer). Once both files have been modified and saved, restart the SSRS service.

Following this change, we were able to upload the RDL to SSRS directly to verify that the change worked and then subsequently were able to complete the organization import for CRM without issue.


Join me at CRMUG Summit in Reno NV October 13-16


Join me at the CRMUG Summit In Reno NV. This is the premier Dynamics CRM Event of the year. Don’t miss out, not too late to register below.

renosummit

 

More details..

http://www.crmugsummit.com/home

Here’s my preliminary schedule. Please refer to the website!

Monday, October 12

8:00pm-10:00pm @ Peppermill – Terrace Lounge Chapter Leader Bash

Tuesday, October 13

2:00pm-4:30pm @ Atlantis: Paradise D & E Volunteer Immersion at CRMUG Summit
5:00pm-8:00pm @ Hall 2 Welcoming Expo Reception

Wednesday, October 14

7:00am-8:00am @ Hall 1 Breakfast – Jump Into a Great Day
9:15am-9:45am @ Ballroom Lobby Networking Break
9:45am-10:45am @ D2 Getting Ready to Pull the CRM Trigger
11:15am-12:25pm @ C4 CRMUG Opening General Session – Microsoft Keynote
1:30pm-2:30pm @ D4 The CRM Superhero’s Tips, Tricks, and Toolbox
1:30pm-2:30pm @ D2 Scouting & Preparing as CRM GameDay Draws Near
2:30pm-3:00pm @ Ballroom Lobby Networking Break
4:00pm-4:30pm @ Ballroom Lobby Networking Break
4:30pm-6:00pm @ D2 ADFS & Upgrading with a Mobile State of Mind
6:00pm-8:00pm @ Hall 2 Expo Reception

Tribridge Customer Party – Invitation Only 7pm on..

Please contact me if your a customer or prospect and did not receive an invitation.

Thursday, October 15

7:00am-8:00am @ Hall 1 Breakfast – Jump Into a Great Day
8:00am-9:00am @ D2 Getting that CRM Upgrade to Purr Like a Kitten
9:00am-9:30am @ Ballroom Lobby Networking Break
10:30am-11:00am @ Ballroom Lobby Networking Break
11:00am-12:00pm @ C4 CRMUG General Community Session
12:00pm-2:00pm @ Halls 1 & 2 Networking Lunch and Expo
2:00pm-3:00pm @ D7 Going OnPremise to CRM Online
2:00pm-3:00pm @ C1 Take It to the Next Level with End User Experience Upgrades in CRM 2015
3:00pm-3:15pm @ Ballroom Lobby Networking Break
3:15pm-4:15pm @ D4 Ask the MVPs: Admin-Foundation Edition
3:15pm-4:15pm @ D7 Ask the Experts – User Adoption Edition: Enterprise CRM Strategies and Pitfalls to Avoid
4:15pm-4:45pm @ Ballroom Lobby Networking Break
4:45pm-5:45pm @ D2 Dissecting Multifaceted CRM Upgrades

Friday, October 16 – CANCLLED

8:00am-9:15am @ D2 Deployment Method of Choice  CANCELLED

 

 


CRM 2011: The Item ‘/SharedReports/5.0.xxxx/MSCRM_FetchDataSource’ cannot be found


Installation of the Microsoft Dynamics CRM Reporting Extensions formally known as the SSRS Data Connector (Still in on the installation media in same folder) is necessary so that CRM and SQL servers can communicate over the kerboros double hop issue when using to seperate servers.

The error below was received because a SQL Server was replaced the SSRS Data Connector was not added back in.

This key is the word ITEM cannot be found. This simply means the SSRS data connector cannot find the original reports. It turns out the report databases that support SSRS were completely removed, disgarded when the SQL server was rebuilt, so the CRM reports were now missing. (Note: Custom Reports would have been deleted and cannot be restored using this procedure. A backup of the ReportDB\ReportTempDB would be required, with a repair of SSRS to get those reports working again).

In order to fix this, remove and reinstall Reporting Extensions first, then run the command line tool from the crm server directory C:\program files\Microsoft Dynamics CRM\Tools\  to recreate/republish the reports by typing the following command :

publishreports ORGNAME  – Organization name of the CRM Installation Missing Reports

Once completed, issue an IISRESET on the crm server. Then navigate back into CRM report section and run/find your missing reports! There are several other topics covered on this blog around SPNs and Service Accounts that could also be an issue if your reports are not working. Take a look! Enjoy.


CRM 2011 Install: Reporting Services Error 401 Not Authorized


Troubleshooting a customer’s CRM 2011 setup failure, we almost were thinking it was machine account issue, so we rejoined the machine to domain last night. Sorry to say, that didn’t help.. So a little more digging…

It turns out that whomever installed SQL on that box (might be a VM image copy), failed to enter the service accounts when configuring SQL during the install, and decided to change the SQL services after the install.

The big issue with this approach is that SQL install creates the specific SPN’s for these accounts. So changing them after will work on the local box, but anything connecting via kerboros (not GP), will have issues trying to pass on the authentication.

The fix was:

setspn –A http/HostName Domain\Reporting Services Sevice Account

setspn –A http/HostName.domain.com Domain\Reporting Services Sevice Account

A quick restart of IIS, and now the CRM server installation can proceed. Enjoy!


Fixing CRM 2011 Reporting Issues with SPNs


A best practice is to install CRM using service accounts. Most administrators will do the same for SQL Server.  Many of the warnings about SPN’s during the install are igorned users. Many DBA’s will change the SQL service accounts after installation which will also cause issues.
CRM 2011 offers a new service called the Sandbox processing service. Failure to set the special SPN for this service while trying to run custom reports created with the report wizard will result in RS failure message.
Let’s make sure your CRM Server it setup in AD to allow delegation on the second tab.

 

 

 

 

 

 

 

Next, check the SPN for the CRM server so that it has HTTP using the following command:
setspn -L ( crm service account)
Look for http/servername and http/servername.FQDN you will need both.
To set them if missing:
setspn -A http/crmservername domainname\crmservice account
setspn -A http/crmservername.fqdn.com domainname\crmservice account

To view use the setspn -L with the service account name to see http has been set. (See screenshot below)

Now, on to the secret spn for the sandbox service..

setpsn -A MSCRMSandboxService domainname\crmsandbox service account

Once that has been completed on the CRM server, now head over to the SQL Server and check the service accounts for SQL. Let’s assume they are running under a SQL Service Account. IF the SQL service accounts were specified during the original install, the SPNs were created automatically. If not…

setspn -L domainname\sql service account to see the spns.

Again, you should see the 2 entries one with FQDN for MSSQLSvc. If not,
setspn -A MSSQLSvc/ domainname\sql service account
setspn – A MSSQLSvc/ servername.FQDN.com:1433 domainname\sql service account

to review, setspn -L domainname\sql service account should now show the correct SPN’s. You must have domain priviledges to set the SPN’s and if you have multiple AD machines it will take time to replicate the changes across.

Enjoy!