Interactive Dashboard Support Guide

 

Contents

Introduction

Background

Installation Prerequisites

Installations

Database Settings

Network Configuration

Internet Settings

Windows Operating Environments

Integration

Common Errors & Recommendations

Troubleshooting

Troubleshooting Tests

HTTP Request errors

How to use JaySQL

Troubleshooting Gadgets

Log Files

Useful Cases

Useful KB Articles

 

 

Introduction:

This Sage CRM Interactive Dashboard Support Guide aims to provide additional knowledge about the Interactive Dashboard and other java related features in SageCRM version 7.1 & version 7.2. It is specifically aimed at Sage CRM Professional Services and Support Engineers.

Note:

This document is specific to SageCRM version 7.1 & version 7.2. An updated version of this document will be released for future versions.

This document provides a run-through of installation, configuration and usage of the Interactive Dashboard. It describes how this feature currently works and provides implementing techniques. This document will also provide general information relating to other java powered areas of the Sage CRM product such as The SpellChecker, Data Upload and Data Export.

This document will review common configuration errors and detail their resolution as well as providing techniques for capturing error details.

This document will also provide best practice advice when carrying out an implementation and information on supporting/troubleshooting the new java functionality effectively. It should be viewed as a supplemental guide to the SageCRM version 7.1 & version 7.2 Administrative Guide.

Return to Table of Contents

 

Background:

The Interactive Dashboard is designed to be a convenient workspace within Sage CRM which all users can utilize to organize and manage their workload.

You will find a wealth of information relating to what can be achieved using the Interactive Dashboard on our Ecosystem site https://community.sagecrm.com/partner_community/default.aspx.

The figure outlined below displays the architecture for version 7.1 of Sage CRM.

CRM v7.1 Architecture

 
Description

The elements of the diagram above which are most relevant to the operation of the Interactive Dashboard are:

? - JDBC: is an API used by Java applications to control access to the database. CRM connection is controlled through a jdbc.properties file.

? - Apache Tomcat Server: is an application server which renders web pages which contain java server page files. It is used to render Interactive Dashboard files as well as the Jspell Spellchecker feature

? - Listener / Redirector: The redirector passes URL values for connecting to the CRM and Tomcat Web Applications. It utilizes ISAPI filters to determine the URL values. In essence, this directs CRM to the Interactive Dashboard pages by managing URL and Web server connection values.

Please note the use of new Java related terminology which will be used throughout this document:

JAR file - a JAR (Java Archive) file is a file that contains the applet components in a single compressed file. Ordinarily, a Sage CRM user will not need to "open" or view a JAR file directly. It is opened and run when the corresponding URL in Sage CRM is invoked.

WAR file – a WAR file (Web Application Archive ) is a JAR file used to distribute a collection of Java Server Pages, servlets, Java classes, XML files, tag libraries and static Web pages (HTML and related files) that together constitute a Web application. ?

WEB-INF – the /WEB-INF directory in the WAR file contains a file named web.xml which defines the structure of the web application.

The figure outlined below displays the architecture for version 7.2 of Sage CRM.

CRM v7.2 Architecture

Description

 

Return to Table of Contents

 

Installation Prerequisites:

We strongly recommend that you keep your Windows environment up to date – Windows updates should be run on the server and all client machines regularly.

Where possible, windows should be set to update automatically.

Similarly we recommend that you keep your Java environment up to date. To do this you should set JRE and Java to automatically update.

In both cases this is to facilitate smooth deployment and installation of Apache Tomcat and Apache POI files. The installation process assumes that the latest Java and windows files for the environment are installed on your SageCRM server.

You will find details of how to set up automatic updates on our Ecosystem community:

- Java Automatic Updates: https://community.sagecrm.com/user_community/b/sage_crm_news/archive/2010/11/10/staying-up-to-date.aspx  ?

- Windows Updates: https://community.sagecrm.com/user_community/b/sage_crm_news/archive/2010/11/10/staying-up-to-date-windows.aspx

Note: Failure to have an up to date operating system or Java environment could result in Tomcat deployment issues during installation / upgrade of Sage CRM.

Return to Table of Contents

 

Installations:

Pre-Installation Checks:

To limit the possibility of errors you must run through the following checklist prior to all installations or upgrades.

Description

Installshield:

When running through an installation or upgrade the installshield will make certain assumptions.

If you select Existing Microsoft SQL Server

Description

 

The install shield will add the default TCP/IP port number for SQL (1433) to the Port Number field on the install screen.

Description

You may not be using the default TCP/IP port for your database instance.

Therefore, beforecarrying out the installation or upgrade you should identify the TCP port being used by the database server.

You can use SQL Server Configuration Manager to identify what port is being used by your SQL instance.

 

SQL Server Installations:

To correctly install CRM you are required to:

? Ensure TCP/ IP access is enabled for SQL

? Identify the TCP/IP port in use

To enable TCP/IP access; click on Start > Programs> SQL Server > Configuration Tools > SQL Server Configuration Manager and confirm that the TCP/IP protocol is enabled for the SQL instance you’re using.

Description

Once you have confirmed this you will be able to check the port number being used.

To do this, right click on the TCP/IP Protocol associated with your SQL instance, choose properties and select the Protocol tab. You will see the following:

Description

