Celtic Coding Solutions Blog

Celtic Coding Solutions Blog - for everything computer related

Installation guide for Dynamics AX 2009 and SQL Reporting Services 2005

This guide is intended to outline the software installation and configuration to start building custom SSRS 2005 for use within Dynamics AX.

 

1.0) Install SQL Server 2005 Developer edition

Select the following (at minimum):-

Make sure the following are to be installed:-
•    Reporting Services/Shared Tools
•    Client Components/Business Intelligence Development Studio (cut down version of Visual Studio 2005)

 

2.0) Install Dynamics AX 2009 Client Tools

The following will need to be installed:-
•    Client
•    Reporting Extensions
•    Reporting Tools
•    .Net Business Connector

2.1) Instance Type

2.2) Components

2.21) Components - Possible Issues

While trying to install Dynamics AX 2009 Client Component, you may get the following error:-

Setup has detected that you are installing Reporting extensions on an operating system that is not supported for Microsoft Dynamics AX. The minimum supported server operating system is Microsoft Windows 2003 with Server Pack 2

Click Yes and you will then see the following error:-

You must install SQL Server 2005 Reporting Services Service Pack 2 before installing the reporting extensions.

After trying to install SQL Services Packs, uninstalling SQL Server etc the only option that seem to work was early option pill from the following website:-

http://community.dynamics.com/product/ax/f/33/p/12322/31719.aspx

The instructions followed are:-
The same problem with SQL Server 2005 SP3, this fix our problem:
1. Open the registry and find the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server key.
2. Right-click the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server key and choose New > Key.
3. Name the key Reporting Services.
4. Right-click the Reporting Services key and choose New > String Value.
5. Name the string value Version. The Version string value will have a type of REG_SZ.
6. Double-click the Version string value and enter 9.00.3042.00 as the value data. Click OK.

3.0) Install Dynamics AX Report Dlls

There are some Dynamics AX Dlls that are used in the SQL Server Reporting Services Reports:-
•    Microsoft.Dynamics.Framework.Design.Reports.dll
•    Microsoft.Dynamics.Framework.Reports.dll
•    Microsoft.Dynamics.Framework.ReportsDeployment.dll
•    Microsoft.Dynamics.Framework.ReportsExtensions.dll
•    SysSecurity.BusinessLogic.dll

They all need to be placed in the Reporting Services bin folder e.g.:-
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

I also install them in medicine abortion the GAC (C:\Windows\Assembly folder) apart from SysSecurity.BusinessLogic.dll which is not strongly named assembly.

SPSite - SharePoint 2010 and Visual Studio 2010 - FileNotFoundException

Trying to connect to my local SharePoint 2010 site using Visual abortion clinics Studio 2010 Asp.Net on Local IIS Web server Version 7.5 (Windows 7 x64) e.g.
SPSite oSpSite = new SPSite("http://LocalPCName:81");

I got the following error (FileNotFoundException):-
The Web application at http://LocalPCName:81 could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a ru486 cost new request URL mapping to the intended application.

 

To Fix:-
Check what account the App Pool for "SharePoint Central Administration V4" is running under.  I use a generic account say Domain\SharePointAdmin.

Make sure this account is under the "WSS_ADMIN_WPG" local accounts group.

Make sure this account is site administrator for the site you are trying to connect to.

"Use Local IIS web server" rather than "Use Visual Studio Development Server" for Visual Studio Project Web debugging.  e.g. in Visual Studio for your project select Project - XXX Properties... - select Web (left tab) - tick "Use Local IIS Web server" - then click "Create Virtual Directory". 
Once created open Internet Information Services (IIS) Manager and find the newly created Web Application.  Select the web application and click "Advanced Settings...".  Change the Application Pool to the same one as the "SharePoint Central Administration v4".


The following I have seen people blog about that fixes their issues, but this was not necessary for me:-
Make sure the App Pool account has DB_Owner access to the SharePoint Configuration Database and the SharePoint Content Database.

Make sure your code runs under Platform Target: x64 (although I find Any CPU works better) - just make sure it is not x86 because SharePoint 2010 was only x64 bit hence it would not work.

Unable to start debugging on the web server (Visual Studio 2010) and IIS 7.5 Part 2

Running Visual Studio 2010 Asp.Net web code using the Local IIS Web server Version 7.5 (Windows 7 x64), I came across the error:-
Unable to start debugging on the web server. Unable free abortion to connect to the web server. Verify that the web server is running and that incoming HTTP requests are not blocked abortions pills by a firewall.

 

 

