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!

High Availability and Disaster Recovery Options for Dynamics 365 (CRM)

Planning the infrastructure for your Dynamics 365 environment is a critical step in the deployment process, especially when the software is mission critical to business needs and day-to-day operations. In scenarios like these, it is imperative to have high availability and even disaster recovery plans in place. So the question is: What options do you have when it comes to Dynamics 365 on premise? In this article, we will break it down from both an application (Dynamics 365) and database (SQL Server) perspective.

High Availability – Dynamics 365
For most companies, having high availability for their Dynamics 365 deployment is a must and the setup for this is similar to that of other web-based applications. Keep in mind that through this section we are strictly referring to Dynamcis 365 servers with the “Front End” or “Full” role – in other words, the servers that host the website in IIS. Having multiple “Back End”-only servers (those with the Async and Sandbox services) is also a good idea but those will natively load balance themselves if they are in the same deployment.

• Network Load Balancing – Probably the most obvious method for high availability is running multiple Dynamics 365 servers in a load-balancing configuration. This will not only help with carrying the day-to-day load of traffic but also serve as the foundation for a good high availability option. If a server were to fail, just pop it out of the NLB pool. Load balancing comes in a few different flavors because of all the applications out there but mainly it comes down to two options – hardware and software.

• Hardware Load Balancing involves the use of a third-party appliance such as F5 or NetScaler that sits in front of the Dynamics 365 servers to handle which server receives the user requests. While this is the preferred option as it does not create any additional processes for the servers to handle and usually provides a wider array of configuration options (e.g. monitoring, security, filtering, etc…), it can be more difficult and much more costly to implement.

• Software Load Balancing on the other hand is relatively easy to setup and in the case of Windows NLB, is free, as you have already paid for your Windows Server licenses. Critics of software load balancing will contend that it is not “true” high-availability due to the lack of many key features found in hardware load balancing. The fact stands that Windows NLB is just Round Robin DNS so if a server were to go down, requests would still be sent to the faulted server until it is told otherwise.

• As one can probably infer, this is simply a cost vs. ROI decision – which is the case with most IT situations. If the hardware option is being considered, make sure to reach out to your appliance vendor to ensure there are no known issues with it and Dynamics 365. It is also a good idea to inquire about documentation for setup. For more information about load balancing Dynamics 365 in general and the required configurations, check out this TechNet: https://technet.microsoft.com/en-us/library/hh699803.aspx. 

• Backup Server(s) – While this method may be a little more rigid, it is still effective and again, has a couple options. The meat and potatoes of this route is having a server to fall back to in the event that your primary Dynamics 365 server(s) fails and NLB has not been implemented.

• VM Snapshots – If you are reading this, you most likely understand the concept of VM snapshots so we will not dive too heavily into this. The idea is to have automatic snapshots taken automatically on a set interval that the Dynamics 365 server can be reverted to or new server built from in the event of failure. Reverting the existing server is the easier option but also the more risky of the two. Spinning up a new server is safer but more involved – and requires the shutdown of the existing server as you will need to use the same host name and IP address.

• Laying in wait – We have had some customers opt for this route as the return to uptime is a little quicker and/or VMs were not being used. Dynamics 365 deployments can obviously be comprised of multiple servers – but not all need to be active or enabled. The concept here is to build a secondary Dynamics 365 server as part of the deployment but leave it disabled until it is needed. If the primary server were to fail, all that would be required is enabling of the server via the Deployment Manager and changes to DNS (and firewall rules, if applicable).

High Availability – SQL Server

Now that we have covered high availability from an application perspective, what about the database? Don’t worry! Microsoft has that covered too!

• SQL Server Failover Cluster – There is nothing new about this concept. Two SQL Servers are setup to use a shared disk, one server goes down and the other picks up the load. When installing Dynamics 365 just make sure to point connection strings to the cluster name and not a single node and you should not have any issues but be sure to test failover prior to going live. Just to note - although you can install Dynamics 365 to a SQL Server cluster configured for either active-active or active-passive clustering, the cluster will function in an active-passive manner. While clustering is tried and true, Microsoft decided to go a step further with...

