AX 2012 reports fail to deploy on a server hosting multiple SQL Server Reporting Server instances

Recently we have had several issues reported where you are at the initial phase of setting up all the AX servers and related software needed for development testing and production usage. Once such scenario is setting up a Report Server with multiple Report Server instances as it is now possible to use that in Microsoft Dynamics AX 2012. However after just having installed all the required SSRS instances, if they are not all fully configured then it may lead to an issue. I have tried to describe the issue with the following scenario, I hope it helps:

Scenario:

In my example, on my test system running Microsoft Windows Server 2008 R2 , I have multiple Microsoft SQL Server 2008 R2 Reporting Server (SSRS) Instances and Microsoft Dynamics AX 2012 AOS instances

– I have 3 SSRS instances called MSSQLSERVER, POWERPIVOT and AX and they were installed in this order.

– I have deployed the AX 2012 Reporting Extensions to the SSRS instance called AX

– The AX SSRS instance is configured as per documentation, and I have a Report Server configured in AX connecting to this Report instance

– I am deploying reports from the SSRS server itself, and my AX client configuration is configured to connect to the correct AOS instance

– Both the MSSQLSERVER and AX SSRS Instances have been fully configured using the Reporting Services Configuration Manager, and they have valid Web Service and Report Manager URLs.

– The SSRS Instance called POWERPIVOT has NOT been configured as yet

– This issue also occurs where multiple Reporting instances has been successfully configured as per TechNet article: Install multiple instances of Reporting Services on the same computer

Issue:

If I run the Microsoft Dynamics AX 2012 Management Shell on the Report Server and then run the following cmdlet to publish any report, in this example I ran:
                       

                           Publish-AXReport -ReportName CustTransList

I get the following error as highlighted:

——————————————————————————-
Deploying reports and related artifacts.

AOSName                   : 01@AX2012-A
ConfigurationId           : Instance1
Description               :
Default                   : True
ReportServerFolder        : DynamicsAX
ReportServerName          : AX2012-A
ReportServerInstanceName  : AX
ReportServerManagerUrl    : http://ax2012-a:81/Reports_AX
ReportServerWebServiceUrl : http://ax2012-a:81/ReportServer_AX

Publish-AXReport : Make sure that SQL Server Reporting Services is configured c
orrectly. Verify the Web Service URL and Report Manager URL configuration in th
e SQL Reporting Services Configuration Manager.
At line:1 char:17
+ Publish-AXReport <<<<  -ReportName CustTransList
    + CategoryInfo          : InvalidOperation: (:) [Publish-AXReport], Invali
   dOperationException
    + FullyQualifiedErrorId : Make sure that SQL Server Reporting Services is
   configured correctly. Verify the Web Service URL and Report Manager URL co
  nfiguration in the SQL Reporting Services Configuration Manager.,Microsoft
.Dynamics.AX.Framework.Management.Reports.PublishReportCommand

Publish-AXReport : The deployment was cancelled because of an error. On the rep
ort server, verify that:
– The SQL Server Reporting Services service is running.
– SQL Server Reporting Services is configured according to the instructions in
the Microsoft Dynamics AX Installation Guide (http://go.microsoft.com/fwlink/?L
inkID=163796).
– The Remote Administration feature is allowed to communicate through Windows F
irewall.
At line:1 char:17
+ Publish-AXReport <<<<  -ReportName CustTransList
    + CategoryInfo          : ResourceUnavailable: (Microsoft.Dynam…ReportsD
   eployer:ReportsDeployer) [Publish-AXReport], ServerException
    + FullyQualifiedErrorId : The deployment was cancelled because of an error
   . On the report server, verify that:
– The SQL Server Reporting Services service is running.
    – SQL Server Reporting Services is configured according to the instruction
   s in the Microsoft Dynamics AX Installation Guide (http://go.microsoft.com
  /fwlink/?LinkID=163796).
    – The Remote Administration feature is allowed to communicate through Wind
   ows Firewall.,Microsoft.Dynamics.AX.Framework.Management.Reports.PublishRe
  portCommand

Deployment completed.
Publish-AXReport : Make sure that SQL Server Reporting Services is configured c
orrectly. Verify the Web Service URL and Report Manager URL configuration in th
e SQL Reporting Services Configuration Manager.
At line:1 char:17
+ Publish-AXReport <<<<  -ReportName CustTransList
    + CategoryInfo          : NotSpecified: (:) [Publish-AXReport], InvalidOpe
   rationException
    + FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.Dynam
   ics.AX.Framework.Management.Reports.PublishReportCommand
—————————-
Subsequently, if I run the TEST-AXReportServerConfiguration cmdlet, I get the following error:

————————-
Testing the report server configurations.

Test-AXReportServerConfiguration : Make sure that SQL Server Reporting Services
is configured correctly. Verify the Web Service URL and Report Manager URL con
figuration in the SQL Reporting Services Configuration Manager.
At line:1 char:33
+ Test-AXReportServerConfiguration <<<<
    + CategoryInfo          : InvalidOperation: (:) [Test-AXReportServerConfig
   uration], InvalidOperationException
    + FullyQualifiedErrorId : Make sure that SQL Server Reporting Services is
   configured correctly. Verify the Web Service URL and Report Manager URL co
  nfiguration in the SQL Reporting Services Configuration Manager.,Microsoft