Keep the default settings as shown above.

Next click onto the IP Addresses tab

Description 
 

A list of IP addresses in use will be shown. These should be set to Active but not enabled.

If you wish to use Dynamic Ports set the Dynamic Ports value to 0. This will be populated with the port being used once you restart SQL services

 Description

Note:

If you enable TCP/ IP or make changes to the port settings you will need to restart SQL and IIS before the change takes effect.

SQL Express Installations:

1. With the SQL Installations, before carrying out the installation or upgrade; Log onto Start > Programs> SQL Express> Configuration Tools and enable the TCP/ IP Protocol

2. When Installshield is running it should check your registry settings and populate the Port Number with the correct SQL Express Port Number.

Note:

You may encounter issues if you have more than one instance of SQL Express – this will be cover in more depth further down this document.

 

Oracle Installations:

The default port number is 1521 when you select Oracle install on the Installshield the port will default to 1521. If your install is using a different port number you will need to manually change this to the port being used.

Verify your service, database and tablespace details on the Installshield screen before proceeding with the installation.

 Description

Multiple Database Server Instances:

As outlined above, when running the Installshield, once the installer sees what kind of database server you are using (SQL/Express / Oracle) it will populate the port number value with the corresponding default value.

If you are running multiple instances e.g. SQL2005, SQL2008 instances on the same server, only one instance can use the default port value. All other subsequent instances will require their own port allocation.

To check the port allocations for SQL Server instances you should use the SQL Server Configuration manager.

To check port allocations for Oracle you should check the TNSNAMES.ORA file to see what ports are being used.

For Example:

ORA11 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORA11)

)

)

Note: In all cases you should manually input the correct port number for the instance to the port number field on the install shield.

Upgrades:

In relation to the database, upgrades are the same as new installations; however you may receive additional on screen prompts advising of views, indexes or statistics which will be overwritten.

In relation to the web files these are overwritten with each service pack update:

When you install a service pack update the upgrade replaces the tomcat webapp directory for CRM.

A tomcat WAR file is copied to the main tomcat directory and from here the Tomcat service expands the war file and re-populates the webapp folder.

Once you launch the patch installer, after the initial stop of the service(s) (IIS, tomcat etc) before the files are copied, the installer will remove the old war file and web app from the tomcat folder. The new war file is then copied over, the tomcat service is restarted and it will expand the war file. Before the webapp can be used by CRM some files need to be updated in the webapp with specific install info, the installer will update the necessary files and this requires a restart of tomcat again.

If you see a message during the install where it indicates tomcat did not restart properly, you should stop the tomcat service in service manager before you run the patch installer (do not uninstall the service) and try again.

Additional installation Information:

There are some other conventions to be aware of when running an installation

?- You need to be specific when identifying the database server host name. Do not use the Default/ Local options offered for server name.

   - Try to avoid using server names which have underscores as part of their install name

?- Similarly do not use special characters in the database server name. The server name should be alphanumeric only.

?- If you’re intending to use the SDATA feature it is preferable to use lowercase letters for the server name.

?- The web server name should not be longer than 15 characters

 

Installations– Database Login Screen

Installation checks:

Description

Post-Installation Checks:

Description

Return to Table of Contents

 

Database Settings:

For the Interactive Dashboard files to load SQL and the JDBC connection must be configured correctly.

SQL Configuration

The TCP/IP protocol must be enabled and listening for connections.

Test the SQL TCP/IP Connection:

1. Open SQL Server Management Studio and click onto the Login tab

Description 
 

2. From here click onto the Connection Properties tab

Description

 

3. On the Network Protocol panel choose TCP/ IP from the dropdown list

4. Click on the Connect tab at the bottom of the screen

5. If the TCP/ IP port is running and configured correctly you will be logged into SQL Server Management Studio

6. If not you will receive a network error such as the below , indicating that the Protocol is not working

Description

JDBC Connection:

The jdbc.properties file found under;

\Program Files\Sage\CRM\CRMINSTALL\tomcat\webapp\crminstallnamej\WEB-INF

Must point to the jdbc driver file, it should match the line below:

jdbc.driver=net.sf.log4jdbc.DriverSpy

And also must contain details of the database server, database and Admin (SA) user account details as per below:

jdbc.url=jdbc:log4jdbc:jtds:sqlserver://machine:1433;databaseName

jdbc.username=username

jdbc.password=password

Please note in the case of distributed Databases:

The JDBC properties file does not get updated if database is changed. You will need to manually edit this.

Password Encryption:

During installation / upgrade when the new Tomcat files are unpacked the jdbc.properties file is automatically populated with the server, database and user logon details. In addition, the user password contained in the jdbc.properties is encrypted.

This encryption is managed by a Jacob.dll usually stored under the
%systemroot%\Windows32 directoryWindows32 directory.

If the jdbc connection experiences problems with the encrypted password you will see connectivity errors relating to the database within log files and also Eventviewer error messages relating to Jacob.dll

Return to Table of Contents

 

Web Configuration

The installation / upgrade process will automatically create the web application files for Tomcat which is used to run the Interactive Dashboard page.

You may have occasion to check some configuration options during your post installation checks but generally do not need to edit these files.

In addition to the Tomcat directory two new services folders are created, again these are automatically configured and should not require editing.