To Fix:-
Open IIS - Control Panel - System and Security - Administrative Tools - Internet Information Services (IIS) Manager
Expand PC/Server Name - Expand Sites - Select Default Web Site
Select Authentication

Make sure

Windows Authentication is enabled

Unable to start debugging on the web server (Visual Studio 2010) and IIS 7.5

Running Visual Studio 2010 Asp.Net web code using the Local IIS Web server Version 7.5 (Windows 7 x64), I came across the error:-
Unable to start debugging on the web server. The server what is an abortion committed a protocol violoation. Section=ResponseStatusLine

 

The problem exists because medication abortion it seems that having skype installed and running can take control of Port 80 which is used as the default port for IIS.  Therefore to fix this, make sure you uncheck the "Use port 80 and 443 as alternatives for incoming connections" in Skype - Tools - options - Advanced - Connection.

Asp.Net Windows Authorisation in IIS7 for Windows Server 2008 and r2

When Kerbos hasn't been setup correctly on a Windows Server 2008 and 2008r2 server, I've noticed a couple of issues when running Asp.Net code in IIS and using Windows Authorisation enabled, anonymouse disabled.

Running the Page locally in IE on the Server works correctly and Windows Credentials are picked up by the Asp.Net code.  However running the Page in IE on a separate machine (via the network) resulted in the following errors:-

"You are not Authorized to view this page"

 

Or "HTTP Error 401"

Running the Page in IE using the IP address of the server instead of the server name worked (probably because it defaults to NTLM instead of abortion price Kerberos).

1.0) Server 2008r2 Fix

When there appears to be an issue with Kerberos on the Network and IIS (as in this case), then we need Web Applications to default to NTLM instead for Windows Authentication pass-through.
The fix for Server 2008r2 is slightly easier than in Server 2008 so this is first.

1.1) Setup Application Pool

This is not 100percent necessary as you can use one of your existing Application Pools; however for demonstration purposes it is easier to create a new one.

Remote onto the Server and load the Internet Information Services (IIS) Manager:-
Select your server (expand using the plus icon)
Select Application Pools

Create a new Application Pool e.g.
Name: ASPNET2
.Net Framework: v2.0
Managed Pipeline: Integrated
Identity: ApplicationPoolIdentity

 

1.2) Setup/Convert to Web Application

Click on your Asp.Net Directory under Sites – Default Web Site.
Right click and "Convert to Web Application" – might already be as the icon will be changed from the standard folder icon.
Make sure our new Application Pool is selected

Click Test Settings and notice the error:-

1.3) Change Folder Settings to allow App Pool Access

The Application Pool is now running under ApplicationPoolIdentity as ASPNET2.  This is visible in the Windows Task Manager on the Server:-

If this user is not visible then open your Web Application in IE on the local server (just click on the "Browse *:80 (Http) in IIS)

Open File Explorer:-
Go to C:\inetpub
Right Click wwwroot - Properties
Security Tab
Click Edit
Click Add
Change location to ServerName (Top of list)
In the "Enter the object names to select" type:-
IIS AppPool\ASPNET2
Click OK

Select ASPNET2 in the "Group or User Names"
Then Tick:-
Read & Execute
List folder contents
Read

1.4) Set Web Application Windows Authorisation

Back in IIS click on authentication icon:-

Enable ASP.Net Impersonation and Windows Authentication:-

 

1.5) Configure Windows Authorisation

Select Windows Authentication and in the Right Hand Menu select Advanced Settings and it should look like the following:-

Close this and next Click "Providers…"
Move NTLM up and then Negotiate is second

Close and restart IIS - all should now work!

2.0) Server 2008 Fix

Server 2008 is slightly different as not all the options are visible.  Therefore follow all the same settings as above for 2008 up until Point 1.3

2.1) Change folder settings to allow App Pool access (Server 2008 method)

In Server 2008 you cannot add the following user to Folder Security as it will not find this user:-
IIS AppPool\ASPNET2

Instead you need to use icacls with the following syntax (open a command line and enter):-
icacls c:\inetpubwwwroot /grant "IIS AppPool\ASPNET2":(OI)(CI)(RX)

When run then in wwwroot folder security you will see ASPNET2 is listed so follow the same instructions as Server 2008r2.

2.2) Configure Windows Authorisation (Server 2008 natural abortion pill method)

