Issue Installing CRM 2016 SRS Data Connector with SQL 2016

In my last post, I mentioned that I had received some Windows Server 2016 boxes to test setup of CRM 2016/Dynamics 365. In addition to them being the 2016 OS, they also came with SQL Server 2016. The installation of CRM went off without a hitch (after working through the Search service problem) and the databases created in SQL as expected.

However, when it came to installation of the SRS Data Connector, things got a little hairy. The installation began but quickly threw this error message:

Error| System.Exception: Action Microsoft.Crm.Setup.SrsDataConnector.AddBindingRedirectForRdlHelper failed. ---> System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportManager\web.config'.

To make a long story short, SQL 2016 no longer creates/uses the path specified in the error message and the RTM Data Connector installer does not check the version of SSRS being used so it will just keep trying to use that directory.

Now, I had some previous experience installing the Data Connector with SQL 2016 involved. From that, I recalled that the installer, when asked to check for updates on the first screen, actually went out and got some new install bits. This time around I was not so lucky – it just told me there were no updates available (I even verified that the Windows Firewall was off and that the box was allowing Windows Updates)… it had internet access but if you’re reading this and your SQL server does not have access to the internet, you should keep reading. Regardless, I needed a solution and the internet was not full of suggestions with this being pretty new technology.

I decided to check the Microsoft Update Catalog to see if I could download and manually patch the update in. To my delight, I came across a listing for “Setup Update for Microsoft Dynamics CRM 2016 Reporting Extensions” and this ultimately helped solve my issue. Here is a step-by-step process of what to do if you are facing the same situation:

1. If your initial install failed, go into the server’s control panel and uninstall the data connector.

2. Go to http://www.catalog.update.microsoft.com/

3. Search for “3129794” and you should get back one listing. Click the download button.

4. A new screen will pop up with a ton of .cab files. Find the one named “srs_kb3129794_amd64_1033_b585b78e247dc7611e1e406a6c132cdee112690c.cab” (just look for 1033) and download that. I will admit that I am not sure of the purpose of all these files but the 1033 one did the trick for me. Later on, you will notice that another file we work with is also “1033” so that probably has something to do with it.

5. Once you have the .cab file downloaded to the server, open it up to reveal the patch file. Right-click on it and choose “Extract”.

6. After the extraction, you will have the .msp file available for use. Go to the directory of the SRS Data Connector’s installation files and locate a folder named “Update”. In here, you will see another .msp file named “Srs_KB3121695_Amd64_1033”.

7. Cut and paste that file out of the “Update” folder – just put it anywhere else on the server.

8. Copy and paste the .msp you just downloaded into the now blank “Update” folder - essentially replacing it.

You should go from this:

To this:

9. Re-run the installer as normal and it should complete without issue.


Just to note a few things:

• I initially played a bunch of trial and error with getting the update patched in before I discovered the ability to replace the .msp in the Update folder. I first went down the path of editing the config.xml to include the patch and then called the installer from a command line specifying it to use the config file. This is how I have always patched in updates for the server installer but it does not seem to work the same way for the Data Connector. I also tried loading the patch into the temp files that the installer looks to… also didn’t work.

• If your base installer’s update file has a different number than “1033” at the tail end of it, you may need to match it up with the correct .cab file from the Update Catalog. I did not encounter this scenario so I cannot test or prove out this theory but I think logic dictates… 

• There is another blog out there about applying SP1/2 to the failed installation and then manually editing file structure. I will not discredit that being a possible workaround but personally, I do not like the idea of relying on an update to fix a broken install.

Good luck!

Reports in CRM not working - The type initializer for 'Microsoft.Crm.LocatorService' threw an exception.

We had a customer running into an issue while trying to run reports from CRM. Of course, the error in the CRM UI was the usual rsProcessingAborted error - Cannot create a connection to Data Source. So as I always need to do, I dug into the SSRS logs. The errors I could extract from there were:

Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: The type initializer for 'Microsoft.Crm.LocatorService' threw an exception. ---> 

Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: Cannot load Counter Name data because an invalid index 'W3SVC_W3WP' was read from the registry.

From what I could surmise, there was something wrong with the performance counters on the server(s). I did find that Microsoft does have a KB for this error but it is in reference to update rollups on the CRM Outlook Client. Luckily for me, the fix turned out to be similar!

1. On the CRM Server, open an administrative command prompt.
2. Type “lodctr /r” and press enter.
3. You will then get the message ‘Info: Successfully rebuilt performance counter setting from system backup store’.
4. Restart CRM Services and run an IISReset
5. On the SSRS Server, open an administrative command prompt.
6. Type “lodctr /r” and press enter.
7. You will then get the message ‘Info: Successfully rebuilt performance counter setting from system backup store’.
8. Restart SSRS Services.

After following the above process, reports in CRM began working again. The commands run rebuild the performance counters.

Report Server cannot load the SQLPDW extension

While troubleshooting SSRS issues, you may run into the following error messages in logs and traces:

Report Server (MSSQLSERVER) cannot load the SQLPDW extension.

Report Server (MSSQLSERVER) cannot load the TERADATA extension.

On numerous occasions I have seen both customers and colleagues believe that these errors were the source of their problems. After which, the wild goose chase to resolve these errors ensues. I am here to tell you that it is highly unlikely that they are related to your problem at hand (especially if you are only using SSRS for CRM reporting).