Configuration for these may be checked by running the Redirector test outlined in the troubleshooting section of this document.

One area which may require your attention is IIS configuration.

In order for the CRM and Tomcat web applications to run correctly IIS will need to be configured to use the correct identities and access levels.

Please see the details below:

The crm, crmj, sdata and Jspell virtual directories should all be using the IUSER account.

CRM related application pools should be using the local system account and using ‘Integrated’ managed pipeline.

The IUSER account should have full access to the Inetpub, Tomcat and IISUTILS directories.

Return to Table of Contents

 

Network Configuration:

Ports

There are a number of application ports which need to be considered in relation to CRM and the Interactive Dashboard.

These include the database systems TCP/IP port, the CRM website’s port and the Tomcat application port. SQL Server Configuration Manager contains details of the TCP/IP port in use. The SageCRM Website Port information is visible under IIS and within the system registry

Description

The ports used by Tomcat are available in the workers.properties file held under \Program Files\Sage\CRM\Services\IISUTILS\Crmurlredirector

Ports in Use

To identify the ports currently in use go to Start – Run – and type CMD

From the command prompt type "netstat –a –n –o". This will tell you which ports are currently in use, and which Process IDS (PIDs) are using them. Locate and make a note of the PID which is using Tomcat’s port.

Open windows task manager – click on processes and find the PID

1. Navigate to the folder containing your installs Tomcat’s server.xml file

e.g. C:\Program Files\Sage\CRM\CRM70\tomcat\conf

Description

2. Open the file and look for the port number. It will look something like “<Connector port="19009" …. />” as below.

Description 

The Tomcat port number in this instance would be 19009

3. You now have two options: you can force Tomcat to use an unused port (step 4), or you can kill the process (PID) which is using Tomcat’s port (steps 5, 6).

4. Open the command prompt and type "netstat –a –n". This will tell you which ports are currently in use. Simply pick a port number which isn’t on screen, and update server.xml accordingly. You can now start Tomcat.

Description 

5. Open the command prompt and type "netstat –a –n –o". This will tell you which ports are currently in use, and which PIDs are using them. Locate and make a note of the PID which is using Tomcat’s port

Description

6. Open “Windows Task Manager”, click “View”, click “Select Columns”, check “PID (Process Identifier)” and press “OK”. Locate the PID you made a note of in step 2 and -- assuming it isn’t a vital windows process -- kill it. You can now start Tomcat.

Description

Tier 3 information:

Should you need to send a list of ports currently in use to Tier 3 Support please run the following from a command prompt?

netstat –ano>C:\Netstatoutput.txt

Collect the text file from the path above along with a screenshot or details of the processes in use and submit to Tier 3 support.

Firewalls

Having checked the ports being used by CRM (through IIS), Tomcat (workers.properties file) and SQL / Oracle

Check any firewall rules which may be blocking any of the ports for Tomcat or TCP/IP.

Also ensure that your antivirus software is not blocking the tomcat service or application.

Proxies:

It is worth confirming whether a proxy server is in use at the customer site.

Once you have established this you should check that that the proxy server details are up to date within the database

Do this by running the query:

Select Parm_Name,Parm_Value

From Custom_SysParams

Where Parm_Name In('DatabaseServer','ProxyPort','ProxyDomain','ProxyAddress')

Also, make sure that the Proxy settings are filled in under Administration – System – Dashboard Proxy Settings

Description 
 

Return to Table of Contents

 

Internet Settings

Internet Explorer Enhanced Security (IE ESC)

By default Windows 2008 Server enable Internet Explorer Enhanced Security this can cause issues with the Interactive Dashboard

You may see Http:500 error messages when you try to click onto the Interactive Dashboard

To prevent errors you should disable enhanced security for Windows 2008.

In Windows 2008, the process is a little different. First, go to Start, then Administrative Tools and choose Server Manager]

Under the Security Summary section click on Configure IE ESC.

 Description

A dialog will pop up where you can disable IE ESC for either Administrators or Users. Click Off to turn off IE ESC for whichever group you prefer.

Description

Internet Explorer: Browser Settings

Internet settings have rarely caused any issues with the Interactive Dashboard but there are some settings which may impact usage

Java applets should be enabled

Description

Use JRE should be checked

Description

Description

Confirm that the Java plugins are installed by opening Manage add-ons

Description

The Sage CRM URL should be added to your list of Trusted / Safe Sites

Description

Return to Table of Contents

 

Windows Operating Environments

Whether setting up a new server or configuring an existing environment there are a number of configuration settings which you will need to check.

Additional note: VM server images will have many services disabled by default if you are working with a VM you should first confirm that all necessary windows services and settings are enabled

Windows 2008: Server Configuration

Server Manager Checklist:

 Description 
 

1. Switch of IE ESC for all users

2. Check whether a firewall is in use

3. If it is ensure that the Tomcat port is not blocked by the firewall

Description 

4. Open the Roles Summary

5. Confirm that the IIS role has been added

6. If not, click on Add Roles and work through the wizard options

Description 

7. Add associated Role Services

These are: all Common HTTP Features; Application Development; Health and Diagnostics; Security and Management Tools

Description

Once installed you can view the status of role services by clicking on the Role Services menu within Server Manager