The "Providers…" option is not available in IIS for 2008 and so this has to be done manually via modifying the ApplicationHost.config file under:-
C:\Windows\System32\inetsrv\config

Scroll down to your Application and amend using the following details:-

<system.webServer>
  <security>
    <authentication>
      <anonymousAuthentication enabled="false" />
        <windowsAuthentication enabled="true" useKernelMode="true">
          <providers>
            <clear />
            <add value="NTLM" />
            <add value="Negotiate" />
          </providers>
        </windowsAuthentication>
     </authentication>
  </security>
</system.webServer>

Note in 2008r2 you may need to also add the following:-
  <extendedProtection tokenChecking="None" />
this goes below
  <windowsAuthentication enabled="true" useKernelMode="true">

Restart IIS and all should work.

SQL Server Reporting Services Subscription Error

After creating a Subscription for a report I noticed it was not actually sending the email error reported was:-
Failure sending mail: An error has occurred during report processing.

To Find out what is the actual error take a look at why abortion the SSRS Logs (usually located at):-
C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles

The Error we are looking for is something along the lines of:-
Error will appear in the latest log as:-
ReportingServicesService!processing!e!DD/MM/YYYY-HH:MM:DD:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
 Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSetName'. ---> System.Data.SqlClient.SqlException: A severe error occurred on the current command.  The results, if any, should be discarded.
Operation cancelled by user.

The cause is abortion options most likely due to the report taking too long to be created.  Therefore check your timeout seconds in the Report Manager:-
Http://YourReportServer/Reports
Click Site Settings (Top Left Corner)
Under "Report Execution Timeout" change to the following:-
"Limit report execution to the following number of seconds: 3600" e.g. 1hr

 

Microsoft Performance Dashboard for SQL Server 2008 - Issue 3

Microsoft's SQL Server Performance Dashboard for SSRS 2005 is still an excellent bit of kit for checking the status of your SQL Server (Microsoft Download page).

However after finding a slight coding issue when trying to install setup.sql (see Previous Blog), and then another issue when running the code (see Previous Blog).  Well another issue occured - yippy:-

An Error has occured during report processing (rsProcessingAborted). Cannot read the next data row for the dataset CPU_UTILIZATION_HISTORY. (rsErrorReadingNextDataRow).

Arithmetic overflow error converting expression to data type int.

Again this is another simple fix.  unwanted pregnancy Either amend the following code in setup.sql or look for free abortions the SP [MS_PerfDashboard].[usp_Main_GetCPUHistory] in the msdb database.

The line to change is:-

dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,

to

dateadd(ms, -1 * (@ts_now - [timestamp])/1000, GetDate()) as EventTime,

Microsoft Performance Dashboard for SQL Server 2008 - Issue 2

Microsoft's SQL Server Performance Dashboard for SSRS 2005 is still an excellent bit of medication abortion kit for checking the status of your SQL Server (Microsoft Download page).

However after finding a slight coding issue when trying to install setup.sql see Previous Blog I have now found another issue when running the code:-

An Error has occured during report processing. (rsProcessingAborted). Cannot read the next data row for anti abortion facts the dataset SESSION_CPU_WAIT_INFO (rsErrorReadingNextDataRow)

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Again this is a simple fix.  Either amend the following code in setup.sql or look for the SP [MS_PerfDashboard].[usp_Main_GetSessionInfo] in the msdb database.

The line to change is:-

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to

sum(convert(bigint, CAST( DATEDIFF(minute, login_time, getdate()) as bigint)*60000
+DATEDIFF(millisecond, DATEADD(minute, DATEDIFF(minute, login_time, getdate()), login_time), getdate() )))
-SUM(CONVERT(bigint, s.total_elapsed_time)) as idle_connection_time,

Could not load file or Assembly Microsoft.SharePoint.Search

When developing Web Part's for SharePoint 2007 (WSS 3.0) in Visual Studio 2008 and referencing the Microsoft.SharePoint.dll sometimes when running the code you will get the following error:-

Could not load file abortion at 5 weeks or Assembly 'Microsoft.SharePoint.Search, Version=12.0.0.0, Culture=neutral, teen abortions PublicKeyToken=71e9bce111e9429c' or one of its dependencies. An attempt was made to load a program with and incorrect format.

 

To Fix this add the following to the Projects Post-build event command line:-
del "$(TargetDir)Microsoft.SharePoint.Search.dll"