• SQL Server Always On – New to SQL Server 2012 (Enterprise Edition), Always On introduced the concept of Availability Groups. An Availability Group is simply a container for a set of databases that fail over together. For purposes of Dynamics 365 databases, this is important considering there are always at least two databases per deployment. With Always On, the databases in the Availability Groups are synchronized amongst all the configured replicas. We won’t get into the details of setting up Always On (there are tons of articles out there already) but be sure to reference this TechNet for how to configure Dynamics 365 with SQL Always On: https://technet.microsoft.com/en-us/library/jj822357.aspx 

Disaster Recovery – Dynamics 365 and SQL Server

While having a single server fail is far more likely than seeing your entire data center swallowed into a pit only to be burnt up in the Earth’s core, it is a situation that should also be planned for! Unlike the sections regarding high availability, we need to think of a DR scenario in terms of the entire infrastructure – both Dynamics 365 and SQL Server – because if there is a disaster, chances are it is taking both so these plans go hand-in-hand. 

Over the years, we have implemented CRM/Dynamics 365 disaster recovery plans for numerous customers and as a result, have learned (what we consider) the best option – which is what we will be covering. This is not to say there are no other viable methods (such as VM snapshot migrations or SQL Log Shipping), but we will keep focus on our preferred option. In a nutshell, we are going to have a separate, self-contained Dynamics 365 environment in the DR data center that has data synced from the production SQL servers in the primary site.

SQL Server

Let us start from a database perspective since it is a little more complex and setting up Dynamics 365 for DR will require this information. As previously touched on, SQL Always On will come in handy yet again and recall the concept of Availability Groups as this will play heavy into this strategy.

For DR scenarios with Dynamics 365, we will need two Availability Groups in SQL AlwaysOn – one for the MSCRM_CONFIG database to be synchronized between the two primary data center nodes and another to synchronize the Organization_MSCRM database between not only the two primary data center SQL Servers but also a third SQL Server in your DR data center. The reason for this will become clear as you keep reading.

Dynamics 365

Going into the Dynamics 365 server installation(s), the third SQL node should already be built and ready with SQL Always On. Unlike the installation of the Dynamics 365 servers in the primary data center, you do NOT want to set the SQL connection to use the Availability Group but rather just the SQL Server located in the DR data center. The reasoning behind this is the MSCRM_CONFIG database – this database is specific per deployment of Dynamics 365 and two cannot exist in the same SQL server/instance (nor can the name be changed). Remember that this Dynamics 365 DR deployment is to be isolated from the primary.

At this point, you may be asking yourself how this all comes together. When the primary data center goes down (mainly both primary SQL nodes), the organization database will be failed over to the third node in DR. Once that happens, launch the deployment manager on the Dynamics 365 DR Server and import the organization database into the deployment. That should take about 5 minutes and then Dynamics 365 will be back up and running.

Great! We have the servers all setup in DR and are ready for that giant pit to open up beneath the data center! Now what? RUN THROUGH A TEST DISASTER RECOVERY SCENARIO! This cannot be stressed enough – you do not want to wait for a real disaster to find some small configuration issue messed up the entire plan.

CRM and TLS 1.0

While performing a routine installation of CRM 2016, I stumbled upon a new error during the system checks at the end of CRM installation wizard. The error was for the SQL Server check and read:

Could not connect to the following SQL Server: 'XXXXXX-SQL02'. Verify that the server is up and running and that you have SQL Server administrative credentials. [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

Having never seen this particular error, I resorted to my friend Google who led me down a wormhole or possible issues and resolutions related to certificates and protocols. Turns out that this was indeed related to a security protocol – TLS 1.0 to be exact. It was disabled on the SQL Server as part of our new security template for new servers. So with the help of one of Cloud Application Engineers, Spencer Ashworth, we enabled TLS 1.0. This is done through a simple registry change.

Browse to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server\ and locate the DWORD named Enabled. Set the value to 1, close the registry and then reboot the server.

Following the reboot and re-run of the CRM installation wizard, the system checks all passed and CRM was able to be successfully installed. Big thanks to Spencer who was the one that recalled CRM’s requirement of TLS 1.0 and knew the resolution!

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.

Next Button Grayed Out in Internet-Facing Deployment Configuration Wizard

While recently assisting a colleague with setting up IFD, we stumbled upon an interesting bug with CRM 2016 in which we could not advance in the IFD wizard because the “Next” button was grayed out and disabled. No matter what we typed in the field, it would not enable the button. Tried an IISReset, restarting CRM services, and even a reboot but they all failed to help.

Realizing this had to be an issue within MSCRM_CONFIG database, we took a look at the tables that hold the IFD properties that we input in these wizards. What we found was that somehow the NVarCharColumn value of ExternalRelyingPartyPassiveIdentifier in the FederationProviderProperties table was set to “uri:ifdMicrosoftCRM”. Typically we would see the Auth URL in this field so we updated it manually to “https://auth.domain.com” in SQL. Make sure to include the “https://” - even though we do not enter this in the field during the configuration wizard, it gets appended automatically and set in the database. Issue an IISRESET on the CRM server and then re-launch the CRM Deployment Manager.

Please note that editing the database manually via SQL is not supported by Microsoft and you are doing so at your own risk. Always take backups and use caution.

CRM Database Log Growth Issue

A customer of ours had come to us facing a rather interesting issue. Every night around 1:00AM their CRM database log file would grow to 31GB and cause the log drive to fill up. When users would log into CRM in the morning, they would receive SQL errors stating that their transactions could not be completed because of this.

Given that this issue occurred on a regular schedule, I determined that the issue had to be attributed to some sort of automated job. Figuring the first place to check would be the out-of-the-box CRM Maintenance Jobs, I downloaded the Job Editor from Codeplex (https://crmjobeditor.codeplex.com/ - Very useful tool that everyone should be using) and got to investigating.

Right off the bat, I saw the error on the Deletion Service:

Deletion Service encountered an internal error: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'SubscriptionTrackingDeletedObject'

From here I moved to SQL and queried the SubscriptionTrackingDeletedObject table of the CRM database. What I found was astounding – the table contained 137 MILLION records. Basically after seeing this I knew that the job had to just be timing out – we tested my theory by running the job manually and immediately saw the log file grow to the expected 31GB.

It was decided to clean this table up manually via truncating it. Before you say “Oh no! Don’t delete records via SQL directly!” let’s explain what this table actually is. When records are deleted from CRM, there are also records that get inserted into this SubscriptionTrackingDeletedObject table.  This table gives the Deletion Service Job ObjectIDs that have been removed so that further cleanup can be performed asynchronously. So essentially, it is just a table of deleted records which gives the Deletion Service knowledge to clean up some other areas of CRM (e.g. POA records, duplicate detection records, etc...) if necessary. Once cleaned up, the records from the table are removed. We understood this and decided the need to clean this table outweighed having the other areas of CRM cleaned up (as you will learn later, this wasn’t a concern for us because of how the records got in there). Please note I cannot condone the practice of editing SQL manually without full knowledge of the possible repercussions. Always consult with Microsoft support if in doubt and remember what works in one scenario, may not work in all.

After the table was truncated, CRM was tested and the deletion service job was run manually – this time not failing with the error above and the log did not grow to 31GB. Before calling this case closed, I still needed to understand what caused this problem in the first place. What could have possibly created so many records in such a short period of time? Luckily, I had the right people involved on the customer’s end and we were able to determine that there had been a malfunctioning Scribe job that was running unnoticed for some time. The job was bulk creating and deleting 50,000 records at a time within CRM but it had since been fixed. Case closed.