For the Interactive Dashboard to work HTTP Redirection, ASP and ISAPI filters and Extensions must be installed.

Description

 

Features:

At a minimum the .Net Framework features must be installed

Description

 

Small Business Server

SBS 2008 standard and premium are supported. SBS 2008 should be configured in the same way as Windows Server 2008.

There are a few additional settings which must be checked

In IIS | Application Pools | Default Application Pool | Advanced Settings | ‘Enable 32 Bit Applications’ needs to be set to True.

There are also some installation issues which you should be aware of, detailed below:

Description

Return to Table of Contents

 

Integration

Exchange:

Whilst running Exchange on the same server as CRM and IIS is supported there are some settings you need to be aware of.

If Exchange 2010 is installed on the same server as CRM you need to check the application bitness settings.

The default app pool & the CRM App Pool should run as 32 bit Application Pools and have Enable 32 bit Applications set to 'True'. However, Exchange runs in 64 bitness.

This can cause a conflict which will manifest itself as follows:

The default and CRM application pool continuously stop and will not remain on once started again. When trying to login to CRM, you see the error: “http 503 Service unavailable”

To rectify this you need to update the "applicationHost.config" file located at:

%System Root%:\Windows\System32\inetsrv\config

add this line: preCondition="bitness64"

Example in our case of the Kerbauth.dll causing the App Pool to fall over:

This is BEFORE CHANGE:
<add name="kerbauth" image="C:\Program Files\Microsoft\Exchange Server\V14\Bin\kerbauth.dll" />

And this is after the change, when we add the line as described above:

<add name="kerbauth" image="C:\Program Files\Microsoft\Exchange Server\V14\Bin\kerbauth.dll" preCondition="bitness64" />

Once you’ve made this change start the AppPool again and any 32 bit application will now work and their associated application pools will remain in a running state.

 

Outlook Web Access:

If you are running OWA from the same server as CRM you will need to check

Windows Event Viewer Application and System Logs for any dll related error messages.

You will need to edit the bitness settings as described under the exchange configuration above.

You will also need to edit the bitness settings for any other dlls referenced in the event viewer logs.

If you are not sure how this should be configured please contact Tier 3 support.

 

ERP Integrations:

Working with Accpac or Sage 200 integrations you should ensure:

- that the CRM Application Pools are set to run as 32 bit Applications
- the CRM Application Pools are set to ‘Integrated’ Managed Pipeline Mode
- the ISAPI filters are installed and handler mappings are set to Read, Write , Execute
-
ASP and ASP.net are enabled

 

IIS Configuration: Under the Application Pools, check which mode the Managed Pipeline is set to.

For CRM the mode should be set to Integrated

 Description

Installation Sequence:

Working with Accpac 6.0 and CRM 7 the installation sequence you choose is very important.

If Accpac is installed first and then CRM then all will work as normal. However, if you install CRM first and then install Accpac, none of the sites will run and you will receive an http 500 error on both the CRM and Accpac portal

Note this only occurs with Windows 2008 server installations.

Return to Table of Contents

 

Common Errors & Recommendations

1) When you click on the use Interactive Dashboard tab you may see the error:

Description

The Dashboard has experienced a problem. Please contact your System Administrator

You may also see this error when adding a new template or gadget

This error indicates a connectivity issue

To resolve you need to:

- Check your jdbc connection properties are correct
- Ensure the Default App pool is running under IIS 
- Ensure the Application Pools are running under the correct identities
- Ensure Handler Mappings have been given Execute rights at machine and site level in IIS
- Check that Apache Tomcat Service is running correctly
- Restart Oracle / SQL Services, Restart Apache Tomcat Services, Restart IIS services 

Error Loading Data

When trying to open a dashboard or gadget template

To resolve you need to:

- Check your jdbc connection properties are correct
- Ensure TCP/IP is enabled
- Restart Oracle / SQL Services, Restart Apache Tomcat Services, Restart IIS services
- Check the setup.log for errors

If you see either of the errors listed above the first thing to check is your redirector test

Issue: Event Viewer Application log error:

‘Could not load ISAPI filters for the site/service. Therefore startup aborted’
‘The HTTP Filter DLL E:\Program Files(x86)\Sage\CRM\Services\IISUtils\URLRewriter\ISAPI_Rewrite.dll failed to load. The data is the error’

This only occurs on 64 bit machines and relates to the Application Pool needing to be run in 32 bit compatibility mode. (See earlier notes relating to Application Pool – Advanced Settings)

Recommendations:
-
Run the CRM Application Pool in 32 bit mode
-
Ensure ISAPI filters are installed

If ISAPI filters are not installed you can install them by:

1. Opening a cmd prompt

2. Navigate to the Framework\v2.0.50727 directory

3. Run aspnet_regiis.exe / i

 

2) Interactive Dashboard not working in Sage CRM.

Cannot find windows time zone for CRM timezone: +11:00_2’

Investigation Steps:

Any Tomcat Issues you need to get the latest logs from the CRM & Tomcat logs folder.

- Stop Tomcat Service
- Delete / Archive existing CRM & Tomcat Logs
- Make sure no log files in the CRM Logs or Tomcat Logs folder’s
- Start Tomcat for the install again
- Reproduce the steps
- Verify the log files to troubleshoot the issues

Error messages in Logs:

The scrm.xml file in the location:

C:\Program Files (x86)\Sage\CRM\[CRM INSTALL NAME]\Logs\SCRM

Was showing:

‘Cannot find windows time zone for CRM timezone: +11:00_2’

 Description

Troubleshooting Steps

1. Confirm whether the below schema URL’s are returning correctly

SDATA Schema URL:

http://localhost/sdata/<crminstallnamej>/sagecrm/-/$schema 

It should return schema as below

Description

If getting HTTP errors need to check the recommendations and correct them.

2. Check the CRM logs and the Tomcat logs for error messages

3. Check the scrm file in CRM logs folder for any errors. You might get the error :

‘Cannot find windows time zone for CRM timezone: +11:00_2’

Compare the registry on a working server and compare the registries in the customer’s CRM server.

Time zone location in Registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WindowsNT\CurrentVersion\Time Zones

You can also use the below query to check the

‘CRM timezone: +11:00_2’

select * from custom_captions where Capt_Family like 'timezonedeltajava%'

The Capt_Code column would show the corresponding Caption.

If any registry entry is missing have to export this registry entry from a working machine and import it to the machine having issues and will fix the time zone error.

 

3) Tomcat Error when accessing Interactive Dashboard

Generated new CRM & Tomcat logs

In CRM Logs folder, Default.log was getting as below:

<log4j:event logger="org.springframework.web.context.ContextLoader" timestamp="1326726585855" level="ERROR" thread="Thread-1"><log4j:message><![CDATA[Context initialization failed]]></log4j:message> <log4j:throwable><![CDATA[org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'timeZoneList' defined in ServletContext resource [/WEB-INF/applicationContext.xml]: Invocation of init method failed; nested exception is java.lang.NullPointerException at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1338) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409) at java.security.AccessController.doPrivileged(Native Method)

Check SDATA & Tomcat schema URL returning any errors.

There are two errors that are showing in the above Logs:

- Context initialization failed
- Error creating bean with name 'timeZoneList'

Check the ApplicationContext.xml file for the first error. This is in the location:

C:\Program Files (x86)\Sage\CRM\ <CRM Install name> \tomcat\webapps \ <CRM Install name j > \WEB-INF.

Try deploying the tomcat web apps again. It might be better to take a copy of the webapps folder before doing this as a backup. The war files in the webapps folder shouldn’t be deleted.

Refer article No: 106-15272 in Self Service to resolve the 'timeZoneList' issue

Recommendations:

- Confirm whether the database connectivity is working correctly using JaySql
- Then check the database with the below query :

Select * from custom_captions where Capt_family like '%TimezoneDeltaJava%'

If this doesn’t return any data in the database, then have to run the runes70.exe tool and use the 70 update.es file from

C:\Program Files\Sage\CRM\<CRM Install Name>\Setup\ES\

Need to copy the Runnes70.zip file to the server.

Attach the 70 update.es file in the Runes70 tool and check whether it fixed the error in CRM\Logs\default.log &SCRMSQL.log

Now check whether CRM\Tomcat\Logs\catalina.log and stdout.log is showing the errors anymore.

After running this tool it will give the message script run successfully. Confirm in the database after running the below SQL query to check whether the related data is present in the database. It could be better if you take a copy of the database before running this script.

Select * from custom_captions where Capt_family like '%TimezoneDeltaJava%'

Confirm whether this fixes the issue.

 

4) Dashboard and Export to Excel not working – In V7.0 (Issue with jre 64 update 29)

Issue:

This happens for Windows 2008 64 bit machines. After uninstalling all the old JRE updates (ex : JRE 22, 24,27 etc.) from a 64 bit machine and install the 64 bit JRE 29 the dashboard & export doesn’t work.

Recommendations:

- Verify whether the JRE update 29 in Control Panel \ Program Features
- Check the schema URL’s
- Check any Firewall or Anti-virus is active. Try disabling it.

Description:

After uninstalling the old JRE updates, it actually deletes the jvm.dll file from the C:\Program Files (x86)\Java\jre6\bin\client. When the new JRE 29 update for 64 bit machine is installed it creates the jvm.dll file in the location: C:\Program Files\Java\jre6\bin\server (obviously as it is a Windows 2008 64 bit machine).

Now in the registry after CRM is installed it puts an entry in the location:

HKEY_LOCAL_MACHINE- SOFTWARE - Wow6432Node – JavaSoft – Java Runtime Environment - 1.6.0_29 - RuntimeLib is pointing to location

C:\Program Files (x86)\Java\jre6\bin\client\jvm.dll. As the old JRE updates are uninstalled and the registry is trying to find the jvm.dll in the C:\Program Files (x86)\Java\jre6\bin\client location and doesn’t find it on a Windows 64 bit version as the jvm.dll file is now in the location: C:\Program Files\Java\jre6\bin\server as it has only the JRE 64 bit update.

Workaround:

After uninstalling all the previous updates of Java and before installing the JRE 29 update for 64 bit, we need to install the JRE 29 (32 bit update) as it will create a jvm.dll file in the C:\Program Files(x86)\Java\jre6\bin\client and then install the JRE 29 update for 64 bit . This is because CRM tomcat expects a 32 bit version of the jvm.dll to be installed in the \program files (x86)\java\jre\bin\client directory.