.Dynamics.AX.Framework.Management.Reports.TestReportServerConfigurationCom
mand

IsAOSRunning                  : True
IsCurrentUserAdminOnAOSServer : True
IsCurrentUserAdminOnSRSServer : True
IsReportManagerRunning        : True
IsReportServerRunning         : False
IsUACDisabledOnAOSServer      : True
IsUACDisabledOnSRSServer      : True

Testing the report server configurations completed.
———————————–

Furthermore this issue will also occur even if you specify the -Id paramter with the specific Instance name configured in AX, with either of the cmdlets.

Cause:

The Reports Deployment and Test cmdlets, both use WMI queries to identify the Reporting Instances installed and furthermore uses queries to identify if each of the Reporting Instances are Configured or not. If however when it tries to validate an instance that is not configured, it fails the test and stops checking any further and reports “IsReportServerRunning” status as FALSE, even though the Report Server service is running successfully.

Resolution:

There are a couple workarounds and permutation of this that you can use to workaround this issue:

Workaround 1:

Ensure ALL SSRS Instances on the Report server are fully configured using Reporting Services Configuration Manager. By this I mean that each instance, whether used by AX or not, has as a minimum a Service Account and a Web Service URL (the URL shows up with a hyperlink):

It doesn’t need to have a database and a Report Manager URL configured, other than ofcourse the instance being used by AX. This is the safest option, it doesn’t involve any registry editing as detailed in the next workaround.

Workaround 2:

If it is not possible to configure all the Reporting Instances for whatever reason, than you can also edit the Registry to swap the list of Reporting Instances that are installed so that it can validate your instance before it stops further checks. One way to achieve this by ensuring all configured SSRS instances are listed at the beginning.
Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs.
Follow these steps to swap the order of the SSRS instances:

(a)  open the registry and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS. By default the Registry editor displays the entries in alphabetical order:

(b) Export the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS to a .reg file and exit the registry editor.

(c) open the .reg file in notepad and this should display the actual order the instances were installed.

For example on my test server, the exported .reg file shows the following content:
————————–
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS]
“MSSQLSERVER”=”MSRS10_50.MSSQLSERVER”
“POWERPIVOT”=”MSRS10_50.POWERPIVOT”
“AX”=”MSRS10_50.AX”
————————–

as you can observe the POWERPIVOT instance is listed before the AX instance.

(d) Now make a backup copy of the exported .reg file and store it in a safe place.

(e) Edit the original exported .reg file and swap the install order such that the instance you are configuring is listed at the very top and save the .reg file.

In my example I swapped the order as follows so that the AX instance was listed at the top, and the POWERPIVOT instance at the bottom of the list and saved my .reg file
————————–
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS]
“AX”=”MSRS10_50.AX”
“MSSQLSERVER”=”MSRS10_50.MSSQLSERVER”
“POWERPIVOT”=”MSRS10_50.POWERPIVOT”
————————–

(f) In order to re-order the instances, one way is to delete the existing instance names and then import the edited .reg file into the registry.

In my example I deleted my 3 SSRS instances listed in the registry of my test server under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS

(g) Double-click on the edited .reg file to import the changes into the registry.

In my example I double-clicked on the .reg file and I get the following prompt:

And I clicked on Yes, to import the changes.

(h) Verify in the registry that the instances got re-added back under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS, and then exit the Registry editor.

(i) Verify Report server configuration again. Run the AX 2012 management shell and run Test-AXReportServerConfiguration cmdlet.

In my test server I get the following output:
——————————————–
Testing the report server configurations.

IsAOSRunning                  : True
IsCurrentUserAdminOnAOSServer : True
IsCurrentUserAdminOnSRSServer : True
IsReportManagerRunning        : True
IsReportServerRunning         : True
IsUACDisabledOnAOSServer      : True
IsUACDisabledOnSRSServer      : True

Testing the report server configurations completed.
——————————————-

The check was successful and I should be able to deploy my reports successfully too.

(j) Now attempt to redeploy the reports. In my test server when I now run the following cmdlet:

Publish-AXReport -ReportName CustTransList

the reports get deployed successfully! This is the output I get:

——————————————-
Deploying reports and related artifacts.

AOSName                   : 01@AX2012-A
ConfigurationId           : Instance1
Description               :
Default                   : True
ReportServerFolder        : DynamicsAX
ReportServerName          : AX2012-A
ReportServerInstanceName  : AX
ReportServerManagerUrl    : http://ax2012-a:81/Reports_AX
ReportServerWebServiceUrl : http://ax2012-a:81/ReportServer_AX

ReportName                 : CustTransList
Designs                    : {CustTransList.Report}
Assemblies                 : {CustTransListReport.BusinessLogic.dll, SharedComp
                             onentsSRS.BusinessLogic.dll, DrillThroughCommon.dl
                             l}
Datasources                :
DesignDeploymentStatus     : {Success}
AssemblyDeploymentStatus   : {Success, Success, Success}
DataSourceDeploymentStatus :

Deployment completed.
——————————————-