These errors occur because the Teradata and SQLPDW extensions are registered in the Reporting Services configuration file by default but the assemblies for these extensions do not get installed in the same manner. Typically these extensions are used with big data and business analytics.

If the errors do not bother you, simply ignore them and track down other error messages. However, if you are the type that prefers clean logs you can take the following steps to alleviate the error messages from being logged:

• Open the rsreportserver.config file. This can usually be found in \Program Files\Microsoft SQL Server\MSRSXX_X.MSSQLSERVER\Reporting Services\ReportServer.
• In this config file, simply search for and comment out the entries related to SQLPDW and TERADATA. There should be 2 entries for SQLPDW and 3 for TERADATA. Below is an example:

<!--Extension Name="SQLPDW" Type="Microsoft.ReportingServices.DataExtensions.SqlDwConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/-->

<!--Extension Name="SQLPDW" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.MSSQLADW.MSSqlAdwSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">

    <Configuration>

        <EnableMathOpCasting>False</EnableMathOpCasting>

    </Configuration>

</Extension-->

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/-->

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">

    <Configuration>

        <EnableMathOpCasting>True</EnableMathOpCasting>

        <ReplaceFunctionName>oREPLACE</ReplaceFunctionName>

    </Configuration>

</Extension-->

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlModelGenerator,Microsoft.ReportingServices.SemanticQueryEngine"/-->

• Save and close the config file.
• Restart the SSRS service.

CRM Reporting Issue - The system cannot contact a domain controller to service the authentication request

Had a case come into my queue indicating a client’s reports in their production CRM were not working so I logged in and began taking a look. At first glance, I was seeing the generic “Reporting Error – The report cannot be displayed. (rsProcessingAborted)” that we see for 90% of reporting issues so I proceeded to the SSRS logs where I was greeted with the following error:

System.Runtime.InteropServices.COMException: The system cannot contact a domain controller to service the authentication request. Please try again later. (Exception from HRESULT: 0x80090350) ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: The system cannot contact a domain controller to service the authentication request. Please try again later. (Exception from HRESULT: 0x80090350)

Interesting to say the least and something I had never seen before. My inclination was that a domain controller was down or somehow the connection to AD was severed (but then again, how was I able to log on the server). I ran a set command to check the logon sever and was able to ping it fine, along with all of the other DCs. Next stop was at the DNS/Gateway settings on the NIC – compared everything to another SQL/SSRS server that was working fine. Again, nothing seemed out of place.
Off to the handy dandy event viewer! Immediately I noticed a flood of errors indicating that the automatic backups and log shipping were failing. Amidst those errors I found a familiar message:

Whatever was causing the reporting issue was also causing the backups to fail. I made my way through the events until I reached the beginning of the flood and there it was, in all its glory:

BackupDiskFile::CreateMedia: Backup device '\\goxsaXXXX\D$\MSSQL11.XXXX\MSSQL\Backup\Log_Shipping\XXXX_20160524043001.trn' failed to create. Operating system error 1331(This user can't sign in because this account is currently disabled.).

Easy enough – the SQLAdmin account running SSRS (and the other SQL services) cannot authenticate because it’s disabled.

 

Had the customer check AD and sure enough, the account was disabled. Once enabled, reports began working again. 

In all, this was a very strange error message for the actual issue. It could have really led us on a wild goose chase into the networking side of things. Probably saved 2-3 hours by reading through the error logs and being thorough in investigation before going back to the customer with the wrong answer. Moral of the story: do your homework.

A Server Certificate Could Not Be Validated for URL - SQL Server Reporting Services 2012

If you ever get the following error during the system checks of the CRM installer, check to see if you can even access the Report Manager URL from the SSRS server:

A Server Certificate Could Not Be Validated for URL: http://reportservername/ReportServer 

When checking the Report Manager URL (http://reportservername/Reports) using HTTP - not HTTPS - you may see the following error:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

You will probably also get a certificate error accessing the Report Service URL (http://reportservername/ReportServer) which will end up resulting in a 404 error screen if you proceed. So why is SSRS looking for SSL/TLS when using HTTP?

The answer is rather simple – it’s configured to do so. Here is how to change that:

1. Go to the Report Server directory. Typically it is {Drive}:\MSRS11\Reporting Services\ReportServer

2. Open the rsreportserver.xml file in notepad or other editing software.

3. In this configuration file, look for or run a find for SecureConnectionLevel. It should be about 12 lines from the top. You will likely see this key set to a value of 2.

4. Change the value to 0 so that the line reads:
<Add Key=”SecureConnectionLevel” Value=”0” />

5. Save and close the file. Restart the SQL Reporting Services service and try accessing the Report Manager URL once more. You should now get the site as expected over HTTP.

6. Go back to your CRM server and re-run the installation wizard.

Of course, the alternative to going this route is to actually provide SSRS with an SSL certificate and access it over HTTPS only but some may not want the added hassle or cost.

CRM Organization Import Issue and SSRS MaxRequestLength

While importing an organization to CRM 2011 I 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. I could clearly see that this issue was occurring with a report titled “Invoice Payments” but knew nothing else. At this point, I enlisted the help of a colleague (The amazing Lauren Hudgins) who is more familiar with report writing to see if there was anything peculiar about this report. After obtaining a copy of the report’s RDL file, we weren't able to 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:
  <executionTimeout = “9000” /> 

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):
       <executionTimeout = “9000” maxRequestLength=”10240″ />

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. Big thanks to Lauren for her assistance on this one.