You could also install the JRE update 27 instead of JRE update 29 if required on the server.

 

5) Interactive Dashboard not working (Error loading data).

Issue:

The redirector was failing, preventing requests from reaching Tomcat. Tomcat seemed to be working successfully.

Recommendations:

-Stop Tomcat Service.
-Delete/Archive existing CRM & Tomcat logs
- Restart Tomcat service
- Reproduce the issue
- Check the schema URL’s

If the SDATA schema is returning HTTP error 404, this indicates that either the redirector wasn't working, or Tomcat wasn't started.

A 404 for Tomcat would mean that Tomcat is running, but there's a problem with the crmj webapp. This would show up in the Tomcat logs.

When checking the schema URL, it's good to check if it's Tomcat or IIS – if IIS gives an error message, there won't be any Tomcat logs, unless it's failing on startup.

Check the redirector now. There's a default test page in the sdata application which can be used for this. The URL is as follows:

http://servername/sdata/test.html

If you check the CRM.Rewriter.rules, you can see that this redirects to http://servername/sdata/Default.aspx?r=/sdata/test.html. This is the same when accessing:

http://servername/sdata/default.aspx 

If the default.aspx page fails, then there's probably a problem with .NET on that machine.

If this brings up any HTTP errors, you can refer the HTTP errors here or also Google the error message to find out the problem.

If you get an error on test.html but not Default.aspx, then there's something wrong with the CRMrewriter.

The problem is that IIS by default will always try to serve you up a file.

It saw that test.html didn't exist in the sdata directory, and threw an error.

This is pretty much what was happening on requests to the SDATA schema.

When we send a request to http://servername/sdata/crmj/sagecrm/-/$schema we want everything to go to http://servername:[Tomcatport]/crmj/sagecrm/-/$schema

Adding the wildcard script mapping tells it to forward everything without looking at it which should resolved the issue. You can refer KBA 492-15544 in self-service which describes the steps for resolving the issue

 

6) Interactive Dashboard doesn’t work on a port other than port 80

Issue:

By default the CRM.Rewriter.rules file in:

C:\Program Files (x86)\Sage\CRM\Services\IISUtils\CRMRewriter

Shows as:

RewriteRule ^/sdata/crm71cj/(.*)$ http://%{HTTP_HOST}:[TomcatPort]/[crm install namej]/$1?%{QUERY_STRING} [P]

The HTTP_HOST is taken into consideration as localhost.

Recommendation:

Stop Tomcat service for the install. Take a copy of the Rewriter rule file.

Update the Rewriter rule with [SERVER_NAME] instead of [HTTP_HOST]. As below:

RewriteRule ^/sdata/crm71cj/(.*)$ http://%{SERVER_NAME}:[TomcatPort]/[crm install namej]/$1?%{QUERY_STRING} [P]

Return to Table of Contents

 

Troubleshooting

Debugging Options:

HTTP Errors:

To see more detailed http or browser errors go to Tools –> Internet Options -> Advanced

?-  Uncheck ‘Show friendly HTTP error messages’

? - Restart Internet Explorer you should then see more descriptive errors within the browser window.

 

Redirector Logs:

In cases where no java driven features are working: Interactive Dashboard, Apache POI or Jspell Spellchecker you should edit the redirector log file to capture more information.

To do this:

1.go to \Program Files\Sage\CRM\Services\IISUtils\URLRedirector

2.Open the ISAPI_Redirect.Properties using notepad

3.Set the log_level =debug

4.Run through the steps to generate the error again. The resulting log file can be retrieved from your Program Files\Sage\CRM\Services\IISUtils\URLRedirector\CRMRedirector.log

Tomcat Logs:

Held under, \Program Files\Sage\CRM\ CRMinstallname\tomcat\logs the relevant logs are

Catalina.log – outputs details of the Apache Tomcat service

Stdout.log – outputs details of the Interactive Dashboard metadata

Log Levels:

By default the log level will be set to ERROR whilst investigating issues you may need to change this level setting

Description
 

 

Change this within the log4j.properties file

CRM Logs:

Held under, \Program Files\Sage\CRM\CRMinstallname\Logs the log files relating to the Interactive Dashboard are:

? - Default.log

? - Scrm.xml

? - Sql.xml

? - Scrmstacktraces.log

Return to Table of Contents

 

Troubleshooting Tests

To help identify issues and narrow down the cause of the issues with the Interactive Dashboard there are a few Schema checks that can be down:

Description 

If there any any issues with any one these schema checks they will return a HTTP error:

Data Connectivity errors:

You could see error messages like in the CRM logs or the Tomcat logs:

‘Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!’

If you see ‘Could not get JDBC Connection errors’ or ‘unknown source’ in the scrmsql.log or the scrm.log files

You should run the tests below:

1. Confirm that TCP/ IP is enabled and listening by connecting via SQL Server Management Studio

2. Confirm that the jdbc properties file is using the correct server name

In a single server install (both Application & SQL on the same server), run the following query on a SQL Query window to confirm the server name for the jdbc.properties file:

Select Parm_value From Custom_SysParams Where Parm_Name='Servernames'

For a CRM instance with Application and Database on different servers, the name in your jdbc.properties file should match the server name exactly as the Database server name. If it doesn’t you will need to update the jdbc.properties file.

