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.

The private key does not support the exchange KeySpec.

When using Active Directory Federation Services (ADFS) for claims-based authentication with Dynamics CRM, one of the requirements is a SSL certificate. If a new certificate has to be procured, it is imperative to make sure the certificate request (CSR) is being generated with the correct KeySpec, if required.

There are two options for KeySpec:

• “1” or “XCN_AT_KEYEXCHANGE” 
• “2” or “XCN_AT_SIGNATURE”

With ADFS, the certificate needs to support key exchange so the required KeySpec is "1" or "XCN_AT_KEYEXCHANGE". If the other option is chosen, you may end up with failures when trying to log in to CRM and will see errors in the event viewer on the ADFS server similar to below.

Exception type: NotSupportedException 

    Exception message: The private key does not support the exchange KeySpec.

   at System.IdentityModel.Tokens.X509AsymmetricSecurityKey.DecryptKey(String algorithm, Byte[] keyData)

   at System.IdentityModel.Selectors.SecurityTokenResolver.SimpleTokenResolver.TryResolveSecurityKeyCore(SecurityKeyIdentifierClause keyIdentifierClause, SecurityKey& key)

While the certificate will still appear valid using "XCN_AT_SIGNATURE" and will secure the webpages, authentication will not succeed with ADFS so the certificate will need to be regenerated. 

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.

Use SSL for Outgoing Connection / Incoming Connection Radio Buttons Greyed Out for CRM Server Side Sync POP3-SMTP Profile

If you need to create a Server Side Sync profile for your POP3 and/or SMTP servers without using SSL for your on-premise CRM 2013, 2015, or 2016 environments you may notice that you are unable to change the option to not use SSL. The radio buttons are simply greyed out and stuck set to “Yes”. This can be problematic if you want to run over basic HTTP.

Luckily, these buttons can be enabled via the following PowerShell commands run on the CRM server:

Add-PSSnapin Microsoft.Crm.Powershell
$setting=Get-CrmSetting ServerSideSyncEmailSettings
$setting.AllowNonSSLEmail=$True
Set-CrmSetting $setting
Get-CrmSetting -SettingType ServerSideSyncEmailsettings
Exit

After the commands are complete, refresh your browser and the buttons should now be active.

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 and IIS Machine Keys

When you have multiple CRM web (front-end) servers splitting the load, requests can bounce between servers or they can be set to remain on the same server through the NLB/Firewall settings. The latter option is commonly referred to as “Sticky Sessions” or “Persistence”.

If requests bounce between servers (whether by design or by fault), you may notice issues when accessing certain functionality within CRM. Often it will be accompanied by the following error message in the event viewer:

Event code: 3012 
Event message: An error occurred processing a web or script resource request. The resource identifier failed to decrypt. 

Exception information: 
    Exception type: HttpException 
    Exception message: Unable to validate data.
   at System.Web.Configuration.MachineKeySection.EncryptOrDecryptData(Boolean fEncrypt, Byte[] buf, Byte[] modifier, Int32 start, Int32 length, Boolean useValidationSymAlgo, Boolean useLegacyMode, IVType ivType, Boolean signData)
   at System.Web.UI.Page.DecryptString(String s, Purpose purpose)
   at System.Web.Handlers.ScriptResourceHandler.ProcessRequest(HttpContextBase context, VirtualFileReader fileReader, Action`2 logAction, Boolean validatePath)

Basically what is happening is one server encrypted (validated) some of the traffic with an automatically generated key and another server received it but cannot decrypt it because it has different keys. These keys are known as “machine keys” and are found within IIS. By default, these keys are all set to generate automatically and no two are the same. When you are using multiple web servers, the machine keys need to be set statically and shared amongst them. Here is how to do this, starting on the first web server:

1. Open IIS, select the Microsoft Dynamics CRM Website, and double-click “Machine Key”
   
   
2. Uncheck the “Automatically generate at runtime” and “Generate a unique key for each application” boxes under both “Validation Key” and “Decryption Key”.
   
   
3. Click “Generate Keys” on the right side of the screen.
   
   
4. This will create new, random keys.
   
   
5. Click apply.
   
   
6. From here, repeat steps 1 and 2 on the remaining web servers. Instead of generating new keys as in step 3, copy and paste the keys generated from earlier into the remaining servers so that all servers have the same set of keys.
7. Once keys on all servers are set and applied, reset IIS on all the boxes. That should do it.

CRM 2016 Update 0.1 Bug with ADFS for Server 2012 R2

Following a successful upgrade to CRM 2016 and installation of the 0.1 Update, users could no longer authenticate against ADFS using the “internal” URL.

On the ADFS server I was seeing Event ID 364 in the Event Viewer:

Exception details:
Microsoft.IdentityServer.Web.InvalidRequestException: MSIS7042: The same client browser session has made '6' requests in the last '0' seconds. Contact your administrator for details.

In efforts to get this working, I tried completely re-configuring CRM for claims and IFD and even recreated the Relying Party Trusts. All with no luck but apparently, I was not alone on this issue and Microsoft has acknowledged it as a bug with Update 0.1. The following forum thread has more info:

https://social.msdn.microsoft.com/Forums/windowsserver/en-US/9d4040eb-81fa-4144-ae4b-85ca4610aa1d/crm-2016-problem-with-claimsbased-authentication?forum=crm

These are the only real details on this issue currently available:

There were major code changes in Ara UR1 for authentication. The affected code is in Microsoft.Crm.Core.Security.Identity.IdentityExtensions.GetUserPrincipalName(). We are unable to cast from type ClaimsIdentity to a new type CrmIdentity. Therefore, the variable is null, and we cannot retrieve the information.

Please note that only the ‘internal’ URL is affected when IFD is setup. The ‘external’ URL (e.g. https://orgname.domain.com) that uses forms authentication works fine still. I will update this blog post as more information becomes available.

UPDATE: Service Pack 1 for CRM 2016 resolves this issue.