JaySQL is useful for testing JDBC connections. It is a tool free to download. You could find description of how to use JaySQL here.

Restart Tomcat and test the connection again by opening the Interactive Dashboard tab.

Password Encryption:

If you see ‘unknown source’ type errors in the Catilina.log file or any errors relating to the Jacob.dll in the windows Eventviewer logs you can test to check whether password encryption is not working correctly.

Firstly, confirm that you have the correct password for the SA user account:

1. Open SQL Server Management Studio.

2. Select SQL Server Authentication as your authentication method

3. Type in the Sa login and password information

Description

If this connects without issue then you can proceed to test the password encryption.

To do this:

1. Navigate to \Program Files\Sage\CRM\yourcrminstallname\tomcat\webapps\yourcrmnstallnamej\WEB-INF

2. Open the jdbc.properties file and edit the connection details

3. Type in the plain text password for the sa account

4. Save your changes and restart the Tomcat Service

5. Test your connection by logging into CRM and Opening the Interactive Dashboard tab.

6. If the tab loads without issue, you should escalate this case to Tier 3 support advising that jdbc password encryption is causing a problem.

Test Port Connectivity:

 

To test connectivity to either the SQL or Oracle TCP Port or to the Tomcat Port you can use Telnet. Telnet should be available as a windows service.

1. Log into the services console and start this service

Description 

2. Once this is running, go to start – run and type telnet localhost

Description 

3. This should open a command window

Description

4. Enter the command telnet localhost and the port number you wish to test e.g. 1433. If the port is open you will see a blank window.

Description 

5. Otherwise you will see a failure message:

Description

Return to Table of Contents

 

HTTP Request errors:

If you encounter any HTTP errors it is best practice to install and run fiddler in the background. This application can be downloaded from: http://www.fiddler2.com/fiddler2/version.asp

Use the text and header views to assess what URL’s are being called. The following is a list of some common HTTP errors and troubleshooting recommendations.

Http: 401:

HTTP Error 401 Unauthorized

1. Open the IIS Management Console

2. Right click on the Default website and choose properties – directory security

3. Check that the IUSER account is set up and anonymous access is enabled

4. Repeat this check against the CRM, Sdata and Jspell websites

5. Next check your Application Pool identity settings:

a. Navigate to the CRM application pool

b. Right click and select Properties – Identity

c. The identity should be set to Predefined (Local System)

d. Ensure the CRM and sdata Application Pools are set to this

e. Ensure the IUSER account has full permissions against the eware folder in the system registry

f. Ensure the IUSER account has full permissions to the wwwroot directory of your install

Description

6. Check the Default Application Pool – here the identity should be set to Predefined (Network Service)

 

HTTP 403: HTTP Error 403 Forbidden

Recommendations:

1. Ensure the Application Pools are set to run with the correct identity

2. Ensure that handler mappings are set to execute

3. Ensure Directory Browsing has been enabled in IIS

4. Untick the Require SSL option in SDATA virtual directory if the install doesn’t use SSL.

 

Issue: Service Unavailable error in browser window, when you try to open a URL

Recommendations:

1. Check whether you can browse to Localhost Default Website

2. Check the Windows Event Viewer Application log for more details

3. Check that ISAPI filters have been loaded

4. Check that IIS and all app pools are running

Http 403.6: You are not authorized to view this Page

Sage CRM Interactive Dashbaord Support Guide

Description 
You will see this error if your IIS website has been configured to only grant access to specific IP addresses. You can check the settings for this by doing the following:
1. Opening IIS Manager - Right click the CRM website and choose Properties
2. Edit Directory Security – IP address and Domain name restrictions
3. Requests should be accepted by the CRM server’s IP address and also the domain IP address range

Description

Description 

4. You should repeat this check for the sdata and Jspell websites

5. ** Note** the Jspell website runs through a local asp page so the localhost IP address must be granted access for Jspell to work properly. This is usually IP address 127.0.0.1

6. To check what IP address localhost is using go to start – run and type ping localhost

7. The reply from IP address is the IP address used by localhost

Description 


HTTP Error 404: File Not found or HTTP Error 404- File or Directory not found
Recommendations:
- Ensure Handler Mappings have been given Execute rights at machine and site level in IIS
- Check that TCP/IP Protocol has been enabled under SQL Server Configuration
- Check that Apache Tomcat Service is running correctly
- Make sure ASP and CRM are enabled under Web Service Extensions
- Go to Add/Remove programs - > Windows component wizard - > Active server pages needs to be ticked
- In the case of Windows 2008 ensure the ASP feature is installed
- Check SDATA – Authentication – Forms Authentication should be disabled
- For V7.0 of Sage CRM check ISAPI Filters tab in Default website. Both CRMUrlRewriter & CRMUrlRedirector should be listed as below screen shot. The CRMUrlRewriter should be pointing to:C:\Programs\Sage\CRM\Services\IISUtils\URLRewriter\ISAPI_Rewrite.dll
The CRMUrlRedirector should be pointing to:C:\Programs\Sage\CRM\Services\IISUtils\URLRedirector\isapi_redirect.dll

Description

 

Issue: HTTP 400: Bad Request

Recommendations:

- Check that all IIS services are running

- Run fiddler to analyse the URL

- Check whether Firewall or Anti-Virus is enabled and stopping access

- Check whether the CRM install name in the schema URL is case sensitive

 

Issue: 401.1 Unauthorized: Logon failed

Recommendations:

- Ensure the Application Pools are set to run with the correct identity

- Ensure that handler mappings are set to execute

- Ensure that the Anonymous Authentication for the Virtual directory (your CRM install virtual directory – e.g. CRM 7.0) is set to Application Pool Identity. IIS 7 screenshot example below

 

Issue: 401.2 Unauthorized

Recommendations:

Check in IIS SDATA - Authorization Rules – Add Allow Rule – All Anonymous user’s mode allow

Issue: 403.14 Forbidden

Recommendations:

Check in IIS SDATA – Directory Browsing and click in Actions pane in Browse. It will display error 403.14 - Forbidden The Web server is configured to not list the contents of this directory. Just double-click on Directory Browsing and select Enable from Actions pane.

 

Issue: HTTP 500 – Internal Server Error when clicking Interactive Dashboard tab

Recommendation:

- Ensure that Web Service Extensions are enabled for ASP and CRM
- Check that Enable Parent Paths is set to True

 

Issue: HTTP 500.19 – Internal Server Error

Error Code 0x80070021 Config Error This configuration section cannot be used at this path. This happens when the section is locked at a parent level.

Recommendation:

- Check whether all ASP.net features are available

 

Issue: Getting HTTP Error 503 – Service unavailable error

Note: These settings are relevant to all installations (SQL, SQL Express or Oracle)

Check that the Default App Pool is running and assigned to NetworkService

From Control Panel->Administrative Tools->Services-> IIS Manager - > Confirm that the DefaultAppPool is associated with NetworkService

On Win 2008: Under Application Pools -> Advanced Settings,

1. Change the Process Model ->identity setting of the CRM App Pool to "Local System".

2. Also, on x64 OS, change "Enable 32-Bit Applications" to True.

 

In the case of IIS 7, amke sure thathandler mappings are granted execute permissions

1. From Control Panel->Administrative Tools->Services-> IIS Manager

Right click on the IIS Server Connection and select Properties - > Edit Feature Permissions and tick the ‘Execute’ checkbox.

2. Staying within IIS Manager navigate to the Default Website - > Right Click on Properties and select Feature Permissions and tick the ‘ Execute’ checkbox

Return to Table of Contents

 

How to use JaySQL

Your JaySQL connection Parameters would look something like this:

Description

To get the JDBC Class you can click the Jdbc Mgr. button in the JDBC Connection Parameters and click on the + sign on the screen and point to the following location :

C:\Program Files\Sage\CRM\<CRMInstallName>\tomcat\webapps\<CRMInstallNamej>\WEB-INF\lib

Select the jtds-1.2.5.jar file and in the JDBC Driver Manager you will see something like above:

Ex: net.sourceforge.jtdc.jdbc.Driver

You need to highlight this on this screen and this brings it in the JDBC Class drop down to select.

In the connection string you need to specify the connection used for the SQL Server. You could get this from the jdbc.properties file located in the below location:

C:\Program files\Sage\CRM\<CRMInstallName>\tomcat\webapps\<CRMInstallNamej>\WEB-INF

The connection string would be the jdbc.url. So you need to copy starting from jdbc: log4jdbc …… and add jtds as per the above diagram.

Description 

Specify the SQL credentials in the User Id & Password fields. You need to specify a profile name and click ok. You will get a query window as below:

Description

If you get a query window as above that means your jtds driver works and you need to stop tomcat and replace the jdbc.url with the connection string (Important : you should have : "jdbc.url=" before the connection string) . This would confirm that the details in jdbc.properties file details are correct.

Return to Table of Contents

 

Troubleshooting Gadgets:

1) Error loading data when you click on Gadget Library:

Try importing the Gadget template (provided by L3support on request)

? - Try adding a new gadget using Add new gadget as opposed to the Gadget library

? - Try adding a website or calendar gadget to test connectivity

 

Website or RSS gadgets not loading:

Check your Interactive Dashboard Proxy settings are correct.

2) An error occurred when attempting to process the Gadget. Please contact your System Administrator

 Description

Recommendations:

Get the latest logs. It would show in SCRM logs (20120210scrm.xml) that the gadget doesn’t exist in the database as it has been deleted from CRM as below:

<log4j: event logger="scrmLog.class com.sage.scrm.model.layout.LandingPageHelper" date_time="2012-02-10 14:39:40.053" timestamp="1328884780053" level="ERROR" thread="http-16009-2"><log4j:message><![CDATA[Gadget doesn't exists (id = 6042)]]></log4j:message><log4j:properties>

Go to a SQL query and change the soft delete as below:

select * from dbo.LPGadget where LPGad_GadgetId = 6042

update LPGadget set LPGad_Deleted = NULL where LPGad_GadgetId = 6042

Return to Table of Contents

 

Log Files:

There are a number of log files and locations to check when issues are encountered.

 Description

Return to Table of Contents

 

Useful Cases:

The following cases may be helpful for troubleshooting purposes:

Description 

Return to Table of Contents

 

 

Useful KB Articles:

The following KB Articles should be helpful when troubleshooting issues:

Description

Return to Table of Contents